Excel VBA Userform Multipage and Frame (copy/paste)

Question

I have a Userform Control Panel that I am making for a workbook. I have a page named #1, which is for the workbook sheet #1. I also have an 'Add sheet' button that copies the #1 page and creates a #2 page.

The problem is that the code for the controls on the #1 page do not work on the newly created #2 page. And I don't know what the page #2 controls are called so I can't make code for it beforehand.

This is the Copy/Paste code that I found somewhere. Page 0 is the General settings page and page 1 is the #1 page. I have a Frame taking up the whole Multipage area so it copies the frame and everything in it and copies it.

Option Explicit
Private Sub AddProgramButton_Click()

Dim l As Double, r As Double
Dim ctl As Control
Dim PAGECOUNT As Long

MultiPage1.Pages.Add

MultiPage1.Pages(1).Controls.Copy
PAGECOUNT = MultiPage1.Pages.Count
MultiPage1.Pages("Page" & PAGECOUNT).Paste
MultiPage1.Pages("Page" & PAGECOUNT).Caption = "#" & PAGECOUNT - 1

 For Each ctl In MultiPage1.Pages(1).Controls
    If TypeOf ctl Is MSForms.Frame Then
        l = ctl.Left
        r = ctl.Top
        Exit For
    End If
Next

For Each ctl In MultiPage1.Pages(PAGECOUNT - 1).Controls
    If TypeOf ctl Is MSForms.Frame Then
        ctl.Left = l
        ctl.Top = r
        Exit For
    End If
Next
End Sub

score 1 · Answer 1 · edited May 23 '17 at 11:59

OK, I haven't got much information from you to go with, but I can make this work by using the following method. If you want to use it, you will have to modify it to suit your own needs.

To follow this example, you will need to create a new UserForm, preferably in a new workbook, and follow my instructions below.

I have created a UserForm as you state, with a Multipage - currently I have pages 0 and 1 on it. Page 0 I am ignoring for the purposes of this example (you mentioned it was just the General Settings page).

Seperate from the Multipage, I have put the main CommandButton (the one which actually adds the new Page when it's clicked) and have named it AddProgramButton as you did.

On Page 1, I have a frame as you state. Within this frame, I have put a CommandButton, a TextBox and a ComboBox on mine. I don't know what your controls are, but you will need to follow my example for now.

Now we need to start entering the code. First, if you haven't already got one, insert a Standard Module in your project. At the top of this standard module, enter the following code:

Option Explicit

Public myButtonArr() As New CButton
Public myComboArr() As New CCombo
Public myTextBoxArr() As New CTextBox

Now, in your UserForm module, you should input the following (note that some of this is the information you first provided):

Option Explicit
Private Sub UserForm_Initialize()
    Dim ctl As Control
    For Each ctl In MultiPage1.Pages(1).Controls
        Select Case TypeName(ctl)
            Case Is = "CommandButton"
                ReDim Preserve myButtonArr(1 To 1)
                Set myButtonArr(1).myButton = ctl
            Case Is = "ComboBox"
                ReDim Preserve myComboArr(1 To 1)
                Set myComboArr(1).myCombo = ctl
                ctl.AddItem "A"
                ctl.AddItem "B"
            Case Is = "TextBox"
                ReDim Preserve myTextBoxArr(1 To 1)
                Set myTextBoxArr(1).myTextBox = ctl
        End Select
    Next ctl
End Sub

Private Sub AddProgramButton_Click()

    Dim l As Double, r As Double
    Dim ctl As Control
    Dim PAGECOUNT As Long

    MultiPage1.Pages.Add

    MultiPage1.Pages(1).Controls.Copy
    PAGECOUNT = MultiPage1.Pages.Count
    MultiPage1.Pages("Page" & PAGECOUNT).Paste
    MultiPage1.Pages("Page" & PAGECOUNT).Caption = "#" & PAGECOUNT - 1

     For Each ctl In MultiPage1.Pages(1).Controls
        If TypeOf ctl Is MSForms.Frame Then
            l = ctl.Left
            r = ctl.Top
            Exit For
        End If
    Next

    For Each ctl In MultiPage1.Pages(PAGECOUNT - 1).Controls
        If TypeOf ctl Is MSForms.Frame Then
            ctl.Left = l
            ctl.Top = r
            Exit For
        End If
    Next

    For Each ctl In MultiPage1.Pages(PAGECOUNT - 1).Controls
        Select Case TypeName(ctl)
            Case Is = "CommandButton"
                ReDim Preserve myButtonArr(1 To PAGECOUNT - 1)
                Set myButtonArr(PAGECOUNT - 1).myButton = ctl
            Case Is = "ComboBox"
                ReDim Preserve myComboArr(1 To PAGECOUNT - 1)
                Set myComboArr(PAGECOUNT - 1).myCombo = ctl
                ctl.AddItem "A"
                ctl.AddItem "B"
            Case Is = "TextBox"
                ReDim Preserve myTextBoxArr(1 To PAGECOUNT - 1)
                Set myTextBoxArr(PAGECOUNT - 1).myTextBox = ctl
        End Select
    Next ctl


End Sub

Now, for each control I have within the frame, we need to create a new Class. Insert three new Class Modules. You must name these as follows:

CButton

CCombo

CTextBox

Now open the CButton class module, and insert the following code:

Option Explicit

Public WithEvents myButton As MSForms.CommandButton

Private Sub myButton_Click()
    MsgBox "You clicked the button on one of the pages"
End Sub

Next, open the CCombo class module, and insert the following code:

Option Explicit

Public WithEvents myCombo As MSForms.ComboBox

Private Sub myCombo_Change()
    MsgBox "You changed the value of the ComboBox on one of the pages"
End Sub

Finally, open the CTextBox class module, and insert the following code:

Option Explicit

Public WithEvents myTextBox As MSForms.TextBox

Private Sub myTextBox_Change()
    MsgBox "You changed some text in the TextBox on one of the pages"
End Sub

Now, if you test your Userform, it should work. You should hopefully be able to modify my example to match your own requirements.

Note: the events in the class module will produce an identical response regardless of which page is selected. You will have to modify the code yourself (or provide more information) to "personalise" the results.

BTW you probably found your original code here: Copy Elements From One Page To Another in Multipage with VBA in Excel.

Excel VBA Userform Multipage and Frame (copy/paste)

1 Answers1