Programatically Add Button

[NicholasP]

Hi,

I'm trying to use VB to add a button in Excel. I've been playing around with this for the better part of the day, and I can't seem to figure it out.

So far, this is what I have:

x = ActiveWorkbook.Name
ActiveSheet.Buttons.Add(700, 15, 100, 37.5).Select

Selection.OnAction = x & "!ShowSheets"
Selection.Characters.Text = "Show Calculation Sheets"


ActiveSheet.Buttons.Add(700, 68, 100, 37.5).Select

With Selection
    .OnAction = x & "!HideSheets"
    .Characters.Text = "Hide Calculation Sheets"
End With

The problem is, this doesn't work (although it was working when I had it in the test file, so I'm not sure what I changed to make this not work).

If anyone has any suggestions, I'd be very grateful! Also, any links that anyone might think are useful for dealing with this topic would be much appreciated as well.

Thanks
Nick

 

[mikerickson]

What specificaly doesn't work?
Also, have you used the macro recorder to get the code you want.

 

[NicholasP]

I used the macro recorder, but it only adds a button and I'm not sure how to apply what I want to do after that....this code succesfully adds the buttons, but it errors on the lines of code:

Selection.OnAction = x & "!ShowSheets"

 .OnAction = x & "!HideSheets"

Basically two ways to say the same thing...the error states:

"Unable to set the OnAction property of the Button class"

Any thoughts?

 

[mikerickson]

This worked for me

With ActiveSheet.Buttons.Add(700, 15, 100, 37.5)
    .OnAction = "ShowSheets"
    .Characters.Text = "Show Calculation Sheets"
End With

 

[NicholasP]

yeah, I took out the reference to the Activeworkbook and it worked for me too....thanks!

Nick