Excel

Populate Multi-Column ComboBox with Data

Ease of Use

Intermediate

Version tested with

2000 

Submitted by:

johnske

Description:

A combo box is shown with ten rows and three columns of data 

Discussion:

Present an abbreviated selection from a many-columned worksheet from which to take further action. 

Code:

instructions for use

			

Option Explicit Private Sub UserForm_activate() Dim MyList(10, 10) 'as array type '//The combo box in this example contains 3 data columns - make as many as you like With ComboBox1 .ColumnCount = 3 .ColumnWidths = 75 .Width = 220 .Height = 15 .ListRows = 6 End With '//Define the list and where it's obtained from (Columns A, D, G in this example) With ActiveSheet '//MyList (Row{0 to 9}, Column{first}) = (Column A in this example) '//remember that counting for Row and '//Column starts from zero (NOT one) MyList(0, 0) = .Range("A1") MyList(1, 0) = .Range("A2") MyList(2, 0) = .Range("A3") MyList(3, 0) = .Range("A4") MyList(4, 0) = .Range("A5") MyList(5, 0) = .Range("A6") MyList(6, 0) = .Range("A7") MyList(7, 0) = .Range("A8") MyList(8, 0) = .Range("A9") MyList(9, 0) = .Range("A10") '//MyList (Row{0 to 9}, Column{second}) = (Column D in this example) MyList(0, 1) = .Range("D1") MyList(1, 1) = .Range("D2") MyList(2, 1) = .Range("D3") MyList(3, 1) = .Range("D4") MyList(4, 1) = .Range("D5") MyList(5, 1) = .Range("D6") MyList(6, 1) = .Range("D7") MyList(7, 1) = .Range("D8") MyList(8, 1) = .Range("D9") MyList(9, 1) = .Range("D10") '//MyList (Row{0 to 9}, Column{third}) = (Column G in this example) MyList(0, 2) = .Range("G1") MyList(1, 2) = .Range("G2") MyList(2, 2) = .Range("G3") MyList(3, 2) = .Range("G4") MyList(4, 2) = .Range("G5") MyList(5, 2) = .Range("G6") MyList(6, 2) = .Range("G7") MyList(7, 2) = .Range("G8") MyList(8, 2) = .Range("G9") MyList(9, 2) = .Range("G10") End With '//now populate the combo box ComboBox1.List() = MyList End Sub

How to use:

  1. Open an Excel workbook
  2. Select Tools/Macro/Visual Basic Editor
  3. In the VBE window, select Insert/UserForm
  4. Select ComboBox from the toolbox, paste a ComboBox on the UserForm
  5. Right-Click UserForm1 in the Project - VBA Project pane
  6. Select ShowCode
  7. Select everything, then copy and paste the code into the code window
  8. Select the UserForm then "Run" (up the top) and click Run Sub/UserForm
 

Test the code:

  1. Type some entries in columns A, D, and G on a spreadsheet
  2. Show the userform again, you should now see three columns of data
 

Sample File:

multi col combo box.zip 13.8KB 

Approved by mdmackillop


This entry has been viewed 390 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2014 VBA Express