How do you control a button inside a multipage in excel VBA

Jovanni G picture Jovanni G · Mar 4, 2013 · Viewed 12.6k times · Source

I have a multipage, I was successfully able to copy elements of the first page which is my reference page to new pages which is created dynamically.

My question is, how do I set a commandbutton's actions inside a page in a multipage control? My goal is to click on the button from any page then pops up another form.

How do I do this? It's pretty hard to adjust from Android to VB. I really appreciate any help from you guys.

This is my code in cloning pages.

i = 0
MultiPage1.Pages.Add
MultiPage1.Pages(i).Controls.Copy
i = i + 1
MultiPage1.Pages(i).Paste
For Each ctl In Me.MultiPage1.Pages(i).Controls
     If TypeOf ctl Is MSForms.Label Then
           '~~~ code omitted
           Select Case ctl.Tag
                Case "startTime"
                        ctl.Caption = "4:00pm"
           End Select
     End If
Next

this is how it's going to look like. enter image description here

the button will concatenate all strings inside the page. the concatenated string will be shown on another userform.

Answer

Joseph picture Joseph · Mar 4, 2013

You probably would be better off creating a button in the ribbon so that it is available on all pages:

http://chandoo.org/wp/2012/02/27/how-to-add-your-own-macros-to-excel-ribbon/

EDIT:

My bad, I thought you meant a worksheet instead of a VBA MultiPage in a userform.

Check this out. I was able to make this work for me:

Assign code to a button created dynamically

Class1:

Option Explicit

Public WithEvents CmdEvents As MSForms.CommandButton

Private Sub CmdEvents_Click()
    MsgBox "yo"
End Sub

Userform with MultiPage object:

Option Explicit

Dim cmdArray() As New Class1

Private Sub CommandButton1_Click()
    Dim newControl As Control

    Set newControl = Me.MultiPage1.Pages(0).Controls.Add("Forms.CommandButton.1", "NewCommand", True)

    newControl.Object.Caption = "hello"


    newControl.Left = 50
    newControl.Top = 50

    ReDim Preserve cmdArray(1 To 1)
    Set cmdArray(1).CmdEvents = newControl

    Set newControl = Nothing
End Sub