Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
List Index filtering
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("A3:DR" & .Cells(.Rows.Count, "A").End(xlUp).Row) End With .List = myRng.Value SourceWB.Close False End With End Sub Thanks burl_rfc |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
List Index filtering
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. burl_rfc wrote: 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("A3:DR" & .Cells(.Rows.Count, "A").End(xlUp).Row) End With .List = myRng.Value SourceWB.Close False End With End Sub Thanks burl_rfc -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
data filtering via vlookup or index/match/find or search withwildcard | Excel Worksheet Functions | |||
How- seperate a combined list by filtering out first list of names | Excel Worksheet Functions | |||
Auto filtering vs Date List Create List | Excel Worksheet Functions | |||
trouble filtering a list. Why isn't column filtering? | Excel Worksheet Functions | |||
need help with filtering a list | Excel Programming |