Macro Buttons

singingsister

Board Regular
Joined
Jun 13, 2006
Messages
145
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?
:eek:
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Stormseed

Banned
Joined
Sep 18, 2006
Messages
3,274
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?
:eek:

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 :cool:
 

singingsister

Board Regular
Joined
Jun 13, 2006
Messages
145
That's great but we wanted the buttons on the actual spreadsheet rather than on the toolbar.

Can this be done?
 

Stormseed

Banned
Joined
Sep 18, 2006
Messages
3,274
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

Board Regular
Joined
Jun 13, 2006
Messages
145

ADVERTISEMENT

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

Stormseed

Banned
Joined
Sep 18, 2006
Messages
3,274
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

Legend
Joined
May 15, 2003
Messages
29,536

ADVERTISEMENT

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

Board Regular
Joined
Jun 13, 2006
Messages
145
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!
 

merce33

Active Member
Joined
Oct 27, 2006
Messages
277
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?
 

Forum statistics

Threads
1,136,649
Messages
5,676,991
Members
419,667
Latest member
MegEri

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top