A function to replace all the application.screenupdating references

Is there a method which could replace all of the numerous places in code in-which the code below is used? Maybe some type of function which is called instead of repeating the same code over and over or maybe this is the best method.

Please Login or Register  to view this content.

Edit: Sorry posted in the wrong forum

Jeff,

You should be able to create a standard module and in it create a Public Sub with that code inside. You can then call that Sub from any worksheet within the workbook.

Hi Paul,

Since I am still learning the ropes of VBA, is this a good method or should you stick within each procedure or is it just preference?

Generally if you repeat steps multiple times it is cleaner to have one instance of it, then call it when necessary. It does save a little memory. Normally it would be used for repetitive functions or more drawn-out code, but it will work in this instance just as in any other.

Maybe you could use somthing like this, Jeff. It maintains a stack of states so subs can push their desired state and then pop to return to the state when they were called. You can skip arguments that you don't care about.

Minimally tested.

Please Login or Register  to view this content.

Thanks Paul/shg,

@shg...I couldn't quite get your code to cooperate but I will keep trying. In the meantime I have run across this...

Please Login or Register  to view this content.

So far with my testing I have not seen any problems but from your experience do you see this as a sound method?

I got the idea from here

Please Login or Register  to view this content.

@shg, can you outline the use of VBA qualifier in the above ?

I've seen Colin using the VBA qualifier of late and presume this is for good reason but have found little info. on it.

@ DO: VBA.Array always generates a zero-based array, even if the user has Option Base 1.

@ Jeffrey: The potential problem is that (a) AppStateFalse always sets each of the properties "off" when that may not be exactly what you want, and (b) you must alternately call AppStateFalse and AppStateRestore or else you lose the state. Mine was intended to solve the case where nested subs may want to set their environment during execution and then restore it on return to the calling routine.

Please Login or Register  to view this content.

What problems are you having? As I said, it got only a two-minute test from me.

Hi shg,

Chalk it up to being over tired last night. I was racking my brain trying to understand it and I was failing, but with your further explanation did the trick.

What I was failing to do was actually change the attributes in the AppOnOff Push part.

I set up a msgbox to display the status, TRUE/FALSE, and it is working fine. Thanks for you patience and instruction.

Great!

1. For consistency, and since there is no collision between vbUseDefault (=-2) and valid calculation states (-4135, -4105, and 2), I think the below is better.

2. I changed it to a function, which returns True unless you try to Pop an empty collection.

Please Login or Register  to view this content.

For performance considerations, col would better be declared as a module-level variable rather than local and static, but I'm going to leave it as is.

Hi again shg,

No matter what combination I run for the AppOnOff Push, I get the MsgBox which says "Oops!". Any ideas why, but in the end everything is restored to the previous state.

I trust you mean for Pop.

Sorry, corrected in prior post.

Works grand