Running a macro from a command button

  xania 16:04 20 Jun 2008
Locked

I am setting up a workbook of spreadsheets with a set of macros, and I want to be able to place buttons on the spreadsheets to run specific macros at specific times. I know that I can draw command button using the Button tool on the Control ToolBox, but this does not seem to have all the features I need. I cannot attach a macro to it and I cannot control the background and text colours - in fact except when I first create it, I don't seem to be able to do much with it. The only right click options that appear - and only this when I've just created it are:

Cut
Copy
Paste
Properties
View Code - I kown I can enter macro code here, but not record a macro or assign an existing macro
CommandButton Object - I can actuall amend the wording on the button but only in very basic terms
Grouping
Order
Format Control

Is there another way to place a button on the work sheet and then assign a macro? I have seen a button with right click options to:
Cut
Copy
Paste
Edit Text
Grouping
Order
Assign Macro
Format Control

I actually found this on a bespoke macro to create a contens list of spreadsheets.

I'm currently workng with Excel 2003, but we are about to migrate to 2007.

  bstb3 16:17 20 Jun 2008

Hi Xania

You can edit the button completely through the properties option. Select it and you have a list of the properties, including background colour, font etc.

If you want to run a macro by clicking the button, the easiest way is to choose view code.
If you enter the following (some is done for you) - assuming the macro is called macro1 :

Private Sub CommandButton1_Click()
Call macro1
End Sub

then that ties the macro to the button :)

hope this helps.

Chances are the other options you have seen before were from an older version of Excel.

  xania 17:20 20 Jun 2008

Hi bstb3. Thanks for your rapid response. I fear you may be right. I have noticed how later versions of Office seems to lose some of the better facilities in the interests of someone else!!!

However, I'll keep this open just a little longer in case VOG has some ideas.

  xania 09:54 23 Jun 2008

^

  VoG II 10:04 23 Jun 2008

Don't know how I missed this except that I am in a different timezone at present.

You have added an ActiveX button from the Controls toobar. If you add a button from the Forms toolbar you will get the Assign Macro option on right clicking it.

  xania 11:12 23 Jun 2008

Thanks VOG. I knew it was there somewhere, but couldn't remember where. That's one problem solved!!

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

OnePlus 5 review

Alice Saey's mesmerising animation for Dutch singer Mark Lotterman

iPad Pro 10.5in (2017) review

Comment booster votre iPhone ?