Filter via message box

[gbell3587]

Hi

Ok, so im good with the message box but i cant seem to get this to work.

We use a spread to control inventory & each items are stored in bins within particular aisles. So what im trying to do is using the filter, sort the sheet with anything in Aisle A, or Aisle B etc etc.

See image below, i can do this on the filter itself - as you can see, A-A will show anything here but what i am trying to do is get it so that a message box appears and the user enters what Aisle it should be filtered by

By default, each Aisle starts with "A-" - so aisle A is A-A, Aisle B is A-B etc etc

How do i get this into the message box?

Thanks
Graham

Heres what i have for the message box

Sub search_sku()
aisle = InputBox("Enter Aisle" & vbCrLf & vbCrLf & _
"(leaving blank will show all results)", "Search by Aisle")
If aisle <> "" Then
    Selection.AutoFilter Field:=8, Criteria1:="A-" & aisle
    ActiveWindow.SmallScroll Down:=-3
    Else
    Selection.AutoFilter Field:=1
   End If
   Range("a1").Select
End Sub

I know its not right, but hopefully you see the idea?

 

[Momentman]

Does the code not work? The idea looks right to me, Although you kept referring to InputBox as MessageBox

 

[gbell3587]

Ha yeah, input box!

So the code does filter but it just filters everything out - it doesnt select anything or filter by 'A-A' as expected like you could do manually.

I have other filters on the sheet that work with the input box and is basically the same code but only when you put the complete/exact filter i.e if i put A-10, then the sheet would filter by A-A-10. So i guess im looking for the way to filter based on an approx match rather than exact.

 

[gbell3587]

Ok, so i figured it out (and it wasnt very hard...couldnt for the life of me see it yesterday!)

Sub Button1024_Click()aisle = InputBox("Enter Aisle" & vbCrLf & vbCrLf & _
"(leaving blank will show all results)", "Search by Aisle")
If aisle <> "" Then
    Selection.AutoFilter Field:=8, Criteria1:="=A-" & aisle & "*"
    ActiveWindow.SmallScroll Down:=-3
    Else
    Selection.AutoFilter Field:=1
   End If
   Range("a1").Select
End Sub