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
    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"

  2. #2
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    751
    Articles
    0
    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
    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

  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
  •