Code to find value based on dropdown selection

dtrussell · Nov 23, 2015

Hope someone can help...

I have a dropdown list of employee names to filter a matrix-style spreadsheet. On selection, all columns except the selected are hidden. I did this by linking each column to a specific value, which is my problem. So every time I add a new employee, I have to modify the code to match the name to a specific column. I need code that will search the columns in a range for the value from the dropdown, then hide all other columns. Here is a shot of my current code: (The cells I need to search for value begins in F9)

Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Range("$E$2").Value
Case Is = "ALL OPERATORS"
Columns("F:AW").EntireColumn.Hidden = False
Range("Table12LL").Select
On Error Resume Next
ActiveSheet.ShowAllData
On Error GoTo 0
Range("A1").Select
Case Is = "Al Castro"
Columns("F:F").EntireColumn.Hidden = False
Columns("G:AW").EntireColumn.Hidden = True
Case Is = "Austin Furrow"
Columns("G:G").EntireColumn.Hidden = False
Columns("F:F").EntireColumn.Hidden = True
Columns("H:AW").EntireColumn.Hidden = True
Case Is = "Bobby Dykman"

NoS · Nov 23, 2015

Use variables and you won't need to make adjustments to your code as certain things are added to the sheet.
Not having your file this is not tested much. I think your header with names is in row 9 starting at column F, the table part is just commented out.
Code something along the lines of this should give you some ideas

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim SrchName As String
    Dim FndName As Range
    Dim LastCol As Long
'limit monitoring to a single cell
If Target.Address <> "$E$2" Or Target.Count <> 1 Or Target.Value = "" Then Exit Sub
Application.ScreenUpdating = False
'the name of concern
SrchName = Range("E2").Value
With ActiveSheet
    .Columns.Hidden = False
    LastCol = .Cells(9, Columns.Count).End(xlToLeft).Column
    If UCase(SrchName) <> "ALL OPERATORS" Then
        Columns.Hidden = False
        Set FndName = .Range(Cells(9, 6), Cells(6, LastCol)).Find _
                    (What:=SrchName, LookIn:=xlValues, LookAt:=xlWhole, _
                    SearchOrder:=xlByColumns, MatchCase:=False)
            If Not FndName Is Nothing Then      'name was found
                'hide all columns
                .Range(Cells(9, 6), Cells(9, LastCol)).EntireColumn.Hidden = True
                'unhide req'd one
                FndName.EntireColumn.Hidden = False
            Else
                MsgBox SrchName & "  Not found"
            End If
    Else
        .Columns.Hidden = False
        'Range("Table12LL").Select
        'On Error Resume Next
        'ActiveSheet.ShowAllData
        'On Error GoTo 0
        .Range("A1").Select
    End If
End With
Application.ScreenUpdating = True
End Sub

dtrussell · Nov 23, 2015

RE: Variable Suggestion

Thanks for your help with this. I'm working on getting help to modify and test it.

Code to find value based on dropdown selection

dtrussell

New member

NoS

Member

dtrussell

New member