Multi column combo box
 

Hello...
Two questions:
1. What is the best way to dynamically set the range of the column
(pVarRange).

2. The data source column on sheet5 has a lot of leading spaces. I
want to use that column as the bound column and pass that value to the
"FindString" procedure that searches all of the other worksheets. In
addition, I want to trim that same column and display that value in
the second column of the combo box for presentation purposes
"trim(pVarArray(i,1))". I've set the
ComboBox1 properties to include:
Bound Column = 1
Column Count = 2
Column Widths = 0pt;250pt

I'm struggling with filling the second column. I've tried a couple of
variations using the list property, but cannot get it to work. Any
suggestions?

TIA
Zachary

Sub LoadCombo()
Dim pVarRange As Range
Dim pVarArray As Variant

With Sheet1
'// Set the combox fill range to column A
Set pVarRange = Sheet5.Range("A8:A200")
pVarArray = pVarRange.Value
.ComboBox1.Clear
For i = LBound(pVarArray) To UBound(pVarArray)
If IsEmpty(pVarArray(i, 1)) = False Then
.ComboBox1.AddItem pVarArray(i, 1)
'Fill second column here
End If
Next
.ComboBox1.ListIndex = 0
End With
Sheet1.Select

End Sub

Multi column combo box
 

Sub LoadCombo()
Dim pVarRange As Range
Dim pVarArray As Variant

With Sheet1
'// Set the combox fill range to column A
With Sheet5.
Set pVarRange = .Range(.Range("A8), _
.Cells(rows.count,1).End(xlup))
End With
pVarArray = pVarRange.Value
.ComboBox1.Clear
For i = LBound(pVarArray) To UBound(pVarArray)
If IsEmpty(pVarArray(i, 1)) = False Then
.ComboBox1.AddItem pVarArray(i, 1)
.ComboBox1.List(Combobox.Listcount-1,1) = "abcd"
'Fill second column here
End If
Next
.ComboBox1.ListIndex = 0
End With
Sheet1.Select

End Sub

--
Regards,
Tom Ogilvy


Zachary Bass wrote in message
om...
Hello...
Two questions:
1. What is the best way to dynamically set the range of the column
(pVarRange).

2. The data source column on sheet5 has a lot of leading spaces. I
want to use that column as the bound column and pass that value to the
"FindString" procedure that searches all of the other worksheets. In
addition, I want to trim that same column and display that value in
the second column of the combo box for presentation purposes
"trim(pVarArray(i,1))". I've set the
ComboBox1 properties to include:
Bound Column = 1
Column Count = 2
Column Widths = 0pt;250pt

I'm struggling with filling the second column. I've tried a couple of
variations using the list property, but cannot get it to work. Any
suggestions?

TIA
Zachary

Sub LoadCombo()
Dim pVarRange As Range
Dim pVarArray As Variant

With Sheet1
'// Set the combox fill range to column A
Set pVarRange = Sheet5.Range("A8:A200")
pVarArray = pVarRange.Value
.ComboBox1.Clear
For i = LBound(pVarArray) To UBound(pVarArray)
If IsEmpty(pVarArray(i, 1)) = False Then
.ComboBox1.AddItem pVarArray(i, 1)
'Fill second column here
End If
Next
.ComboBox1.ListIndex = 0
End With
Sheet1.Select

End Sub