Filter via message box

gbell3587

Board Regular
Joined
Jan 30, 2011
Messages
117
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


filter.jpg


Heres what i have for the message box

Code:
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?
 
Last edited:

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Momentman

Well-known Member
Joined
Jan 11, 2012
Messages
4,142
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Does the code not work? The idea looks right to me, Although you kept referring to InputBox as MessageBox :)
 
Upvote 0

gbell3587

Board Regular
Joined
Jan 30, 2011
Messages
117
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.
 
Upvote 0

gbell3587

Board Regular
Joined
Jan 30, 2011
Messages
117
Ok, so i figured it out (and it wasnt very hard...couldnt for the life of me see it yesterday!)

Code:
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
 
Upvote 0

Forum statistics

Threads
1,190,558
Messages
5,981,690
Members
439,730
Latest member
gjvv

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top