Results 1 to 10 of 10
  1. #1
    New Lounger
    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

  2. #2
    Super Moderator rory's Avatar
    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

  3. #3
    New Lounger
    Join Date
    Nov 2012
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Same error

    Quote Originally Posted by rory View Post
    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
    Tried it.
    I get the exact same error, but thanks for trying.

  4. #4
    Super Moderator rory's Avatar
    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

  5. #5
    New Lounger
    Join Date
    Nov 2012
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Same error

    Quote Originally Posted by rory View Post
    How about:

    Code:
    Dim btn As Object
    Set btn = ActiveSheet.Buttons.Add(525, 255, 100, 30)
    btn.OnAction = "Sheet11.DoReport"
    btn.Caption = "Import Samples"
    Good idea!
    Same error.

  6. #6
    5 Star Lounger kmurdock's Avatar
    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?

  7. #7
    New Lounger
    Join Date
    Nov 2012
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by kmurdock View Post
    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?
    As stated in the thread title:
    "'1004': Unable to get the OnAction property of the Button class"
    I I try to set the button caption before the OnAction then the error is "Unable to get the Characters property of the Button class"

  8. #8
    5 Star Lounger kmurdock's Avatar
    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?

  9. #9
    New Lounger
    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.

  10. #10
    5 Star Lounger kmurdock's Avatar
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •