Macro to display available selections for a filter

[ghp2017]

Hello,

I am trying to create a spreadsheet for people who are not that familiar with excel, if at all. I am trying to create a macro that would give them a list of available selections in the filter to choose from and then action their selection.

Anyone have any suggestions? THANKS in advance!!

I have the following code to search the field but it's not helpful if they don't know what is in the column to choose from.
-----------------------------------------------------------------------------------------------------
Sub VAN_INVENTORY_GROUP_sEARCH()


Dim strName As String

strName = InputBox("What word would you like to search for?")

Selection.AutoFilter Field:=4, Criteria1:="=*" & strName & "*", Operator:=xlAnd
-----------------------------------------------------------------------------------------------------
The valid selctions are:
Air Conditioning
Burner
Commercial
Controls
Electrical
Gas
LPG
Major Equipment
Miscellaneous
Mobile Home Furnace
Motors
Plumbing
Pool Heater
Propane
Security
SERV
Sundries
Tools
Venting
Warm Air Accessories
Water

 

[bugmonsta]

Using this code by Trebor76 to create the choice of unique data in a column, I added in your input list idea with some vb carriage returns to display the inputbox and list nicer.

Depending on how big your file is could make your input box massive....

This is using column C in my test

Sub Macro1()

    Dim lngLastRow As Long
    Dim rngCell As Range, _
        rngMyData As Range
    Dim clnMyList As New Collection
    Dim varMyList As Variant
    Dim strMyList As String
        
    'Assumes the dataset is from A2 to A[whatever the last row in Column A is].
    'Change to suit
    lngLastRow = Cells(Rows.Count, "c").End(xlUp).Row
    Set rngMyData = Range("c2:c" & lngLastRow)
    
    Application.ScreenUpdating = False
    
    On Error Resume Next 'Need to ignore errors as a Collection can only contain unique values
        For Each rngCell In rngMyData
            clnMyList.Add Item:=rngCell.Value, Key:=CStr(rngCell.Value)
        Next rngCell
    On Error GoTo 0 'Nullify error handler
    
    For Each varMyList In clnMyList
        If strMyList = "" Then
            strMyList = varMyList
        Else
            strMyList = strMyList & vbNewLine & varMyList
        End If
    Next varMyList
    
strname = InputBox("What word would you like to search for?" & vbCr & vbCr & "Your choices are:" & _
vbCr & vbCr & strMyList & vbCr)
    
    Application.ScreenUpdating = True

        ActiveSheet.Range("C:C").AutoFilter Field:=1, Criteria1:=strname
End Sub

 

[ghp2017]

Using this code by Trebor76 to create the choice of unique data in a column, I added in your input list idea with some vb carriage returns to display the inputbox and list nicer.

Depending on how big your file is could make your input box massive....

This is using column C in my test

Sub Macro1()

    Dim lngLastRow As Long
    Dim rngCell As Range, _
        rngMyData As Range
    Dim clnMyList As New Collection
    Dim varMyList As Variant
    Dim strMyList As String
        
    'Assumes the dataset is from A2 to A[whatever the last row in Column A is].
    'Change to suit
    lngLastRow = Cells(Rows.Count, "c").End(xlUp).Row
    Set rngMyData = Range("c2:c" & lngLastRow)
    
    Application.ScreenUpdating = False
    
    On Error Resume Next 'Need to ignore errors as a Collection can only contain unique values
        For Each rngCell In rngMyData
            clnMyList.Add Item:=rngCell.Value, Key:=CStr(rngCell.Value)
        Next rngCell
    On Error GoTo 0 'Nullify error handler
    
    For Each varMyList In clnMyList
        If strMyList = "" Then
            strMyList = varMyList
        Else
            strMyList = strMyList & vbNewLine & varMyList
        End If
    Next varMyList
    
strname = InputBox("What word would you like to search for?" & vbCr & vbCr & "Your choices are:" & _
vbCr & vbCr & strMyList & vbCr)
    
    Application.ScreenUpdating = True

        ActiveSheet.Range("C:C").AutoFilter Field:=1, Criteria1:=strname
End Sub

This is perfect! THANK you so much!!