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

Question

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!

How did you insert the MultiPage control on a worksheet? I didn't think that was an option. You could instead try referencng the "page object": see ozgrid.com/Excel/free-training/ExcelVBA2/excelvba2lesson8.htm — Tim Williams
– Tim Williams, Commented May 4, 2018 at 22:33
Under Insert Active-X Controls there is an option for More Controls (last button) that brings up a whole bunch of things that can be inserted. Sorry, not in front of a PC right now to grab a screenshot. — MichaelA
– MichaelA, Commented May 4, 2018 at 22:33
I don't think this is possible. It's a shame though. You could however "fake" it with a tabstrip and a grey rectangular form beneath it, making your controls visible on whatever tab you click on. — horst
– horst, Commented May 7, 2018 at 15:08

ashleedawg · Accepted Answer · 2018-05-04 23:52:10Z

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!

answered May 4, 2018 at 23:52

ashleedawg

21.9k9 gold badges82 silver badges120 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

MichaelA Over a year ago

Thank you for trying. I actually have zero problems with my MultiPage directly on a sheet other than not being able to capture a button press. The MultiPage has Text Boxes that I can set with Active X buttons placed directly on the sheet. It has a List Box that is populated on Workbook open. Switching Tabs on the MultiPage works fine and generates a Click event I can capture. In other words, no problems other than not being able to capture the button press for a button in the MP. Just can't figure out the right syntax for the Sub name... 😩

ashleedawg Over a year ago

Correction: you have zero problems so far.

ashleedawg Over a year ago

Also, the button should be the easiest to add code to. Different behaviours on your machijnenand mine... that was i meant by "upredictable".... its a bad idea, you'll see

MichaelA · Accepted Answer · 2018-05-08 18:57:21Z

1

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!

answered May 8, 2018 at 18:57

MichaelA

859 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

How to: Access a Page in a MultiPage Control

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

How to: Access a Page in a MultiPage Control

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related