Running a macro from a command button

  xania 16:04 20 Jun 08
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 08

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 08

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 08

^

  VoG II 10:04 23 Jun 08

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 08

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.

AMD Ryzen release date, specifications and features: Three CPUs from the Ryzen 7 range now…

1995-2015: How technology has changed the world in 20 years

How the painting-like animated sequences in A Monster Calls were created by Glassworks Barcelona

Best iPhone games 2017 | Best iPad games 2017: 162 fantastic iOS games that you need to play right…