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?

 

[Stormseed]

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?

Assume you have all the macros ready...

In Excel - Click on Tools --> Customize --> Commands tab --> Select Macros on the left side of the window --> Drag the "Custom Button" to a toolbar on the top of the Excel window --> Right Click on the button --> Click on Assign Macro --> Select a Macro from ur Macro list you want to assign to that Button...next time u click on the button to run the macro

 

[singingsister]

That's great but we wanted the buttons on the actual spreadsheet rather than on the toolbar.

Can this be done?

 

[Stormseed]

That's great but we wanted the buttons on the actual spreadsheet rather than on the toolbar.

Can this be done?

Ofcourse it can be....nothing is impossible...u will need to create a user form in order to create a button on the spreadsheet...the logic is the same u used to create a macro thru VBA !

 

[singingsister]

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

 

[Stormseed]

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...

 

[Smitty]

That's great but we wanted the buttons on the actual spreadsheet rather than on the toolbar.

Can this be done?

Why not?

You can draw a button from the Forms toolbar and put it where you want, then assign a macro to it when you're prompted.

If you goto Windows-->Freeze Panes beneath the buttons after that, then they'll stay fixed at the top of the display.

I find that handy when you have frozen headers anyway.

HTH,

Smitty

 

[singingsister]

I have now added the code to the button (I think), but I can't work out how to make the button appear on the spreadsheet.
HELP!

 

[singingsister]

DONE IT! Thanks!

 

[merce33]

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?