Macro Buttons

[singingsister]

I have a long document for which I have set up macros to automatically hide certain columns, as different people will want to view different information in the spreadsheet. I have successfully created the macros and watched it work, but I now want to add buttons to the top of the spreadsheet so all you have to do is click the relevant button for the information you wish to see and the macro is carried out.

HOW do I do this?

 

[merce33]

Re: onAction macro with a passed variable??

Any chance someone can talk me through how to do that?

Set MyButton = Activesheet.Buttons.Add (20,30,40,50)

'the numbers in the parenthesis are top, left, height & width

MyButton.Onaction = "RunThisCodeWhenClicked"

Sub RunThisCodeWhenClicked()

'your macro code here

End Sub

take care...

What if the Sub needs to pass a variable:
MyButton.OnAction = "RunThisCodeWhenClicked(ActiveSheet.Index)"

Sub RunThisCodeWhenClicked(SheetIndex as Integer)
' code is here
End Sub

I get 'variable undefined' errors when I try and do that, and if i remove the quotes it expects a function. if i add a sub "Call", more errors.
If it's not possible with OnAction, is there any other type of event handler to do this?

nevermind. figured it out.