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

Published by

Dinesh Kumar Takyar

Welcome to exceltrainingvideos.com! My aim is to help you learn MS Excel including VBA. I always felt that a good way to learn something was to find solutions to problems in that domain. That is why I share these Excel videos with you. Mostly these are questions asked during my corporate training sessions. I've been training individuals and companies in computers since 1991. I did my B. Sc. (Vordiplom), M. Sc. (Diplom) and Ph.D. (Dr. rer. nat.) from Hamburg, Germany. The best thing about solving some of my visitor's questions is that I also gain new insights. For more Excel VBA solutions you may like to visit my YouTube channel: http://youtube.com/familycomputerclub For a structured Excel VBA training course online you can visit: https://www.youtube.com/excelvbaonline

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.