Load Combo and List Boxes At Form Startup
When Excel forms are used, all Combo and List boxes need to be loaded at form startup. The example below illustrates two methods. The first method, which loads a combo box, builds an array of vendor names that are stored in a worksheet, and then using a single command, initializes the combo box with values from the array. The second example loads a list box one entry at a time.
This example uses the UserForm_Initialize() event, which is activated when a form starts to load. By the time it is fully rendered for the user, both the combo box and list box will be initialized.
Program Code
Option Explicit
Option Base 1
Private Sub UserForm_Initialize()
' **********************************************
' This is triggered by the Excel Form Open Event
' **********************************************
' **********************************************
' Define Workbook and Worksheet Variables
' **********************************************
Dim wkbLoadForm As Workbook
Dim wksLoadForm As Worksheet
Dim wksVendorNames As Worksheet
' **********************************************
' Define The Vendor Name Array so it can be
' redimensioned to the exact number of entries
' **********************************************
Dim strVendorName() As String
' **********************************************
' Other Variables
' **********************************************
Dim lngLastVendorDataRow As Long
Dim lngVendorRow As Long
Dim lngVendorArrayPtr As Long
' ***********************************************
' Initialize The Workbook and Worksheet Variables
' ***********************************************
Set wkbLoadForm = ThisWorkbook
Set wksLoadForm = Sheets("MainSheet")
Set wksVendorNames = Sheets("VendorNames")
' **************************************************
' Redimension Vendor Array To Arbitrary Large Number
' **************************************************
ReDim strVendorName(5000)
' *********************************************************************
' Find The Last Row For Vendor Names In The Worksheet
' *********************************************************************
lngLastVendorDataRow = wksVendorNames.Cells(Rows.Count, "A").End(xlUp).Row
' *********************************************************************
' Load the array with vendor names
' Row 1 of vendor names is a header, so start with row 2
' *********************************************************************
lngVendorArrayPtr = 0
For lngVendorRow = 2 To lngLastVendorDataRow
lngVendorArrayPtr = lngVendorArrayPtr + 1
strVendorName(lngVendorArrayPtr) = wksVendorNames.Cells(lngVendorRow, 1).Value
Next lngVendorRow
' ************************************************************
' Redimension the vendor array to the actual number of entries
' ************************************************************
ReDim Preserve strVendorName(lngVendorArrayPtr)
' ************************************************************
' Load the Combo Box with one command
' ************************************************************
UserForm1.ComboBox1.List = strVendorName
' ************************************************************
' Demonstrate how to load a text box one entry at a time
' ************************************************************
' *********************************************************************
With ListBox1
.Clear
.AddItem "Alberta"
.AddItem "Bakersfield"
.AddItem "Chicago"
.AddItem "Detroit"
.AddItem "Eugene"
.AddItem "France"
.AddItem "Georgia"
.AddItem "Hawaii"
.AddItem "Idaho"
End With
End Sub