Detect Button Press Event on an Excel Sheet MultiPage Form (NOT a VBA UserForm MultiPage)

[makke]

I am struggling to figure out how to detect a button press event on a MultiPage form that resides directly on an Excel sheet. The form is not generated using VBA, it is built directly on the sheet using Developer > Insert > ActiveX Controls > More Controls > Microsoft Forms 2.0 MultiPage.

I expect the syntax to be something like:

Private Sub MultiPage1_Page1_Frame1_CommandButton1_Click()
​

Do Stuff
​

End Sub

​

which is analogous to

MultiPage1.Page1.Frame1.TextBox1 = "Some Text"

​

that works just fine... In other words, I can set values for text boxes or combo boxes on this form, I just can't capture a button press event for a button in the form.

​

I can't believe Microsoft would provide functionality for building a form like this without an ability to detect and react to a button press. Any help would be greatly appreciated!

​

Thank you!

 

[Jaafar Tribak]

First, right-click the MultiPage control > MultiPage Object > Edit. Then, right-click the MultiPage control again, and select Toolbox from the context menu. Now you'll be able to add controls from the Toolbox onto the MultiPage control.

Thanks Domenic for showing us the steps required for adding the controls ... I did right-click the Multipage at one point but I somehow missed the edition submenu .

 

[Jaafar Tribak]

However, that's not what I was asking about. I was asking how to detect a press of a button that is in the MultiPage. For example, the "Choose TEMP / SPC File" button.

Thank you.

Hi makke,

One way you could achieve what you want is as follows :

Code in the worksheet module where the Multipage is embeeded :

Private WithEvents cmbtn As CommandButton

Private Sub MultiPage1_GotFocus()
    Set cmbtn = Me.MultiPage1.Pages(1).Frame1.CommandButton1
End Sub

Private Sub cmbtn_Click()
    MsgBox "You clicked : " & cmbtn.Name
End Sub

 

[makke]

Holy Mackerel!

How in hell did you figure that out?

THANK YOU!

Here is the full code with 2 buttons for a sheet called "MAIN2" for those, like me, who need a little extra help.

Insert a new Class Module and name it clsSheetMultipage using the Properties Window (View > Properties Window, or F4)

' In Class Module clsSheetMultipage

Public WithEvents pMultiPage As MSForms.MultiPage
Public WithEvents internalButton1 As MSForms.CommandButton
Public WithEvents internalButton2 As MSForms.CommandButton

Property Get MultiPage() As MSForms.MultiPage
    Set MultiPage = pMultiPage
End Property

Property Set MultiPage(inMP As MSForms.MultiPage)
    Set pMultiPage = inMP
    Set internalButton1 = inMP.Pages(0).Controls("CommandButton1")
    Set internalButton2 = inMP.Pages(0).Controls("CommandButton2")
End Property

Private Sub internalButton1_Click()
    'Do stuff here or call the Button1_Click sub on the parent sheet of the multiPage
    Call pMultiPage.Parent.Button1_Click
End Sub

Private Sub internalButton2_Click()
    'Do stuff here or call the Button1_Click sub on the parent sheet of the multiPage
    Call pMultiPage.Parent.Button2_Click
End Sub

In the Sheet Code for, in my case, "MAIN2":

Private Sub Worksheet_Activate()
    Call multiPageButtonTest
End Sub

Sub multiPageButtonTest()
    Set myMP = New clsSheetMultipage
    With myMP
        Set .MultiPage = Worksheets("MAIN2").MultiPage1
    End With
End Sub

Public Sub Button1_Click()
    MsgBox "Hip-Hip, Hurray MAIN2 Button 1!"
End Sub

Public Sub Button2_Click()
    MsgBox "Hip-Hip, Hurray MAIN2 Button 2!"
End Sub

THANK YOU again, mikerickson!

 

[makke]

Actually, Jaafar's answer above also works and is much easier for my brain to comprehend.

THANK YOU!, Jaafar!