Hide/Unhide Rows based on Combo Box (form control)

**JimExcel** · 01-31-2013, 05:38 PM

I'm trying to set up an excel worksheet that will hide/unhide rows based on a selection in a Combo Box (form control).

Combo Box: I have this pulling in the names I want in the dropdown list based on another hidden tab (sheet2). However, it is linked to cell B4 on the tab I want showing.

I want it so that when you select someone's name from the list, it will hide all other lines that don't have the person's name in column I. I know autofilter would solve this very easily, but I need the combobox.

Any suggestions? Thanks!

**JimExcel** · 01-31-2013, 07:17 PM

Here is an example of what I'm talking about:

Book6.xlsm

I'd like it so that you could sort by, say Susan Black, and only the rows with her as the leader would appear. The rest would just be hidden.

Thanks!

**stnkynts** · 01-31-2013, 07:40 PM

Change from a Form control to an ActiveX control. Same type that you used before (one of the insert options just like the form control). Label the new ActiveX control ComboBox1. Under the properties of ComboBox1 (right click on it while Design Mode is enabled to get to properties) you will see a ListFillRange. Put this there:

Sheet2!A1:A4

Right clicking on the ActiveX control will also give you a "View Code" option. Do that and put this code there. Disable Design Mode and change the name to see it work:

Private Sub ComboBox1_Change()
Dim ws As Worksheet:    Set ws = Sheets("Data")
Dim lastrow As Long
Dim icell As Range

Application.ScreenUpdating = False

ws.Range("E7:E65000").EntireRow.Hidden = False
lastrow = ws.Range("E" & Rows.Count).End(xlUp).Row

For Each icell In ws.Range("E7:E" & lastrow)
    If icell.Value = ComboBox1.Value Then
        'do nothing
    Else
        icell.EntireRow.Hidden = True
    End If
    
Next icell
    
Application.ScreenUpdating = True

End Sub