Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
pause a macro
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
pause a macro
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" wrote: 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
pause a macro
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" wrote: 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" wrote: 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
pause a macro
hi
what version xl are you using? post the code your are using now. regards FSt1 "tsmith" wrote: 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" wrote: 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" wrote: 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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
pause a macro
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! "FSt1" wrote: hi what version xl are you using? post the code your are using now. regards FSt1 "tsmith" wrote: 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" wrote: 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" wrote: 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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
pause a macro
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. -- Gary''s Student - gsnu200828 "tsmith" wrote: 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! "FSt1" wrote: hi what version xl are you using? post the code your are using now. regards FSt1 "tsmith" wrote: 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" wrote: 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" wrote: 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. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
pause a macro
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" wrote: 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! "FSt1" wrote: hi what version xl are you using? post the code your are using now. regards FSt1 "tsmith" wrote: 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" wrote: 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" wrote: 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. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
pause a macro
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! "Gary''s Student" wrote: 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. -- Gary''s Student - gsnu200828 "tsmith" wrote: 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! "FSt1" wrote: hi what version xl are you using? post the code your are using now. regards FSt1 "tsmith" wrote: 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" wrote: 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" wrote: 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. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
pause a macro
Thank you! That worked perfectly!
Always looking forward to learning something new in Excel! "FSt1" wrote: 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" wrote: 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! "FSt1" wrote: hi what version xl are you using? post the code your are using now. regards FSt1 "tsmith" wrote: 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" wrote: 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" wrote: 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. |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
pause a macro
glad to help
regards FSt1 "tsmith" wrote: Thank you! That worked perfectly! Always looking forward to learning something new in Excel! "FSt1" wrote: 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" wrote: 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! "FSt1" wrote: hi what version xl are you using? post the code your are using now. regards FSt1 "tsmith" wrote: 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" wrote: 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" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I pause a macro? | Excel Discussion (Misc queries) | |||
Pause Macro 2 | Excel Discussion (Misc queries) | |||
Pause Excel Macro | Excel Discussion (Misc queries) | |||
Forcing a macro to pause for filename | Excel Discussion (Misc queries) | |||
Macro to move to specified cells, pause for input at each? | Excel Discussion (Misc queries) |