Logicwurks Home Page

Links To Excel Code Examples

Tracing VBA Statements
Range/Wkb/Wks Variables
Add Grand Totals Using Ranges
Using Range Offset Property
Using Range Find Method
ConvertCellAddressToRange
Set Conditional Formatting
Union Of Ranges
Parse Range Strings
Delete Duplicate Rows
Delete Rows And Columns
Worksheet Variables
TypeName And TypeOf
Loop Through Worksheets
Loop Through Open Workbooks
Form Button Magic
Command Button Magic
Add Worksheets Dynamically
ImportExternalWorksheets
Find Last Row Or Column
Copy And Paste Special
Copy To Specific Cell Types
Range Copy With Filter
ExcelFileOpenSaveClose
ExcelFileOpenSaveCSV
Open An Excel File
Open An Excel File w/Params
Open An Excel File On Web
Save A Workbook
Save A Workbook Using mso
Clone A Workbook
Test If WEB URL Exists
Parse Using Split Command
Using Classes in Excel
TypeStatementStructures
Color Management
Convert Cell Color To RGB
Sort Methods 2003 - 2010
Sort Alpha/Numeric In ASCII
Search Using Match Function
Search Using Vlookup Function
Search Using Xlookup Function
Using Find Instead of Vlookup
Remove String Non-Printables
Auto_Open And Auto_Close
Initialize Form At Open
Edit Numerics In UserForm
Load Combo And List Boxes
Floating Sheet Combo Boxes
Advanced User Form Coding
Excel Events
Worksheet Change Events
Binary Search Of Array
Typecast Constants
Excel Error Handling
Handling Optional Parameters
Data Validation Drop Downs
Insert Data Validation Sub
Read A Text File w/Handle
Write A Text File w/Handle
Read a Binary File w/Handle
Update a Binary File w/Handle
Binary File Copy and Update
Read A Text Fiile w/Script
Text File Processing Examples
Test For Exists Or Open
Splash Screen
Dynamically Load Formulas
PaymentStreamsByDate
Date Examples
Date Find Same Days
Convert Month To Number
Initialize Arrays
Load Arrays Using Evaluate
ChartsAndGraphsVBA
Redim An Array
Reassign Button Action
Timer Functions
Legacy Calendar Control
Excel 2010 Date Picker
Date Picker Alternative
Generate Multiple Worksheets
Read Access Data Into Excel
Send Outlook Email w/Attach
Copy AutoFilters To Sheets
Export A Text File
Get Windows User Name
VBA Format Statement
Manipulate Files via VBA
Dynamically Load Images
Loop Through Worksheet Objects
Loop Through Form Objects
Loop Through Files with DIR
Active-X Checkboxes
Add Forms Checkboxes Dynam
Paste Pictures Into Excel
Copy Pictures Sheet To Sheet
Copy Pictures Sheet To Sheet
Create Forms Buttons With VBA
Extract Filename From Path
Convert R1C1 Format to A1
Special Cells Property
Insert Cell Comments

Links To Access Code Examples

DAO Versus ADODB
SearchVBACodeStrings
Interface Excel With Access
Create Form Manually
Create Recordset With AddNew
Multi-Select List Boxes
Update Field(s) In A Recordset
Update Excel Pivot From Access
Import A Tab Delimited File
Export Excel FileDialog
Create Excel Within Access
Open Excel Within Access
Open Excel OBJ From Access
Format Excel From Access
Control Excel via Access VBA
On Error Best Practices
Import Tab Delim w/WinAPI
Initialize Global Variables
Using TempVars For Globals
Access Error Handling
Loop Through Form Controls
Insert A Calendar Control
Create A Filtered Recordset
Populate Combo Boxes
Bookmarks And Forms
Combo Box Multiple Sources
Passing Form Objects
Create VBA SQL Statements
Create Dynamic Queries
Display File Images On A Form
Manipulate Files via VBA
Manipulate Files via Scripting
Number Subform Records
Reference Subform Objects
Parse Delimited Fields
Parameterized Queries (VBA)
Manipulating QueryDefs In VBA
FindFirst On Combined Keys
Dlookup Command
Dlookup In Form Datasheet
Execute SQL Delete Records
Commit Form To Table
Report With No Data
Reference Form Objects
DSNLess Connections To MySQL
Print Active Form Record
Count Records in Linked Tables
Delete Empty Tables
Open Linked SQL Tables

 

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