Show/Select Visible Rows via ComboBox

[notso]

I have a form where I use the combobox to select a row to edit.
The worksheet has a filter. Can I tell the combobox to only show the visible
rows? I don't want to loose the index in the combobox because I use the index
to update the cooresponding row.

Data Filter On
Visible Rows# Combo Box Value
1 Row1
2 Row2
4 Row4

Using the above example, if I select Row4 from the combo box, I want to
automatically update Row4, not Row3 --- How do I do that? I also want the
combo box values to show Row1, Row2, Row4 only since Row3 is hidden it should
not show up. If the user changes the data filter then the values displayed in
the combo box should automatically change.

Thank you!

 

[Greg Glynn]

Notso,

How are you populating your list?

Greg

 

[Dave Peterson]

You can have multiple columns in a combobox's list--in fact, you can have
multiple columns in that list and hide the columns you don't want to see.

I used the .boundcolumn property with the row column. Then I can use
me.combobox1.value as the row number.

Option Explicit
Dim Wks As Worksheet
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub CommandButton2_Click()
With Me.ComboBox1

'I can use the .list and pick out what I want
MsgBox .Value & vbLf _
& .List(.ListIndex, 0) & vbLf _
& .List(.ListIndex, 1)

'Or I can use .value, since the .boundcolumn is the row number
MsgBox Wks.Cells(.Value, "D").Value
End With
End Sub
Private Sub UserForm_Initialize()
Dim myRng As Range
Dim myVRng As Range
Dim myCell As Range

Set Wks = Worksheets("sheet1")

With Wks
Set myRng = .AutoFilter.Range
End With

If myRng.Columns(1).Cells.SpecialCells(xlCellTypeVisible) = 1 Then
MsgBox "no visible rows found!"
Me.ComboBox1.Enabled = False
Else
Set myVRng = myRng.Resize(myRng.Rows.Count - 1, 1) _
.Offset(1, 0).Cells.SpecialCells(xlCellTypeVisible)
With Me.ComboBox1
.ColumnCount = 2 'row number and column A
.BoundColumn = 1
.ColumnWidths = "0;-1"
For Each myCell In myVRng.Cells
.AddItem myCell.Row
.List(.ListCount - 1, 1) = myCell.Value
Next myCell
End With
End If

End Sub

 

[Dave Peterson]

I had a typo in my code.

Change this line:
If myRng.Columns(1).Cells.SpecialCells(xlCellTypeVisible) = 1 Then
to:
If myRng.Columns(1).Cells.SpecialCells(xlCellTypeVisible).Count = 1 Then

(I left out .count.)