Question about input boxes

[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?

 

[ryguy7272]

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---

 

[JLGWhiz]

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

 

[Rob]

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?

 

[Mike Fogleman]

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