pause a macro

[tsmith]

I know you can pause a macro to enter data into a cell but I need to pause a
macro to change a filter selection. Is there a way to do this?

Thanks in advance for any suggestions.

 

[FSt1]

hi
the info you supplied is vague so about all i can give is a vague answer.
yes. use an input box.
see vb help on input boxes.

regards
FSt1

 

[tsmith]

Yeah I guess the question was a little vague, here is my issue

I have autofilter applied to one of my rows, my macro is set up to pick
"000-002" from one of the filters then print the results. After that I need
it to pick "000-055" then go to another filter and pause so I can select the
month (1-12) before printing those results.

So basicaly I need a pause to be able to select an autofilter field.

Thanks

 

[FSt1]

hi
what version xl are you using?
post the code your are using now.

regards
FSt1

 

[tsmith]

2003

this macro is huge since i print to 18 diff printers so i shortened it for
example purposes

Application.Run "'PM DUE REPORT.xls'!refresh"
Selection.AutoFilter Field:=7, Criteria1:=">=95%", Operator:=xlAnd
Selection.AutoFilter Field:=9, Criteria1:="000-002"
Selection.AutoFilter Field:=3, Criteria1:="ATL"
Application.ActivePrinter = "02Atlanta LaserJet 2100 on Ne18:"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"02Atlanta LaserJet 2100 on Ne17:", Collate:=True
Selection.AutoFilter Field:=9, Criteria1:="000-055"
*here is where i need to pause to manually select the month
Selection.AutoFilter Field:=3, Criteria1:="=ATL", Operator:=xlOr, _
Criteria2:="=OTR"
Application.ActivePrinter = "02Atlanta LaserJet 2100 on Ne18:"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"02Atlanta LaserJet 2100 on Ne17:", Collate:=True

Thanks!

 

[Gary''s Student]

Here is one way to pause a macro to allow user interaction:

Public proceed As Variant

Sub main()
proceed = False
MsgBox ("please make adjustments and press the button")
While proceed = False
DoEvents
Wend
MsgBox ("thank you")
End Sub

Sub handler()
proceed = True
End Sub


1. put a button on the worksheet and assign sub handler to it.
2. when main runs, it outputs the message and then patiently waits until the
variable proceed becomes true
3. the user makes any required changes and then pushes the button
4. the handler sub runs and sets proceed to True
5. the main sub detects proceed becoming True and continues execution

This is a case of VBA sharing focus with the user.

 

[FSt1]

hi
sorry it took so long to get back. got hung up.
looks like you are doing this more than once which might suggest a loop but
with out seeing our code, i would be afraid to suggest that soo....
here is the line you single out...
*here is where i need to pause to manually select the month
Selection.AutoFilter Field:=3, Criteria1:="=ATL", Operator:=xlOr, _
Criteria2:="=OTR"
it looks like you need 2 criterias so
dim c as string
dim oc as string
c=Inputbox("enter the criteria")
oc = Inputbox("enter the other cirteria")

Selection.AutoFilter Field:=3, Criteria1:=c, Operator:=xlOr, _
Criteria2:=oc

this is how an inputbox work. you create variables, assign values to the
variables then plug the variable into the code as i did above for criteria.
the macro will pause, display the input box and wait for criteria to be
entered.

regards
FSt1

 

[tsmith]

This seems like it should work but when I get to the msg box if I click ok so
I can make the selection it doen't continue
if I don't click ok on the msg box then I don't have access to make the
selection that I need. I set up a test sheet, here is what i have...(forgive
me I don't have alot of experience with macros)

Sub testingpause()
Rows("4:4").Select
Selection.AutoFilter
Selection.AutoFilter
Selection.AutoFilter Field:=2, Criteria1:="A17720"
Application.Run "'test pause.xls'!msgbx"
End Sub

Sub msgbx()
proceed = False
msgbox ("please select term") *here is where it ends
While proceed = False
DoEvents
Wend
msgbox ("thank you")
End Sub

Sub handler()
proceed = True
End Sub

Thank you for your help!

 

[tsmith]

Thank you! That worked perfectly!

Always looking forward to learning something new in Excel!

 

[FSt1]

glad to help
regards
FSt1

Thank you! That worked perfectly!

Always looking forward to learning something new in Excel!