VBA ComboBox - More than one range

george hart

Board Regular
Joined
Dec 4, 2008
Messages
241
Hello

Is there a way in this bit of code where I can refer to more than one column? ListItems = SourceWB.Worksheets(1).Range("B2:B3000").Value
Change to Range("B2:B3000"). & Range("C2:C3000").??


im ListItems As Variant, i As Integer
Dim SourceWB As Workbook
With Me.ComboBox1
Set SourceWB = Workbooks.Open("C:\Documents and Settings\HartG\My Documents\Projects\Sickness\Ops Employee LookUp.xls", _
False, True)
ListItems = SourceWB.Worksheets(1).Range("B2:B3000").Value
' get the values you want
SourceWB.Close False ' close the source workbook without saving changes
Set SourceWB = Nothing
Application.ScreenUpdating = True
ListItems = Application.WorksheetFunction.Transpose(ListItems)
' convert values to a vertical array
For i = 1 To UBound(ListItems)
.AddItem ListItems(i) ' populate the listbox
Next i
ComboBox1.ListIndex = 1 ' no items selected, set to 0 to select the first item
End With
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Watch MrExcel Video

Forum statistics

Threads
1,109,041
Messages
5,526,417
Members
409,701
Latest member
nitmani

This Week's Hot Topics

Top