Screen Updating

Hi all,

I have a problem that i just cannot wrap my brain around.

My reset macro here, works terrific, and does not show any steps when running the macro.

Please Login or Register  to view this content.

But when i add two select lines to my macro, shown here, the screen updating begins and i cannot figure it out since i still have all of the screen updating code the same. This problem is killing me and i have been trying to figure it out for a few days now. Any help is greatly appretiated. Thanks

Please Login or Register  to view this content.

Why are you selecting those cells? What purpose does that serve?

I am selecting them because there is a macro on the other pages that will not work until that cell is selected. the macro is a conditional locking of cells, so i have to clear the contents in I8 and I9, the conditional cells, then select a non locked cell, then clear all of the cells that were locked. So I have to select C56 in order to clear the rest of the cells C37 to C56. Also on the second one it is the same locking macro. I am thinking i could use something like .Deselect, but i don't know the correct command for it. Thanks

I think your other macro is causing the screen to update. I ran a test on my computer and when selecting cells the update occurs at the very end of the macro with screen updating set to false. Make sure your other macros have the line

Please Login or Register  to view this content.

Does every macro on every page get that line, if so when in the code, very first... Also does it get Application.ScreenUpdating = True at the end?

Okay, your solution worked for that fix, but i am still having another problem. On the pages entitled Claim Count - Credibility, and Loss Development factors i have this code to make sure the user know to only copy and paste values. Whenever i run my reset macro with the .Select i am getting these boxes that pop up and i have to click okay for them to go away and continue the macro. This was not hapening with no .Select before but it was happening with it in, i was hopping it would go away with your last fix, but it does not. Do i need Screen updating = True at the end of them or what. Thanks,

Please Login or Register  to view this content.

Originally Posted by JosephP

is the other macro a worksheet_selectionchange event?

JosephP, i am not sure what a worksheet_selectionchange even is.

Any message box will run whether or not screen updating is false. So if at some point you activate a macro with a message box the code will run the box when it gets to it. One way to get around the message boxes is have a public variable as a boolean. When you go to reset, make the boolean false...

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

111StepsAhead,
I am sorry, I am pretty new to VBA, I understand what you are trying to do, but where does this code go? Does it go as a new macro or somewhere in my existing reset macro or in the existing box macros? Thank you for all of your help.

Originally Posted by JosephP

it is a routine that runs automatically in response to you selecting a different cell or cells on a sheet.
you need

Please Login or Register  to view this content.

to stop the activate event code from running when you select the sheet. make sure you turn events back on again though. :-)

JosephP, Like i told 111StepsAhead. I am a VBA newbie, where does your code go? Thank you

In your VBA project area you should see a list of modules on the left side. If you don't, go up to insert -> module. Now you will see a module. Select the one you created or any module. Near the top, not in any Sub or Function place the public variable.

Please Login or Register  to view this content.

Next you need to set the public or global variable you created. Sometimes people have a macro that automatically runs when the workbook opens. You can find that macro under the Microsoft Excel Objects, "ThisWorkbook". Near the top there is a dropdown. Select "Workbook". In the second dropdown to the right select Open. Now a new sub has been created. Put the second code there.

Please Login or Register  to view this content.

Finally, in every message box that you want to turn off put the final code.

Please Login or Register  to view this content.

I should mention that the public variable in this example is only set when you open the workbook. If you try to run the code without assigning a value to it the code will throw an error at you.