|
How to disable macro prompts for OK in Excel?
I've written a macro that uses the histogram tool. When using the macro, it
generates a dialogue box for "OK" to overwrite existing data which makes me hit enter on the keyboard. I just want it to continue without prompting when running the macro. I can't seem to find the magic to disable this. Any thoughts from anyone. Help much appreciated. Thanks. Tom |
How to disable macro prompts for OK in Excel?
Sub MyMacro()
Application.DisplayAlerts = False 'your code here Application.DisplayAlerts = False End Sub -- Regards, Peo Sjoblom http://nwexcelsolutions.com "Austin" wrote in message ... I've written a macro that uses the histogram tool. When using the macro, it generates a dialogue box for "OK" to overwrite existing data which makes me hit enter on the keyboard. I just want it to continue without prompting when running the macro. I can't seem to find the magic to disable this. Any thoughts from anyone. Help much appreciated. Thanks. Tom |
How to disable macro prompts for OK in Excel?
Oops! Last but second line should be
Application.DisplayAlerts = True Peo "Peo Sjoblom" wrote in message ... Sub MyMacro() Application.DisplayAlerts = False 'your code here Application.DisplayAlerts = False End Sub -- Regards, Peo Sjoblom http://nwexcelsolutions.com "Austin" wrote in message ... I've written a macro that uses the histogram tool. When using the macro, it generates a dialogue box for "OK" to overwrite existing data which makes me hit enter on the keyboard. I just want it to continue without prompting when running the macro. I can't seem to find the magic to disable this. Any thoughts from anyone. Help much appreciated. Thanks. Tom |
How to disable macro prompts for OK in Excel?
Peo,
I tried your suggestion but it didn't help, I still get the dialogue box to press "OK". Any other ideas? "Peo Sjoblom" wrote: Oops! Last but second line should be Application.DisplayAlerts = True Peo "Peo Sjoblom" wrote in message ... Sub MyMacro() Application.DisplayAlerts = False 'your code here Application.DisplayAlerts = False End Sub -- Regards, Peo Sjoblom http://nwexcelsolutions.com "Austin" wrote in message ... I've written a macro that uses the histogram tool. When using the macro, it generates a dialogue box for "OK" to overwrite existing data which makes me hit enter on the keyboard. I just want it to continue without prompting when running the macro. I can't seem to find the magic to disable this. Any thoughts from anyone. Help much appreciated. Thanks. Tom |
How to disable macro prompts for OK in Excel?
Post the code you have
-- Regards, Peo Sjoblom Excel 95 - Excel 2007 Northwest Excel Solutions www.nwexcelsolutions.com "It is a good thing to follow the first law of holes; if you are in one stop digging." Lord Healey "Austin" wrote in message ... Peo, I tried your suggestion but it didn't help, I still get the dialogue box to press "OK". Any other ideas? "Peo Sjoblom" wrote: Oops! Last but second line should be Application.DisplayAlerts = True Peo "Peo Sjoblom" wrote in message ... Sub MyMacro() Application.DisplayAlerts = False 'your code here Application.DisplayAlerts = False End Sub -- Regards, Peo Sjoblom http://nwexcelsolutions.com "Austin" wrote in message ... I've written a macro that uses the histogram tool. When using the macro, it generates a dialogue box for "OK" to overwrite existing data which makes me hit enter on the keyboard. I just want it to continue without prompting when running the macro. I can't seem to find the magic to disable this. Any thoughts from anyone. Help much appreciated. Thanks. Tom |
How to disable macro prompts for OK in Excel?
Here's the Macro code that is causing the popup OK prompt to overwrite
existing cells: ------------------------------------------------------------------------ Sub Sheets_Update() 'Histogram_Update Macro ' Application.DisplayAlerts = False Application.Run "ATPVBAEN.XLA!Histogram", ActiveSheet.Range("$T$23:$T$2055") _ , ActiveSheet.Range("$AB$8"), , False, False, False, False Application.Run "ATPVBAEN.XLA!Histogram", ActiveSheet.Range("$B$8:$B$2055"), _ ActiveSheet.Range("$H$8"), , False, False, False, False Application.DisplayAlerts = True End Sub ----------------------------------------------------------------------------------------------- "Peo Sjoblom" wrote: Post the code you have -- Regards, Peo Sjoblom Excel 95 - Excel 2007 Northwest Excel Solutions www.nwexcelsolutions.com "It is a good thing to follow the first law of holes; if you are in one stop digging." Lord Healey "Austin" wrote in message ... Peo, I tried your suggestion but it didn't help, I still get the dialogue box to press "OK". Any other ideas? "Peo Sjoblom" wrote: Oops! Last but second line should be Application.DisplayAlerts = True Peo "Peo Sjoblom" wrote in message ... Sub MyMacro() Application.DisplayAlerts = False 'your code here Application.DisplayAlerts = False End Sub -- Regards, Peo Sjoblom http://nwexcelsolutions.com "Austin" wrote in message ... I've written a macro that uses the histogram tool. When using the macro, it generates a dialogue box for "OK" to overwrite existing data which makes me hit enter on the keyboard. I just want it to continue without prompting when running the macro. I can't seem to find the magic to disable this. Any thoughts from anyone. Help much appreciated. Thanks. Tom |
How to disable macro prompts for OK in Excel?
Sometimes, there's code that turn the alerts back on--and you have no control
over that code. If you only get the popup once, then I'm guessing that the histogram turns alerts back on and the second call is causing the trouble. If you're getting that popup twice (one time for each call), then there must be something in the Histogram routine that turns it on -- even if you dislike it. I'd try this (Untested): application.displayalerts = false Application.Run "ATPVBAEN.XLA!Histogram", _ ActiveSheet.Range("$T$23:$T$2055"), _ ActiveSheet.Range("$AB$8"), , False, False, False, False application.displayalerts = false 'turn it off once more Application.Run "ATPVBAEN.XLA!Histogram", _ ActiveSheet.Range("$B$8:$B$2055"), _ ActiveSheet.Range("$H$8"), , False, False, False, False application.displayalerts = true 'probably unnecessary if Histogram does it! It may not help, but it can't hurt to try. Austin wrote: Here's the Macro code that is causing the popup OK prompt to overwrite existing cells: ------------------------------------------------------------------------ Sub Sheets_Update() 'Histogram_Update Macro ' Application.DisplayAlerts = False Application.Run "ATPVBAEN.XLA!Histogram", ActiveSheet.Range("$T$23:$T$2055") _ , ActiveSheet.Range("$AB$8"), , False, False, False, False Application.Run "ATPVBAEN.XLA!Histogram", ActiveSheet.Range("$B$8:$B$2055"), _ ActiveSheet.Range("$H$8"), , False, False, False, False Application.DisplayAlerts = True End Sub ----------------------------------------------------------------------------------------------- "Peo Sjoblom" wrote: Post the code you have -- Regards, Peo Sjoblom Excel 95 - Excel 2007 Northwest Excel Solutions www.nwexcelsolutions.com "It is a good thing to follow the first law of holes; if you are in one stop digging." Lord Healey "Austin" wrote in message ... Peo, I tried your suggestion but it didn't help, I still get the dialogue box to press "OK". Any other ideas? "Peo Sjoblom" wrote: Oops! Last but second line should be Application.DisplayAlerts = True Peo "Peo Sjoblom" wrote in message ... Sub MyMacro() Application.DisplayAlerts = False 'your code here Application.DisplayAlerts = False End Sub -- Regards, Peo Sjoblom http://nwexcelsolutions.com "Austin" wrote in message ... I've written a macro that uses the histogram tool. When using the macro, it generates a dialogue box for "OK" to overwrite existing data which makes me hit enter on the keyboard. I just want it to continue without prompting when running the macro. I can't seem to find the magic to disable this. Any thoughts from anyone. Help much appreciated. Thanks. Tom -- Dave Peterson |
How to disable macro prompts for OK in Excel?
Dave,
Thanks, it looks like the app gens the popup each time, regardless of the displayalerts setting. Seems like I'm stuck with this mode of operation. I did try to first clear the cells that are being written by the histogram app and that seems to prevent the popup message from happening. "Dave Peterson" wrote: Sometimes, there's code that turn the alerts back on--and you have no control over that code. If you only get the popup once, then I'm guessing that the histogram turns alerts back on and the second call is causing the trouble. If you're getting that popup twice (one time for each call), then there must be something in the Histogram routine that turns it on -- even if you dislike it. I'd try this (Untested): application.displayalerts = false Application.Run "ATPVBAEN.XLA!Histogram", _ ActiveSheet.Range("$T$23:$T$2055"), _ ActiveSheet.Range("$AB$8"), , False, False, False, False application.displayalerts = false 'turn it off once more Application.Run "ATPVBAEN.XLA!Histogram", _ ActiveSheet.Range("$B$8:$B$2055"), _ ActiveSheet.Range("$H$8"), , False, False, False, False application.displayalerts = true 'probably unnecessary if Histogram does it! It may not help, but it can't hurt to try. Austin wrote: Here's the Macro code that is causing the popup OK prompt to overwrite existing cells: ------------------------------------------------------------------------ Sub Sheets_Update() 'Histogram_Update Macro ' Application.DisplayAlerts = False Application.Run "ATPVBAEN.XLA!Histogram", ActiveSheet.Range("$T$23:$T$2055") _ , ActiveSheet.Range("$AB$8"), , False, False, False, False Application.Run "ATPVBAEN.XLA!Histogram", ActiveSheet.Range("$B$8:$B$2055"), _ ActiveSheet.Range("$H$8"), , False, False, False, False Application.DisplayAlerts = True End Sub ----------------------------------------------------------------------------------------------- "Peo Sjoblom" wrote: Post the code you have -- Regards, Peo Sjoblom Excel 95 - Excel 2007 Northwest Excel Solutions www.nwexcelsolutions.com "It is a good thing to follow the first law of holes; if you are in one stop digging." Lord Healey "Austin" wrote in message ... Peo, I tried your suggestion but it didn't help, I still get the dialogue box to press "OK". Any other ideas? "Peo Sjoblom" wrote: Oops! Last but second line should be Application.DisplayAlerts = True Peo "Peo Sjoblom" wrote in message ... Sub MyMacro() Application.DisplayAlerts = False 'your code here Application.DisplayAlerts = False End Sub -- Regards, Peo Sjoblom http://nwexcelsolutions.com "Austin" wrote in message ... I've written a macro that uses the histogram tool. When using the macro, it generates a dialogue box for "OK" to overwrite existing data which makes me hit enter on the keyboard. I just want it to continue without prompting when running the macro. I can't seem to find the magic to disable this. Any thoughts from anyone. Help much appreciated. Thanks. Tom -- Dave Peterson |
How to disable macro prompts for OK in Excel?
If you clear out that range (and a bit more), does that help? (Untested,
though) Austin wrote: Dave, Thanks, it looks like the app gens the popup each time, regardless of the displayalerts setting. Seems like I'm stuck with this mode of operation. I did try to first clear the cells that are being written by the histogram app and that seems to prevent the popup message from happening. "Dave Peterson" wrote: Sometimes, there's code that turn the alerts back on--and you have no control over that code. If you only get the popup once, then I'm guessing that the histogram turns alerts back on and the second call is causing the trouble. If you're getting that popup twice (one time for each call), then there must be something in the Histogram routine that turns it on -- even if you dislike it. I'd try this (Untested): application.displayalerts = false Application.Run "ATPVBAEN.XLA!Histogram", _ ActiveSheet.Range("$T$23:$T$2055"), _ ActiveSheet.Range("$AB$8"), , False, False, False, False application.displayalerts = false 'turn it off once more Application.Run "ATPVBAEN.XLA!Histogram", _ ActiveSheet.Range("$B$8:$B$2055"), _ ActiveSheet.Range("$H$8"), , False, False, False, False application.displayalerts = true 'probably unnecessary if Histogram does it! It may not help, but it can't hurt to try. Austin wrote: Here's the Macro code that is causing the popup OK prompt to overwrite existing cells: ------------------------------------------------------------------------ Sub Sheets_Update() 'Histogram_Update Macro ' Application.DisplayAlerts = False Application.Run "ATPVBAEN.XLA!Histogram", ActiveSheet.Range("$T$23:$T$2055") _ , ActiveSheet.Range("$AB$8"), , False, False, False, False Application.Run "ATPVBAEN.XLA!Histogram", ActiveSheet.Range("$B$8:$B$2055"), _ ActiveSheet.Range("$H$8"), , False, False, False, False Application.DisplayAlerts = True End Sub ----------------------------------------------------------------------------------------------- "Peo Sjoblom" wrote: Post the code you have -- Regards, Peo Sjoblom Excel 95 - Excel 2007 Northwest Excel Solutions www.nwexcelsolutions.com "It is a good thing to follow the first law of holes; if you are in one stop digging." Lord Healey "Austin" wrote in message ... Peo, I tried your suggestion but it didn't help, I still get the dialogue box to press "OK". Any other ideas? "Peo Sjoblom" wrote: Oops! Last but second line should be Application.DisplayAlerts = True Peo "Peo Sjoblom" wrote in message ... Sub MyMacro() Application.DisplayAlerts = False 'your code here Application.DisplayAlerts = False End Sub -- Regards, Peo Sjoblom http://nwexcelsolutions.com "Austin" wrote in message ... I've written a macro that uses the histogram tool. When using the macro, it generates a dialogue box for "OK" to overwrite existing data which makes me hit enter on the keyboard. I just want it to continue without prompting when running the macro. I can't seem to find the magic to disable this. Any thoughts from anyone. Help much appreciated. Thanks. Tom -- Dave Peterson -- Dave Peterson |
| All times are GMT +1. The time now is 07:38 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com