How to disable macro prompts for OK in Excel?

[Guest]

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

 

[Peo Sjoblom]

Sub MyMacro()
Application.DisplayAlerts = False
'your code here
Application.DisplayAlerts = False
End Sub


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com

 

[Peo Sjoblom]

Oops! Last but second line should be

Application.DisplayAlerts = True


Peo

 

[Guest]

Peo,

I tried your suggestion but it didn't help, I still get the dialogue box to
press "OK". Any other ideas?

 

[Peo Sjoblom]

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

 

[Guest]

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 Su
-----------------------------------------------------------------------------------------------

 

[Dave Peterson]

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.

 

[Guest]

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]

If you clear out that range (and a bit more), does that help? (Untested,
though)