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
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

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
 

Forum statistics

Threads
1,141,227
Messages
5,705,137
Members
421,380
Latest member
Nuwan Sanjeewa Aponso

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