Question about input boxes

  • Thread starter Thread starter Rob
  • Start date Start date
R

Rob

I know almost nothing about VBA but I pieced together a macro by copying and
pasting. It currently has a line which applies an autofilter to sort out any
values less than 3:
Selection.AutoFilter Field:=4, Criteria1:=">=3", Operator:=xlAnd

I would like to change this from always having a criteria of 3 to allowing
the user to input into an input box any number to filter for. Can someone
please provide an example of how to do this?
 
Something like this should work for you:
Sub Filter_Stuff()
With Sheets("Sheet1")
.Range("A1:A20").AutoFilter Field:=1, Criteria1:=.Range("E1").Value
End With
End Sub

Sub Unfilter()
Cells.Select
Selection.AutoFilter
End Sub

Put your filter criteria in Cell E1. Try it and see how you get along.

HTH,
Ryan---
 
This allows a user to enter a number, but they cannot designate the
operators of < or >.


Dim myNum
myNum = Application. _
InputBox("Enter a number to filter", "Number", Type:=1)
Selection.AutoFilter Field:=4, Criteria1:=myNum, Operator:=xlAnd


This will allow the user to enter a formula using the operators <, >, and =.

Dim myNum
myNum = Application. _
InputBox("Enter a number to filter", "Number", Type:=0)
Selection.AutoFilter Field:=4, Criteria1:=myNum, Operator:=xlAnd
 
Thank you for the answer. I didn't mention that the worksheet which contains
the macro isn't the worksheet which is being filtered. The first line of
code switches to the other spreadsheet with ActiveWindow.ActivateNext. How
can I take the criteria from cell E1 in the worksheet which contains the
macro, and apply it to the other worksheet?
 
From Help:
You can use the sum of the allowable values for Type. For example, for an
input box that can accept both text and numbers, set Type to 1 + 2.

Type 0 + type 1 = 1. So should type:=1 accept both formula and number?



Mike F
 
Back
Top