Dynamically Duplicating a page in a Multipage

Question

Good afternoon, So I have been assigned a task where I'm supposed to take a Calendar Userform I made and implement the ability to duplicate as many calendars as needed as tabs on a multipage Userform.

My question is : Is it possible to do so dynamically? I copied over all the controls over to another tab to test it, but all the control buttons get renamed and I'm not able to name them the the same name as the buttons on the 'master page' because of the ambiguity error when naming 2 buttons the same on one userform.

I could write new code using the new button names, but this would not be able to be done dynamically since for ever new tab added, I'd have to have code ready for it prior to creation.

Any help would greatly be appreciated.

Answer 1

First of all I would recommend looking at Thomas Inzina's answer. However, in one of my worksheets I did the same without relying on WithEvents, so I wanted to show the alternative method.

This method sets names of the controls along a predetermined format (e.g. "Input1_" (counter + 1)) so the controls are easily referenced from other operations.

The controls themselves are within Frames with set Captions, so I can still reference them after they've been copied.

I've edited the code somewhat since I stripped it from a longer procedure, but hopefully it's still intact.

Dim Ctrl As msforms.Control
Dim Mpage As msforms.Control
Dim Ctrl2 As msforms.Control
Dim pge As msforms.Page
Dim L As Double, R As Double
Dim PageName As String, PageTitle As String
Dim counter As Long

counter = 0
Set Mpage = Me.Controls("Multipage_1") 'set Multipage

'count current number of tabs within MPage
For Each pge In Mpage.Pages    
    counter = counter + 1
Next pge

'set name/title for new page
PageName = "Tab_" & (counter + 1)
PageTitle = "Tab " & (counter + 1)

With Mpage    'add tab
    .Pages.Add PageName, PageTitle
    .Pages(0).Controls.Copy
    .Pages(counter).Paste
End With

'get position of original frame (controls are within this frame)
For Each Ctrl In Mpage.Pages(0).Controls    
    If TypeOf Ctrl Is msforms.Frame Then
        L = Ctrl.Left
        R = Ctrl.Top
        Exit For
    End If
Next

'apply position to new frame
For Each Ctrl In Mpage.Pages(counter).Controls    
    If TypeOf Ctrl Is msforms.Frame Then
        Ctrl.Left = L
        Ctrl.Top = R
        Exit For
    End If
Next

'renames input-controls and removes copied values by looping through frames
'that contain the controls, since frame-captions can be duplicates
For Each Ctrl In Mpage.Pages(counter).Controls    
    If TypeOf Ctrl Is msforms.Frame Then
        Select Case Ctrl.Caption
            Case "Input1"
                For Each Ctrl2 In Ctrl.Controls
                    Ctrl2.Name = "Input1_" (counter + 1)
                    Ctrl2.Text = ""
                Next Ctrl2
            Case "Input2"
                For Each Ctrl2 In Ctrl.Controls
                    Ctrl2.Name = "Input2_" (counter + 1)         
                    Ctrl2.Text = ""
                Next Ctrl2
            Case "Input3"
                For Each Ctrl2 In Ctrl.Controls
                    Ctrl2.Name = "Input3_" (counter + 1)
                    Ctrl2.Text = ""
                Next Ctrl2
        End Select
    End If
Next Ctrl

Answer 2

Demo Workbook

The key to handling this type of problem is to create a class to hold references to the newly created controls. Using WithEvents will allow you to handle the events of the referenced controls.

Read about WithEvents here: Events And Event Procedures In VBA

In order to make this work you'll have to Set references between the newly created Page controls and their doppelgangers in your class. You'll also need to keep the class references alive by adding them to a global collection, dictionary or array.

In my example I created a subroutine that will iterate over the template page controls, creating and copy the code necessary to declare the variable and set the references into the Windows ClipBoard.

The final step is to copy the event code from the Userform into the class module.

Download Demo Workbook for the code example.

Alternatively, you could replace the Multipage control with a TabStrip control. The difference is that you can have the controls on a TabStrip span across all the Tabs. You could than use the TabStrip1_Change() to update the controls based on the selected tab (TabStrip1.SelectedItem).

Answer 3

use a prefix for the control names. eg. tab1_button1, tab2_button1, tab33_button1. then have only one one event handler that services all the events (button presses, checkbox clicks)

here is some info using one sub for multiple buttons in excel vba