Results 1 to 10 of 10
-
2019-01-25, 12:16 #1
- Join Date
- Nov 2012
- Posts
- 11
- Thanks
- 0
- Thanked 0 Times in 0 Posts
Run-time error '1004': Unable to get the OnAction property of the Button class
I have a VB6 program that imports a worksheet from a "master" Excel file into the active workbook. Each spreadsheet in the master file has a macro embedded, but no buttons to run them.
The VB6 program defines a button on the imported worksheet, sets the button text and action with the .character.text and .onAction properties of the button.
The code I have works fine with Excel 2003 (v11.0), but does not work with Excel v16.
In the newer Excel, he button gets created all right, but I can't figure out how to change the button text or point the button to run the macro.
In older Excel I would record a macro, make the changes I need, the look at the freshly recorded macro to see what its expecting. That does not work with the newer version of Excel - the recorded macro code is the same as what my VB program does, but the program throws error 1004.
Here is what works with Excel 11.0
Excel_Object.ActiveSheet.Buttons.Add(525, 255, 100, 30).Select
Excel_Object.Selection.Characters.Text = "Import Samples"
Excel_Object.Selection.OnAction = "Sheet11.DoReport"
Here is the recorded macro from Excel 16:
ActiveSheet.Buttons.Add(525, 255, 100, 30).Select
Selection.OnAction = "Sheet11.DoReport"
Selection.Characters.Text = "Import Samples"
and here is what I try to run in VB6 that fails with the 1004 error:
Excel_Object.ActiveSheet.Buttons.Add(525, 255, 100, 30).Select
Excel_Object.Selection.OnAction = "Sheet11.DoReport"
Excel_Object.Selection.Characters.Text = "Import Samples"
I have also tried the following:
ActiveSheet.Buttons.Add(525, 255, 100, 30).Select
Selection.OnAction = "Sheet11.DoReport"
Selection.Characters.Text = "Import Samples"
and
With ActiveSheet.Buttons.Add(525, 255, 100, 30)
.OnAction = "Sheet11.DoReport"
.Characters.Text = "Import Samples"
End With
If I switch the order of the .Characters.Text and .OnAction, I get the same error code but the message is "Unable to get the Character property of the Button class".
I assume that there must be some other way of setting these properties that WILL work when run from an external program vs. using the code from an internal macro.
So how DOES one code the text and action of a button in Excel v16 from a VB program?
Thanks
-
2019-01-28, 07:34 #2
- Join Date
- Dec 2000
- Location
- Burwash, East Sussex, United Kingdom
- Posts
- 6,446
- Thanks
- 3
- Thanked 261 Times in 239 Posts
I don't have VB6 installed anywhere currently to test, but try this:
Code:With Excel_Object.ActiveSheet.Buttons.Add(525, 255, 100, 30) .OnAction = "Sheet11.DoReport" .Caption = "Import Samples" End With
Regards,
Rory
-
2019-01-28, 08:48 #3
- Join Date
- Nov 2012
- Posts
- 11
- Thanks
- 0
- Thanked 0 Times in 0 Posts
-
2019-01-28, 09:47 #4
- Join Date
- Dec 2000
- Location
- Burwash, East Sussex, United Kingdom
- Posts
- 6,446
- Thanks
- 3
- Thanked 261 Times in 239 Posts
How about:
Code:Dim btn As Object Set btn = ActiveSheet.Buttons.Add(525, 255, 100, 30) btn.OnAction = "Sheet11.DoReport" btn.Caption = "Import Samples"
Regards,
Rory
-
2019-01-28, 09:59 #5
- Join Date
- Nov 2012
- Posts
- 11
- Thanks
- 0
- Thanked 0 Times in 0 Posts
-
2019-01-29, 13:46 #6
- Join Date
- Feb 2003
- Location
- Pacific Grove, California, USA
- Posts
- 728
- Thanks
- 14
- Thanked 40 Times in 34 Posts
I am not an Excel programmer, but there are a lot of variants on the 1004 run time error. What is the exact one you get?
-
2019-01-30, 09:09 #7
- Join Date
- Nov 2012
- Posts
- 11
- Thanks
- 0
- Thanked 0 Times in 0 Posts
-
2019-01-30, 13:13 #8
- Join Date
- Feb 2003
- Location
- Pacific Grove, California, USA
- Posts
- 728
- Thanks
- 14
- Thanked 40 Times in 34 Posts
Is any part of your spreadsheet frozen?
-
2019-01-30, 13:37 #9
- Join Date
- Nov 2012
- Posts
- 11
- Thanks
- 0
- Thanked 0 Times in 0 Posts
No.
The Excel file at that moment consists of three worksheets, 2 that were generated by the program and the active sheet that was copied form a master template that contains several dozen worksheets, each is a data entry form. There is a macro that is also imported with that worksheet. The button that is added is to run that macro. That works fine in older versions of Excel.
-
2019-01-30, 18:57 #10
- Join Date
- Feb 2003
- Location
- Pacific Grove, California, USA
- Posts
- 728
- Thanks
- 14
- Thanked 40 Times in 34 Posts
Well, that was a shot in the dark anyway. Sorry. Here's another shot in the dark from stack overflow, so you may have already tried.
Dim b As Button
Set b = ActiveSheet.Buttons.Add(525, 255, 100, 30)
With b
.OnAction = "Sheet11.DoReport"
.Characters.Text = "Import Samples"
End With
The only other piece of advice I've read is that the Select Method is sometimes flaky and it's better to try to set a range. That MSoft created your macro with .Select does not, in my experience, mean that it's the best way to go.
Best to you, Kim