assigning a macro to an activex command button

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: assigning a macro to an activex command button

  1. #1
    New Member
    Join Date
    Nov 2002
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default assigning a macro to an activex command button

     
    Help! Attempting to get a command button to run a simple pre-recorded macro. Zero experience of visual basic , office assistant telling me to type in the macro!!??!! Hello , what. Please help me or shoot me.Brain hurting.

  2. #2
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    73,092
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: assigning a macro to an activex command button

    If you CommandButton is from the Control ToolBox, right click it and choose View Code. You should something like this:

    Code:
    Private Sub CommandButton1_Click()
    
    End Sub
    You can copy your recorded code into there (excluding its Sub and End Sub lines) or run the existing Macro like this:

    Code:
    Private Sub CommandButton1_Click()
       Call Macro1
    End Sub
    where Macro1 is the name of your recorded Macro.

  3. #3
    MrExcel MVP Richie(UK)'s Avatar
    Join Date
    May 2002
    Location
    UK
    Posts
    3,329
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: assigning a macro to an activex command button

    Quote Originally Posted by PEACHY
    Please help me or shoot me.
    BANG!

    Seriously though, lets assume that you already have your recorded macro. If you press Alt&F11 you will be taken to the Visual Basic Editor (VBE). Here you should see, in the Project window, the VBAProject associated with your workbook. Under the project will be a folder called 'Modules' - double-click on this and it will show that it contains a module called 'Module1'. Within this module you will see the macro that you recorded, make a note of the name (probably Macro1).

    Now, go back to the Excel window where you have added the Command Button from the Controls Toolbox. Double-click on the CommandButton and you will be taken back to the VBE between some lines of code like this:
    Code:
    Private Sub CommandButton1_Click()
    
    End Sub
    Between those lines write the name of your macro (Macro1). Alternatively, you can type 'Module1' immediately followed by a full stop (.) - this will then give you the option to choose from the macros contained in Module1.

    Now go back to Excel and exit Desin Mode. That's it, whenever you click the button your routine will be executed.

    HTH
    Richie

  4. #4
    New Member
    Join Date
    Oct 2013
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: assigning a macro to an activex command button

    Tnx very helpfull even after all these years

  5. #5
    New Member
    Join Date
    Nov 2011
    Posts
    48
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: assigning a macro to an activex command button

    Quote Originally Posted by Richie(UK) View Post
    BANG!

    Seriously though, lets assume that you already have your recorded macro. If you press Alt&F11 you will be taken to the Visual Basic Editor (VBE). Here you should see, in the Project window, the VBAProject associated with your workbook. Under the project will be a folder called 'Modules' - double-click on this and it will show that it contains a module called 'Module1'. Within this module you will see the macro that you recorded, make a note of the name (probably Macro1).

    Now, go back to the Excel window where you have added the Command Button from the Controls Toolbox. Double-click on the CommandButton and you will be taken back to the VBE between some lines of code like this:
    Code:
    Private Sub CommandButton1_Click()
    
    End Sub
    Between those lines write the name of your macro (Macro1). Alternatively, you can type 'Module1' immediately followed by a full stop (.) - this will then give you the option to choose from the macros contained in Module1.

    Now go back to Excel and exit Desin Mode. That's it, whenever you click the button your routine will be executed.

    HTH
    Thank you so much!!!

  6. #6
    New Member
    Join Date
    May 2015
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: assigning a macro to an activex command button

    Hi,
    I used macros for my activex controls button. But it works wrong when I click (copies the same thing), but works properly when I run macro's itself. How can I fix it?
    code:

    Private Sub CommandButton1_Click()
    Sheets("Archive").Select
    ActiveSheet.unprotect Password:="111"
    Call Macro1
    Sheets("Archive").Select
    ActiveSheet.protect Password:="111"
    End Sub

    Thanks in advance

  7. #7
    Board Regular
    Join Date
    Nov 2016
    Posts
    96
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: assigning a macro to an activex command button

    Thanks Richie (UK)!!

    Clarifying that you need to exit design mode afterwards was useful, and saved me some time.

  8. #8
    New Member
    Join Date
    Apr 2016
    Posts
    25
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: assigning a macro to an activex command button

      
    Private Sub CommandButton1_Click()

    ' I have utilized the "Custom UI Editor For Microsoft Office", and made a Custom UI Ribbon
    ' The macro is called: Sub Process_Bookmarks(control As IRibbonControl)
    ' To get it to work here with the button, remove this part: control As IRibbonControl
    ' So, it will be renamed to: Sub Process_Bookmarks()

    Call Process_Bookmarks

    End Sub

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com