November 4, 2017

How to Add List Box Controls on UserForm at Runtime

How to add List Box controls on UserForm at runtime and populate them automatically using VBA. Last time we learnt how to add label controls at runtime and in the second last video we created textboxes at runtime. Watch the video to learn how to create list boxes at run time:


Watch this video on YouTube.


Here’s the complete VBA code including the code for textboxes, labels and list boxes.

Dim numbertxt As Long ‘declare global variable

Private Sub UserForm_Initialize()
Dim i As Long
numbertxt = InputBox(“Enter no of text-boxes and labels you wish to create at run-time”, “Enter TextBox & Label Number”)
Dim txtB1 As Control
For i = 1 To numbertxt
Set txtB1 = Controls.Add(“Forms.TextBox.1”)
With txtB1
.Name = “txtBox” & i
.Height = 20
.Width = 50
.Left = 70
.Top = 20 * i * 1
End With
Next i

Dim lblL1 As Control
For i = 1 To numbertxt
Set lblL1 = Controls.Add(“Forms.Label.1”)
With lblL1
.Caption = “Label” & i
.Name = “lbl” & i
.Height = 20
.Width = 50
.Left = 20
.Top = 20 * i * 1
End With
Next i

Dim q As Long
For q = 1 To numbertxt
Controls(“lbl” & q) = Cells(1, q)
Next q

Dim lstbox As Control
numberlst = InputBox(“Enter no of list-boxes you wish to create”, “ListBoxes Number”)
For r = 1 To numberlst
Set lstbox = Controls.Add(“Forms.ListBox.1”)
With lstbox
.Name = “List” & r
.Height = 60
.Width = 50
.Left = 150
.Top = 20
End With
If lstbox.Name = “List1” Then
Controls(“List1”).List = Array(“Tata”, “Vodafone”, “Airtel”, “Jio”)
ElseIf lstbox.Name = “List2” Then
lstbox.Left = 220
Controls(“List2”).List = Array(“Two”, “Three”, “Four”, “Five”, “Six”)
End If
Next r

End Sub

Private Sub CommandButton1_Click()
Dim p As Long
Dim erow As Long
erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row


For p = 1 To numbertxt

Cells(erow, p) = Controls(“txtBox” & p).Text

Next p
Cells(erow, 6) = Controls(“list1”).Value
Cells(erow, 7) = Controls(“list2”).Value

End Sub

Private Sub CommandButton3_Click()

Dim ctl As Control
For Each ctl In Me.Controls
If TypeName(ctl) = “TextBox” Or TypeName(ctl) = “ListBox” Then
ctl.Value = “”
End If
Next ctl

End Sub

Private Sub CommandButton2_Click()
Unload Me
End Sub

Further Reading:

Adding UserForm controls at runtime