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!

 

[mikerickson]

The Command Button is a control in its own right, as well as being on a MultiPage.

I would expect something like

Sub CommandButton1_Click()
    do stuff
End Sub

 

[makke]

I wish it was that simple. Unfortunately, the Sheet object is not aware of CommandButton1 in its own right. You can verify this by seeing that CommandButton1 is not available in the drop-down with General, Worksheet, and MultiPage1 in the Code Editor... Just like TextBox1 has to be addressed through MultiPage1.Page1.Frame1.TextBox1, so I think does the button...

 

[Jaafar Tribak]

edited

 

[Jaafar Tribak]

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!

I have never seen a MultiPage control directly on a worksheet.

I have just tried adding one and the multipage was added successfully but when I tried adding a textbox or a commandbutton to it, the controls didn't stick to the MultiPage so I don't know how you achieved adding controls to the MultiPage !!

Have you inherited this workbook , or was it you who built the multipage and its controls ?

Can you upload a copy of the workbook to a file sharing site and post a link here for us to see ? .. I am curious to see this.

 

[Domenic]

I have just tried adding one and the multipage was added successfully but when I tried adding a textbox or a commandbutton to it, the controls didn't stick to the MultiPage so I don't know how you achieved adding controls to the MultiPage !!

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.

 

[makke]

Exactly as Domenic said. Microsoft has a knack for making things obvious, don't they?

Here is a link to my test workbook: https://www.dropbox.com/s/oo1pizen5i5l6zz/STOP_Scripts_v1.0.xlsm?dl=0. The MAIN2 tab has the built-in form. However, since I could not figure out how to capture button clicks, I eventually stopped banging my head against the wall, gave up, and created a standard user form instead. You can launch if from the MAIN tab. Again, this is just a test spreadsheet, so set your expectations accordingly.

For situations like this, I believe that having the UI form right on the sheet is preferable to having to launch it. The workbook behaves more like the Excel people expect, the data in the form stays in the form when the workbook is saved (so you don't have to store it on a hidden sheet and then populate the form with VBA), you don't have an extra window floating around, and you don't have to constantly open and close the form.

Thank you.

 

[Leith Ross]

Hello makke,

Here is the code for the Command Button so it will put text into TextBox1.

Private Sub CommandButton1_Click()
    ActiveSheet.OLEObjects(1).Object.Pages(0).Controls("TextBox1").Value = "Some Text"
End Sub

 

[makke]

Leith,

The workbook that I linked above already has a working button that can put text into TextBox1. The code is:

Private Sub CommandButton1_Click()
    MultiPage1.Pages(0).Frame1.TextBox1.Text = "Some Text"
End Sub

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.

 

[mikerickson]

I found an ugly workaround that uses Class Modules.
Insert a class module, name it clsSheetMultipage and put this code in it.

' in code module clsSheetMultipage

Public WithEvents pMultiPage As MSForms.MultiPage
Public WithEvents internalButton 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 internalButton = inMP.Pages(0).Controls("CommandButton1")
End Property

Private Sub internalButton_Click()
    pMultiPage.Parent.Butt*******
End Sub

Then put this code in the sheet code for that sheet.

' in code module for sheet 1

Dim myMP As clsSheetMultipage

Sub test()
    Set myMP = New clsSheetMultipage
    With myMP
        Set .MultiPage = Sheet1.MultiPage1
    End With
End Sub

Public Sub Butt*******()
    MsgBox "button clicked"
End Sub

Change the sub Butt******* as desired.