Need help pausing a macro for user input

[sneel3]

I have the following code to use an auto filter; however, I want the user to enter the criteria into the "custom filter" under 'equal to'. The user would input a contract number ["=CON025070" in the code below].

Sub ContractFilter()
'
' ContractFilter Macro
' Macro recorded 10/4/2006 by scott neel
'

'
Selection.AutoFilter Field:=5, Criteria1:="=CON025070", Operator:=xlAnd
Rows("10:10").Select
Range(Selection, Selection.End(xlDown)).Select
Sheets.Add
Sheets("Data").Select

 

[Pyromantic]

The easiest way to do this would be via the input box. Try this:

Sub ContractFilter() 
Dim ContractNo as String

ContractNo = InputBox("Please enter Contract Number")

Selection.AutoFilter Field:=5, Criteria1:=ContractNo, Operator:=xlAnd 
Rows("10:10").Select 
Range(Selection, Selection.End(xlDown)).Select 
Sheets.Add 
Sheets("Data").Select

 

[sneel3]

That works perfect. You da man!

Later in the macro (a part not included in the above) I delete the sheet that I created. How do I make the macro press the delete button in the message box that pops up telling me that I may be deleting data?

 

[Pyromantic]

If you pop this into your code :

Sub Macro1()

Application.DisplayAlerts = False
.....
....your code here...
......
Application.DisplayAlerts = True
End Sub

However, beware if your macro deletes something your do not want to delete, it will not prompt you. It will simply delete it so use it with care.

Cheers

 

[sneel3]

Awesome!