I am working on a spreadsheet and added a button w/ VBA to filter by status of line items in rows. There are 3 different status types available. I have it that the user selects the button and gets a input box to enter one of the statuses (with * = ALL status). The status entered in the input box is inserted in a field to show which filter is selected. My question is (maybe this is a 2-parter): If the user select * (for all), how can I get the text "ALL" inserted into the field? Or, instead of a input box, is there VBA could for a pull down/ option selection of all 3 status options plus "ALL"? Code used is below. Thank you! Kathleen
Private Sub CommandButton1_Click()
ActiveSheet.Unprotect Password:="1234"
Dim strInput As String
strInput = InputBox("Please enter the status to Filter - enter * for ALL rows")
Selection.AutoFilter
ActiveSheet.Range("A7:S1006").AutoFilter Field:=13, Criteria1:=strInput
Range("F3").Value = strInput
ActiveSheet.Protect Password:="1234"
End Sub
Use the Replace function
strInput = Replace(InputBox("Please enter the status to Filter - enter * for ALL rows"), "*", "ALL")