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 four methods.
(1)
The first method, illustrated in the first section of "Program Code 1", 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.
(2)
The second method illustrated in "Program Code 1" loads a list box one entry at a time.
(3) The third method is the most sophisticated. It uses values from a worksheet to populate the combo boxes by calling a universal subroutine that can handle any combo box and data source. "Program Code 2" illustrates this method.
(4) Program Code 3 shows how to load and then access a multi-select listbox or combobox.
Examples 1-4 all use 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 boxs are initialized and ready to use.
Program Code 1
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
Program Code 2
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& The Following Code is in The Main Modules &&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
' **********************************************
' The Sub AddANewLine Is Activated By A
' Button the the Main Worksheet
' **********************************************
Option Explicit
Public Sub AddANewLine()
frmDataEntry.Show
End Sub
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& The Following Code is in the UserForm &&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
Option Explicit
' **********************************************
' Define Workbook and Worksheet Variables
' **********************************************
Dim wkbTestOfForm As Workbook
Dim wksJobSource As Worksheet
Dim wksProfiles As Worksheet
Private Sub UserForm_Initialize()
' **********************************************
' This is triggered by the Excel Form Open Event
' **********************************************
' **********************************************
' Other Variables
' **********************************************
Dim lngLastTechDataRow As Long
Dim lngLastCityDataRow As Long
Dim lngLastTypeDataRow As Long
Dim lngLastHomeDataRow As Long
Dim lngLastByDataRow As Long
Dim cmbComboBox As ComboBox
' ***********************************************
' Initialize The Workbook and Worksheet Variables
' ***********************************************
Set wkbTestOfForm = ThisWorkbook
Set wksJobSource = wkbTestOfForm.Sheets("JobSource")
Set wksProfiles = wkbTestOfForm.Sheets("Profiles")
' *********************************************************************
' Find The Last Row For Each Of The Columns Used to Load the Five
' Combo Boxes (Tech, City, Type, Home and By)
' *********************************************************************
lngLastTechDataRow = wksProfiles.Cells(Rows.Count, "A").End(xlUp).Row
lngLastCityDataRow = wksProfiles.Cells(Rows.Count, "B").End(xlUp).Row
lngLastTypeDataRow = wksProfiles.Cells(Rows.Count, "C").End(xlUp).Row
lngLastHomeDataRow = wksProfiles.Cells(Rows.Count, "D").End(xlUp).Row
lngLastByDataRow = wksProfiles.Cells(Rows.Count, "E").End(xlUp).Row
' *********************************************************************
' Load The 5 Combo Boxes (See Parameter Description Below)
' *********************************************************************
Set cmbComboBox = Me.cmbTech
Call LoadComboBox(wksProfiles, 1, 3, lngLastTechDataRow, cmbTech)
Call LoadComboBox(wksProfiles, 2, 3, lngLastCityDataRow, cmbCity)
Call LoadComboBox(wksProfiles, 3, 3, lngLastTypeDataRow, cmbType)
Call LoadComboBox(wksProfiles, 4, 3, lngLastHomeDataRow, cmbHome)
Call LoadComboBox(wksProfiles, 5, 3, lngLastByDataRow, cmbBy)
End Sub
' *********************************************************************
' This Code Is Activated By Clicking A Button on the User Form
' It Saves the Values from the Form to the Excel Worksheet
' *********************************************************************
Private Sub cmdAddRow_Click()
Dim lngLastTestOfFormRow As Long
Dim lngNewRow As Long
lngLastTestOfFormRow = wksJobSource.Cells(Rows.Count, "A").End(xlUp).Row
lngNewRow = lngLastTestOfFormRow + 1
wksJobSource.Cells(lngNewRow, 1).Value = Me.txtName
wksJobSource.Cells(lngNewRow, 2).Value = Me.txtAddress
wksJobSource.Cells(lngNewRow, 3).Value = Me.cmbTech
wksJobSource.Cells(lngNewRow, 4).Value = Me.cmbCity
wksJobSource.Cells(lngNewRow, 5).Value = Me.cmbType
wksJobSource.Cells(lngNewRow, 6).Value = Me.cmbHome
wksJobSource.Cells(lngNewRow, 7).Value = Me.cmbBy
Unload Me
End Sub
' *********************************************************************
' This Code Is Called By UserForm_Initialize
' It Is A Generic Procedure That Will Load Any Combo Box Using
' The Parameters That Are Passed To It
' Param 1 - The Worksheet Variable That Points To the Proper Worksheet
' Param 2 - The Column Number Of the Worksheet That Contains the
' cell values to be loaded to the Combo Box
' Param 3 - The Beginning Row number for the cell values to be loaded
' Param 4 - The Ending Row number fo the cell values to be loaded
' Param 5 - The Combo Box Variable that points to the Combo Box to
' be loaded
' *********************************************************************
Private Sub LoadComboBox(ProfileWorksheet As Worksheet, ColumnNumber As Long, BeginningRow As Long, EndingRow As Long, TargetComboBox As ComboBox)
Dim i As Long
TargetComboBox.Clear
For i = BeginningRow To EndingRow
TargetComboBox.AddItem (ProfileWorksheet.Cells(i, ColumnNumber))
Next i
End Sub
Program Code 3 - Load And Access MultiSelect Listbox
' ****************************************************************
' How To Evaluate a Multi-Select List Box
' After The Values Are Selected
' ****************************************************************
With Me.lstCountry
For i = 0 To .ListCount - 1
If .Selected(i) Then
intCountriesSelected = intCountriesSelected + 1
strCountrySelected(intCountriesSelected) = .List(i)
End If
Next i
End With
If intCountriesSelected < 1 Or intCountriesSelected > 4 Then
MsgBox ("You Must Select From 1 To 4 Countries")
Exit Sub
End If
' ****************************************************************
' How To Load A Listbox And Combo-BoxWith Initial Values
' Calling From The UserForm_Initialize Event
' ****************************************************************
Private Sub UserForm_Initialize()
Call LoadListBox(wksCostingVariables, 1, 2, lngLastRowInCountry, Me.lstCountry)
Call LoadComboBox(wksCostingVariables, 4, 2, lngLastRowInGender, Me.cmbGender)
End Sub
' ****************************************************************
' This Loads The Listbox - Part of the UserForm Code
' ****************************************************************
Private Sub LoadListBox(ProfileWorksheet As Worksheet, ColumnNumber As Long, BeginningRow As Long, EndingRow As Long, TargetListBox As MsForms.ListBox)
Dim i As Long
Me.lstCountry.Clear
For i = BeginningRow To EndingRow
TargetListBox.AddItem (ProfileWorksheet.Cells(i, ColumnNumber))
Next i
End Sub
' ****************************************************************
' This Loads The Combobox - Part of the UserForm Code
' ****************************************************************
Private Sub LoadComboBox(ProfileWorksheet As Worksheet, ColumnNumber As Long, BeginningRow As Long, EndingRow As Long, TargetComboBox As MsForms.ComboBox)
Dim i As Long
TargetComboBox.Clear
For i = BeginningRow To EndingRow
TargetComboBox.AddItem (ProfileWorksheet.Cells(i, ColumnNumber))
Next i
End Sub
' ***********************************************************************
' Simplified Version Where The ONE Subroutine can Load Both A Listbox
' Or ComboBox
' ***********************************************************************
Private Sub UserForm_Initialize()
Call LoadComboOrListBox(wksCostingVariables, 1, 2, lngLastRowInCountry, Me.lstCountry)
Call LoadComboOrListBox(wksCostingVariables, 4, 2, lngLastRowInGender, Me.cmbGender)
Call LoadComboOrListBox(wksCostingVariables, 7, 2, lngLastRowInSizes, Me.cmbSize)
End Sub
Private Sub LoadComboOrListBox(ProfileWorksheet As Worksheet, ColumnNumber As Long, BeginningRow As Long, EndingRow As Long, TargetComboOrListBox As MsForms.Control)
' ***************************************************************************
' If You Set Up The Parameter in the Subroutine as a MSForms.Control
' You can Pass it Either A Forms Combobox or Listbox
' ***************************************************************************
Dim i As Long
If TypeName(TargetComboOrListBox) = "ListBox" Or TypeName(TargetComboOrListBox) = "ComboBox" Then
TargetComboOrListBox.Clear
For i = BeginningRow To EndingRow
TargetComboOrListBox.AddItem (ProfileWorksheet.Cells(i, ColumnNumber))
Next i
Else
MsgBox ("MsForms Type Must Be List or Combo Box")
End If
End Sub
Option Explicit
Private Sub UserForm_Initialize()
' *********************************************************
' Load A ListBox or ComboBox From A Table (List Object)
' Using RowSource
' *********************************************************
Dim oList As ListObject
Dim wkbThisWorkbook As Workbook
Dim wksSheet2 As Worksheet
Set wkbThisWorkbook = ThisWorkbook
Set wksSheet2 = wkbThisWorkbook.Sheets("Sheet2")
Set oList = wksSheet2.ListObjects.Item("TableRich")
Me.ListBox1.RowSource = oList.DataBodyRange.Address(external:=True)
End Sub
Option Explicit
Private Sub UserForm_Initialize()
' *********************************************************
' Load A ListBox or ComboBox From A Range Using RowSource
' *********************************************************
Dim wkbThisWorkbook As Workbook
Dim wksSheet2 As Worksheet
Dim wksRange As Range
Set wkbThisWorkbook = ThisWorkbook
Set wksSheet2 = wkbThisWorkbook.Sheets("Sheet2")
Set wksRange = wksSheet2.Range("D1:D5")
Me.ListBox1.Clear
Me.ListBox1.RowSource = wksRange.Worksheet.Name & "!" & wksRange.Address
End Sub