Detect Button Press Event on an Excel Sheet MultiP

I am struggling to figure out how to detect a button press event on a MultiPage form that resides directly on an Excel sheet. In other words, the form is not generated using VBA, it is built directly on the sheet.

I expect the syntax to be something like:

Private Sub MultiPage1_Frame1_CommandButton1_Click()
    Do Stuff
End Sub

However, that doesn't work because I think I also need to specify the page in the MultiPage. For example, a text box on the same form can be accessed via:

MultiPage1.Pages(0).Frame1.TextBox1.Text = "Some Text"

Unfortunately,

Private Sub MultiPage1_Pages(0)_Frame1_CommandButton1_Click()

gives a Compile error: Expected: identifier with the zero inside (0) selected.

So, how do I convert

MultiPage1.Pages(0).Frame1.CommandButton1

to a Sub name that will detect the button press event?

Thank you!

回答1:

I'm not sure but I think you may have stumbled onto a bug.

There is a Microsoft Forms 2.0 Control under More Controls, but I'm pretty sure it's only intended only for use on UserForms.

I tried adding it to a worksheet and got an error. However, once I added one to a UserForm and went back to the worksheet, I was able to add it... but things got "glitchy" for moment, and when I opened the Properties dialog for the bod, the font was poorly rendered.

All the documentation that I looked at (like this, this and this) only have examples of it being used on a UserForm, or in Outlook.

There are many types of ActiveX controls, and not all of them can be used anywhere. As a rule of thumb in Excel, it's best to stick to the controls that are built-in.

Also, from another forum:

Q: It seems that I could not find and add Multipage control into worksheet. How to add a Multipage control to Excel worksheet?

A: Unless you put it on a UserForm first, you can't display it on a Worksheet. The UserForm provides the user interface to VBA. The MultiPagecontrol is designed to work with this user interface, and not the Excel Worksheet. Is there a problem using the UserForm to display the MutliPage control? _{Source: Leith Ross}

This evidence combined tells me, even if you can get it to work, you shouldn't. There's no predicting how it will behave.

In case you decide to use a MultiPage on a UserForm, note that in your example above, MultiPage1 is the name of the control; that's not referring to "page 1". The control as a whole has a Click event which is not specific to a page:

Private Sub MultiPage1_Click(ByVal Index As Long)

For the sake of completeness, I'll paste in a complete code sample but once again: this is not recommended for a worksheet-based control.

How to: Access a Page in a MultiPage Control

The following example accesses an individual page of a MultiPage in several ways:

Using the Pages collection with a numeric index.
Using the name of the individual page in the MultiPage.
Using the SelectedItem property.

To use this example, copy this sample code to the Script Editor of a form. Make sure that the form contains a MultiPage named MultiPage1 and a CommandButton named CommandButton1.

Sub CommandButton1_Click()
    Dim PageName
    Set MultiPage1 = Item.GetInspector.ModifiedFormPages("P.2").MultiPage1

    For i = 0 To MultiPage1.Count - 1
        'Use index (numeric or string)
        MsgBox "MultiPage1.Pages(i).Caption = " & MultiPage1.Pages(i).Caption
        MsgBox "MultiPage1.Pages.Item(i).Caption = " & _
            MultiPage1.Pages.Item(i).Caption

        'Use Page object without referring to Pages collection
        If i = 0 Then
            MsgBox "MultiPage1.Page1.Caption = " & MultiPage1.Page1.Caption
        ElseIf i = 1 Then
            MsgBox "MultiPage1.Page2.Caption = " & MultiPage1.Page2.Caption
        End If

        'Use SelectedItem Property
        MultiPage1.Value = i
        MsgBox "MultiPage1.SelectedItem.Caption = " & _
            MultiPage1.SelectedItem.Caption
    Next
End Sub

(Source)

Now I'm going to delete that buggy worksheet and reboot; I'd suggest you do the same!

回答2:

Mikerickson and Jaafar Tribak on MrExcel.com figured it out:

https://www.mrexcel.com/forum/excel-questions/1054446-detect-button-press-event-excel-sheet-multipage-form-not-vba-userform-multipage-2.html

Big thanks to both of them!