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
