Userform dynamic controls-Add, remove controls dynamically and create event procedure of the dynamically added controls

When the contents of the userform can not be determined at design time, we need to add controls to and remove controls from the userform at run time. The event handling of the dynamically added controls is also to be designed dynamically using the class module. This page discusses how to work with a dynamic userform.

1. Userform at design time

Maximize the design of the required controls at design time to leverage the built-in functions including the event handling.
For example, the procedure related to the Listbox designed at design time is written in the Userform code module.

Private Sub ListBox1_Click()
    If ListBox1.ListIndex <> -1 Then
        Call txtRefresh(ListBox1.ListIndex + 1)
    End If
    UpdateControls_udc	
End Sub
			

The reserved space is arranged at design time for controls to be added at run time.

2. Dynamic controls at run time

The functions for the dynamic controls are Add, Remove and Event handling related to the dynamically added controls.

2.1 Add controls dynamically

Controls are added using the Add method.
Controls.Add(bstrprogid:="forms.textbox.1", Name:="textbox" & i, Visible:=True

			With Userform1.Frame1
				For i=1 to n
					Set ObjText = .Controls.Add(bstrprogid:="forms.textbox.1", Name:="textbox" & i, Visible:=True)
					With ObjText
						.Height = 16: .Width = 150
						.Top = 6 + 30 * (i - 1): .Left = 108
						.Font.Bold = False
						.Font.size = 8
						.BorderStyle = 1
						.BackColor = RGB(255, 255, 255)
						.Text = ""
						BottomPosition = .Top
					End With
				Next i
			End with
			

The top position of the last added control is read to decide the size (height, or width) of dynamic scrollbar control.

2.2 Add Scrollbar dynamically

Scrollbar is added when the dynamically added controls take up larger space than the reserve space arranged at design time. Show Scrollbar of the Frame and set height of the scrollbar equal to the full coverage of the added controls. This will allow all the added controls to be visible by scrolling.

			With Userform1.Frame1
				BottomPosition = BottomPosition + 32
				If BottomPosition > 216 Then
					.ScrollBars = fmScrollBarsVertical
					.ScrollHeight = (BottomPosition - TopPosition)
					.ScrollWidth = 12
					.ScrollTop = 0
				End If
			End With 
			

2.3 Remove controls dynamically.

Bear in mind that the controls added at the design time are not able to be removed. Removing controls is made using Remove method looping through all the controls using For Each ... Next structure.

			With Userform1.Frame1
				For Each ctrl In .Controls
					.Remove ctrl.Name
				Next
			End with
			

2.4 Event procedure of the dynamically added controls

The event handling of the dynamically added controls are written in a class module (named cls_udc).

Public WithEvents Tbx As MSForms.TextBox
Private Sub Tbx_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    Dim tbxno As Long
    Cancel = True
    tbxno = CInt(Right(Tbx.Name, Len(Tbx.Name) - 7))
    Call DataFind(tbxno, Tbx.Text)
End Sub
			

The class module is instantiated to make a new object in a standard module. This is done using New keyword at the general declaration section.
Dim c_udc() As New cls_udc

The dynamically added controls ObjText are assigned to the object C_udc().Tbx in the class module, enabling to activate the dynamically added controls.

			Set ObjText = .Controls.Add(bstrprogid:="forms.textbox.1", Name:="textbox" & i, Visible:=True)
            Set c_udc(i).Tbx = ObjText
			

3. Sample Macro

The sample Macro that can be downloaded demonstates how to develop a Userform with the dynamically added controls.
The brief operation procedure is described below.

1. You can create a kind of User Interface, assigning the sub procedure udc_Commence to Quick Access Toolbar or Customize Ribbon or a shape added. Visit the page for details User Interface
2. Select an item in the listbox at top right to fill the controls inside the frame.
3. Change text in the text box inside the frame and double click the text box to alert the row number of the item found.
4. Press OK to enable any change in textbox to overwrite on the worksheet.

4. Download open-source workbook

Download Excel workbook with open-source code

---

Dated on: 13 November 2019