Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to find a value with multi-column, multi-record list | Excel Worksheet Functions | |||
Multi column lookup | Excel Discussion (Misc queries) | |||
match in multi-column and multi-row array | Excel Discussion (Misc queries) | |||
Multi Column Sum and conditions | Excel Worksheet Functions | |||
Extract values from a multi-select multi-column list-box | Excel Programming |