I am running a macro across multiple tabs where I am removing subtotals.
Prior to the removal of the subtotals for each tab the "This removes the
subtotal and sorts again" notification pops-up and the "OK" button must be
clicked to proceed. How can I turn off this notification?

application.DisplayAlerts=False

"Hoppy" a écrit dans le message de news:
...
I am running a macro across multiple tabs where I am removing subtotals.
Prior to the removal of the subtotals for each tab the "This removes the
subtotal and sorts again" notification pops-up and the "OK" button must be
clicked to proceed. How can I turn off this notification?

Untested but you could try this...

Application.displayalerts = false
'Your code here
Application.displayalerts = true
--
HTH...

Jim Thomlinson


"Hoppy" wrote:

I am running a macro across multiple tabs where I am removing subtotals.
Prior to the removal of the subtotals for each tab the "This removes the
subtotal and sorts again" notification pops-up and the "OK" button must be
clicked to proceed. How can I turn off this notification?

This is an application level setting so you must be sure to reset it to True
when you are done. In fact it should really have an error handler on it to
ensure that it gets reset. Something like this (I neglected to add the error
handler in my post).

public sub Whatever()
on error goto ErrorHandler
application.displayalerts = false
'your code here
ErrorHandler:
application.displayalerts = true
end sub

--
HTH...

Jim Thomlinson


"Tiah" wrote:

application.DisplayAlerts=False

"Hoppy" a écrit dans le message de news:
...
I am running a macro across multiple tabs where I am removing subtotals.
Prior to the removal of the subtotals for each tab the "This removes the
subtotal and sorts again" notification pops-up and the "OK" button must be
clicked to proceed. How can I turn off this notification?

Thanks for the help!

"Jim Thomlinson" wrote:

This is an application level setting so you must be sure to reset it to True
when you are done. In fact it should really have an error handler on it to
ensure that it gets reset. Something like this (I neglected to add the error
handler in my post).

public sub Whatever()
on error goto ErrorHandler
application.displayalerts = false
'your code here
ErrorHandler:
application.displayalerts = true
end sub

--
HTH...

Jim Thomlinson


"Tiah" wrote:

application.DisplayAlerts=False

"Hoppy" a écrit dans le message de news:
...
I am running a macro across multiple tabs where I am removing subtotals.
Prior to the removal of the subtotals for each tab the "This removes the
subtotal and sorts again" notification pops-up and the "OK" button must be
clicked to proceed. How can I turn off this notification?

Precede with

Application.DisplayAlerts = False

and reset to True after.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Hoppy" wrote in message
...
I am running a macro across multiple tabs where I am removing subtotals.
Prior to the removal of the subtotals for each tab the "This removes the
subtotal and sorts again" notification pops-up and the "OK" button must be
clicked to proceed. How can I turn off this notification?

try
application.DisplayAlerts = False

'code that causes a message

application.DisplayAlerts = True

--
regards,
Tom Ogilvy

"Hoppy" wrote:

I am running a macro across multiple tabs where I am removing subtotals.
Prior to the removal of the subtotals for each tab the "This removes the
subtotal and sorts again" notification pops-up and the "OK" button must be
clicked to proceed. How can I turn off this notification?

From the help on displayalerts:

"If you set this property to False, Micorosoft Excel sets this property to
True when the code is finished, unless you are running cross process code."

so while there is nothing wrong with what you suggest, what you imply is
usually not the case.

--
Regards,
Tom Ogilvy






"Jim Thomlinson" wrote:

This is an application level setting so you must be sure to reset it to True
when you are done. In fact it should really have an error handler on it to
ensure that it gets reset. Something like this (I neglected to add the error
handler in my post).

public sub Whatever()
on error goto ErrorHandler
application.displayalerts = false
'your code here
ErrorHandler:
application.displayalerts = true
end sub

--
HTH...

Jim Thomlinson


"Tiah" wrote:

application.DisplayAlerts=False

"Hoppy" a écrit dans le message de news:
...
I am running a macro across multiple tabs where I am removing subtotals.
Prior to the removal of the subtotals for each tab the "This removes the
subtotal and sorts again" notification pops-up and the "OK" button must be
clicked to proceed. How can I turn off this notification?

Absolutely true and thanks for clearing that up. That and being a little bit
lazy is why I left the error handler out in my original code. Display alerts
is one of those automatic resetting switches. Unlike some other switches that
do not reset themselves, such as EnableEvents. IMO you are best off to always
use an error handler. As you point out it is not always necesary, but it
never hurts. An error handler only takes a few seconds to add and if you are
wrong about what gets automatically reset, or Excel decides to change the way
it handles these things (like that would ever happen <bg)then you are
covered. Just my two cents...
--
HTH...

Jim Thomlinson


"Tom Ogilvy" wrote:

From the help on displayalerts:

"If you set this property to False, Micorosoft Excel sets this property to
True when the code is finished, unless you are running cross process code."

so while there is nothing wrong with what you suggest, what you imply is
usually not the case.

--
Regards,
Tom Ogilvy






"Jim Thomlinson" wrote:

This is an application level setting so you must be sure to reset it to True
when you are done. In fact it should really have an error handler on it to
ensure that it gets reset. Something like this (I neglected to add the error
handler in my post).

public sub Whatever()
on error goto ErrorHandler
application.displayalerts = false
'your code here
ErrorHandler:
application.displayalerts = true
end sub

--
HTH...

Jim Thomlinson


"Tiah" wrote:

application.DisplayAlerts=False

"Hoppy" a écrit dans le message de news:
...
I am running a macro across multiple tabs where I am removing subtotals.
Prior to the removal of the subtotals for each tab the "This removes the
subtotal and sorts again" notification pops-up and the "OK" button must be
clicked to proceed. How can I turn off this notification?