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.
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.
The functions for the dynamic controls are Add, Remove and Event handling related to the dynamically added controls.
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.
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
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
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
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.
Dated on: 13 November 2019