+ Reply to Thread
Results 1 to 11 of 11

Assign Macro to Active X Button

  1. #1
    Registered User
    Join Date
    10-03-2012
    Location
    New York
    MS-Off Ver
    Excel 2007
    Posts
    44

    Assign Macro to Active X Button

    Hi All,

    This is probably a simple task to complete, but for some reason I am having difficulty.

    I have a macro saved in ThisWorkbook named MyMacro.

    On a sheet in ThisWorkbook I added an Active X button. When I go the VBA coding for the Active X button I just want it to run MyMacro. I use the code Call MyMacro in the VBA coding for the Active X button. This fails everytime.

    Why is this happening? How can I assign the macro MyMacro to the Active X button using VBA coding?

  2. #2
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Assign Macro to Active X Button

    What exactly do you mean by "fails"; does it error, nothing happens?

    Can you show the command button procedure code?

    Where are the two procedures (CommandButton_Clck and MyMacro) located? MyMacro should be in a standard code module e.g.; Module1.
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  3. #3
    Forum Expert
    Join Date
    07-31-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4,070

    Re: Assign Macro to Active X Button

    Remove it from ThisWorkbook and create an independent module. I.E. right click on ThisWorkbook. select instert. module.

    After it is in its own module it should work fine.

  4. #4
    Registered User
    Join Date
    04-07-2014
    Location
    Port Talbot, Wales
    MS-Off Ver
    Excel 2007
    Posts
    54

    Re: Assign Macro to Active X Button

    Copy the code for your macro

    Right click the Active X button and select "View Code"

    Paste all your macro's code in to the active x code except the Sub name (something like Sub MyMacro()) and the End Sub, these should already be there as part of the Active X code

    Works for me (most of the time)
    Last edited by cobwebs; 09-11-2014 at 10:29 AM. Reason: misspelling
    Cobwebs, Alba Gu Brath (Scotland Forever)

  5. #5
    Registered User
    Join Date
    10-03-2012
    Location
    New York
    MS-Off Ver
    Excel 2007
    Posts
    44

    Re: Assign Macro to Active X Button

    Thanks for the quick responses.

    If I press the button, nothing happens.

    If I run the code line by line when it reaches Call MyMacro it states sub not defined.

    CommandButton_Click is a private sub located in Sheet1.
    MyMacro is located in ThisWorkbook.

    Why would the macro only be recoginized if it was part of the modules? How can the macro be called if it is saved in ThisWorkbook?

  6. #6
    Registered User
    Join Date
    04-07-2014
    Location
    Port Talbot, Wales
    MS-Off Ver
    Excel 2007
    Posts
    54

    Re: Assign Macro to Active X Button

    Select "Developer" from the ribbon the click on "Design" before right clicking the Active X button

  7. #7
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Assign Macro to Active X Button

    Quote Originally Posted by Excel Guy 123 View Post
    Why would the macro only be recoginized if it was part of the modules? How can the macro be called if it is saved in ThisWorkbook?
    Why do you have it in ThisWorkbook? It should be in a standard code module. Try it and see what happens.

  8. #8
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Assign Macro to Active X Button

    Quote Originally Posted by Excel Guy 123 View Post
    How can the macro be called if it is saved in ThisWorkbook?
    Please Login or Register  to view this content.
    But unless you have a good reason for it to be in ThisWorkbook, it really ought to be in a normal module as others have said.
    Remember what the dormouse said
    Feed your head

  9. #9
    Registered User
    Join Date
    10-03-2012
    Location
    New York
    MS-Off Ver
    Excel 2007
    Posts
    44

    Re: Assign Macro to Active X Button

    I moved the macro to the modules and it still fails.

    The message is complie error: Expected variable or procedure, not module.

    Please Login or Register  to view this content.

  10. #10
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Assign Macro to Active X Button

    Did you rename the module 'MyMacro'?
    If posting code please use code tags, see here.

  11. #11
    Registered User
    Join Date
    10-03-2012
    Location
    New York
    MS-Off Ver
    Excel 2007
    Posts
    44

    Re: Assign Macro to Active X Button

    Placing the macro in a normal module solved the problem. I must have accidently hit a key in the macro name when testing it previously...

    Thanks all for your help.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Assign a Keyboard Shortcut to an Active X Command Button
    By HangMan in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 07-18-2011, 08:32 AM
  2. Using VBA to assign a macro to a button
    By globalpontoon in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-25-2011, 04:10 AM
  3. How to assign a macro to a button?
    By sailor64 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-03-2009, 01:34 PM
  4. Assign Macro to a Button
    By tt388 in forum Excel General
    Replies: 1
    Last Post: 10-26-2008, 09:40 AM
  5. A macro to create a form button, assign a macro and name the button
    By cl361 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-11-2008, 01:07 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1