List Index filtering

[burl_rfc]

In the following example I'd like to do something a little different
with the list index and I'm not sure how to go about it.

In the Private Sub Userform_Initialize() routine I'd like to see only
the listing for specific records, let's say in column 2 I have listed
all the product types, for example, Glass, Metals and Crystals and I'd
like to see only the Glass catagory populate into the combo box in the
Private Sub CommandButton1_Click() routine, how can this be done.


Private Sub CommandButton1_Click()

Dim myVar As Variant 'String/Long/double???
Dim myVar1 As String

With Me.ComboBox1
If .ListIndex > -1 Then
myVar = .List(.ListIndex, 1) '<-- second column!
MsgBox myVar 'for testing only
myVar1 = .List(.ListIndex, 0)
Select Case myVar
Case Is = "Glass"
frmGlassQuoteForm.Show
End Select
End If
End With
End Sub

Private Sub Userform_Initialize()
Dim SourceWB As Workbook
Dim myRng As Range

With Me.ComboBox1
.ColumnCount = 2
.ColumnWidths = "12;0" 'hide the second column
.Clear
Set SourceWB = Workbooks.Open("C:\TEMP\Quote Models\Quote
Log.xls", False, True)
With SourceWB.Worksheets(1)
Set myRng = .Range("A3R" & .Cells(.Rows.Count,
"A").End(xlUp).Row)
End With
.List = myRng.Value
SourceWB.Close False
End With
End Sub

Thanks
burl_rfc

 

[Dave Peterson]

I didn't set up an external workbook, but this worked ok for me:

Option Explicit

Private Sub Userform_Initialize()
Dim SourceWB As Workbook
Dim myRng As Range
Dim myCell As Range

With Me.ComboBox1
.ColumnCount = 2
.ColumnWidths = "12;0" 'hide the second column
.Clear
'Set SourceWB _
' = Workbooks.Open("C:\TEMP\Quote Models\Quote Log.xls ", False, True)

'I used thisworkbook in my test
Set SourceWB = ThisWorkbook
With SourceWB.Worksheets(1)
Set myRng = .Range("A3:b" & .Cells(.Rows.Count, "A").End(xlUp).Row)
End With

For Each myCell In myRng.Columns(1).Cells
If LCase(myCell.Offset(0, 1).Value) = "glass" Then
.AddItem myCell.Value
.List(.ListCount - 1, 1) = myCell.Offset(0, 1).Value
End If
Next myCell

'I commented the next line
'SourceWB.Close False
End With
End Sub

But I'm not sure I got the correct combobox populated????

But you can loop through the cells, check the values and add them to the
combobox list.