Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    May 2005
    Location
    Wilcox, Saskatchewan
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    button for macros (excel)

    Hello all out there,

    I am trying to add buttons on to my work sheet to make the macro activation easier, how do I do this?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: button for macros (excel)

    Activate the Forms toolbar.
    Click on the Command button icon on the toolbar.
    Drag a rectangle on your worksheet.
    You'll be prompted to assign a macro to the button.
    You can edit the properties of the button, move and resize it etc.
    Once you click outside the button, you stop editing it, and it will become active, i.e. clicking on it will stat the macro assigned to it.
    To edit the command button later on, right-click it.

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: button for macros (excel)

    Hi Seaner,

    Once you have the macro recorded, right click on the toolbars to get the list of toolbars!
    Choose forms from the list.
    On the forms toolbar, click on the Button button.
    Draw a button onto the sheet.
    It will prompt you to select a macro to attach to the button.
    Select the macro and choose OK.
    You can then edit the text on the button to wha ever you need.
    The moment you click off the button, it becomes active. When you click it now it will trigger the macro!

    PS: to edit the button, right click on it...

    Hope this is clear!
    Regards,
    Rudi

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Ankeny, Iowa, USA
    Posts
    298
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: button for macros (excel)

    I see both Hans and Rudi suggest doing this through the Forms toolbar. I've always used the Control Toolbox to create a button. Is there any reason to choose one over the other (forms vs controls)? I don't think I'm clear on what the differences are, if there are any.

    Thanks!

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: button for macros (excel)

    Both command buttons work well, but the controls from the Forms toolbar and from the Control Toolbox have slightly different properties. You can assign a macro directly to a command button from the Forms toolbar, whereas a command button from the Control Toolbox has an On Click event procedure in the worksheet module, from which you can call a macro. The caption on the command button from the Forms toolbar can be rotated +90 or -90 degrees, and the background color of the command button from the Comtrol Toolbox can be changed, etc.
    You can use whichever you like best.

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Ankeny, Iowa, USA
    Posts
    298
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: button for macros (excel)

    Thanks for the explanation...that's helpful.

    -Brett

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: button for macros (excel)

    General comments on the Forms vs control toolbox items:

    I find the Forms toolbar items easier to use and setup, but are much more limited in formatting than the equivalent items in control toolbox.

    Control toolbox items can have code triggered by many different events so are more versatile/customizable (which is why they may be more difficult to use)

    Forms combobox and listboxes can be based on a dynamic range name (eg with OFFSET) and update automatically when the range changes. Control toolbox have to be reset if the range changes (even with a dynamic range name).

    The Forms has the big advantage in that they can be added to chart sheets and chart objects which can add functionality directly to the chart.

    Steve

  8. #8
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Ankeny, Iowa, USA
    Posts
    298
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: button for macros (excel)

    Thanks for your comments, Steve. Very helpful.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •