Need help pausing a macro for user input

sneel3

Active Member
Joined
Oct 9, 2002
Messages
331
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
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Pyromantic

Board Regular
Joined
Aug 9, 2006
Messages
58
The easiest way to do this would be via the input box. Try this:

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

Active Member
Joined
Oct 9, 2002
Messages
331
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

Board Regular
Joined
Aug 9, 2006
Messages
58
If you pop this into your code :

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
 

Watch MrExcel Video

Forum statistics

Threads
1,108,956
Messages
5,525,883
Members
409,669
Latest member
JDCupps

This Week's Hot Topics

Top