Results 1 to 3 of 3

Thread: Code to find value based on dropdown selection

  1. #1

    Code to find value based on dropdown selection

    Register for a FREE account, and/
    or Log in to avoid these ads!

    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
    On Error Resume Next
    On Error GoTo 0
    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"

  2. #2
    Magician NoS's Avatar
    Join Date
    Jan 2013
    British Columbia
    Excel Version
    Excel 2010 64bit
    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
    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
                    MsgBox SrchName & "  Not found"
                End If
            .Columns.Hidden = False
            'On Error Resume Next
            'On Error GoTo 0
        End If
    End With
    Application.ScreenUpdating = True
    End Sub

  3. #3

    RE: Variable Suggestion

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

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts