Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Wellington, Wellington, New Zealand
    Posts
    378
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Access macro from worksheet (Any version)

    Hi

    A workbook contains a macro that is only relevant to one of the worksheets (tabs). Is it possible to have a button/menu/whatever that is only visible when that worksheet is selected? I had in mind something like Word's MacroButton field, but can't find an equivalent in Excel.

    Can do?

    PS: I'm not worried about expert users finding a way around it (for example, by going Tools|Macros and running the macro from there).

    Thanks
    Dale

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

    Re: Access macro from worksheet (Any version)

    You can place a command button on the worksheet itself. Both the Forms toolbar and the Control Toolbox contain a command button.

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Wellington, Wellington, New Zealand
    Posts
    378
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Access macro from worksheet (Any version)

    Thanks, Hans

    I've inserted a command button into the sheet and double-clicked it, which takes me to the VB editor. I've put a call to my macro into "Private Sub CommandButton1_Click()".

    How does the user activate the macro? (If I click the button, it just takes me to the VB Editor.)

    Thanks
    Dale

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

    Re: Access macro from worksheet (Any version)

    You have inserted a command button from the Control Toolbox. This has automatically turned on design mode. The first button on the Control Toolbox toggles design mode on/off. If design mode is off, clicking the command button executes its On Click procedure. If design mode is on, you can move and resize the button, set its properties (button next to the design mode button) and double click it to edit the code.

    Make sure that design mode is off before turning the workbook over to the end users.

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

    Re: Access macro from worksheet (Any version)

    The "Button" button from the Forms toolbar is easier to use, as it is not necessary to deal in VBA. The moment you draw up a button on the sheet, you will be prompted to assign a macro to it. This button is useful for those who are not cumfortable with VBA, but on the negative, you sacrifice a lot of flexibility regarding property settings, and it is also much easier for users to tamper with the button when right-clicking it. For the normal end-user is is great though!

    If you are not interested in a worksheet button, you can use VBA (linked to the ActivateSheet event) to generate a toolbar button or menu command at runtime! This will require a little more coding...but it is also very useful!
    Regards,
    Rudi

Posting Permissions

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