Search code examples
excelvbamodulecommandbutton

linking activeX control's command button to a module

okay so here is my question. Is it possible to call a module from an activeX Control command button on the Excel sheet?

Here is the scenario: i have a sheet called Menu. On Menu, there is 10 activeX Control command buttons.I need the command button to call or linked it to a module. I haven't the slightest idea on how this could be achieved. Is it even possible? If so, how do i go about doing it? if not is there an alternative?

enter image description here

Thank you very much.

Solution
    1. Open the VB Editor (Alt+F11)
    2. Under 'Microsoft Excel Objects' in the Project Explorer window, right click the sheet that the buttons appear on and select 'View Code'

    View Code

    1. In the upper left-hand drop down, select the appropriate command button

    Command Button

    1. This will add the following code into the appropriate worksheet module

      Private Sub CommandButton21_Click()
'Your on click actions go here
End Sub

    Which means now we can do actions (call our subroutine, for example)

        Private Sub CommandButton21_Click()
        Call MyMacro
    End Sub

    Now, MyMacro can be present either in the worksheet level code that the Click event is in, or it can exist as a Public Sub somewhere in a module.

    Update

    To show a userform use the name of the userform with the Show method. The default name is UserForm1 (or UserForm2 or UserForm3 etc. depending on how many you add).

    If you want to show this userform on click you could use this code:

    Private Sub CommandButton21_Click()
        UserForm1.Show
End Sub