VBA code to stop 'screen flashing'

[ddander54]

I have some VBA code that I turn screen updating off to keep the screen from flashing, then turn it on later just before I end, however part of my code is doing some copy pasting, so if I turn it on after I do the copy paste, I don't see any of the data that is copy/pasted. If I turn it on before I start the copy/paste, the cells get updated, but the user sees all the screen flashing as it does the copy paste. What am I doing wrong, or how can I resolve this?

Sub currentcode()

    With Application
       .ScreenUpdating = False
       .Calculation = xlCalculationManual
       .EnableEvents = False
    End With

.....do a bunch of stuff....

    With Application
       .ScreenUpdating = True
       .Calculation = xlCalculationAutomatic
       .EnableEvents = True
    End With

....do the copy pasting......

End Sub

Sub WhatIwouldlike()

    With Application
       .ScreenUpdating = False
       .Calculation = xlCalculationManual
       .EnableEvents = False
    End With

.....do a bunch of stuff....
....do the copy pasting......

    With Application
       .ScreenUpdating = True
       .Calculation = xlCalculationAutomatic
       .EnableEvents = True
    End With

End Sub

Thanks,
Don

 

[Joe4]

Moving the ScreenUpdating and Calculation lines should have no impact on what gets pasted where.
However, the EnableEvents command MIGHT. By turning it back on BEFORE the pasting, it might trigger your event code to happen automatically, whereas having it after wouldn't.

What Event Procedure code do you have and when is it supposed to run (especially related to your code above)?

 

[ddander54]

Joe4,

I checked all the tabs (including hidden) and in this workbook I don't have any Event Procedures in any of the tabs. Using this process...... Right Click Tab, click View Code, empty code window.

Don

 

[Joe4]

Try deleting those all "EnableEvents" lines in your code.
Does that make any difference when you run the code?

 

[ddander54]

Joe4,

Thank you! It works perfectly as long as I leave one last command after it......MsgBox ("Successfully Completed the Task.")

Don

 

[Joe4]

Thank you! It works perfectly as long as I leave one last command after it......MsgBox ("Successfully Completed the Task.")

MsgBox should have no impact on anything.

If removing those lines helped, it seems to imply that you do have some Event Procedure code somewhere in your workbook.

 

[ddander54]

Joe4,

The only way I know of to check for Event Procedures is by right clicking the tabs and click View Code (that's the extend of my current knowledge). Is there something else I should be looking for?

Don

 

[Joe4]

The only way I know of to check for Event Procedures is by right clicking the tabs and click View Code (that's the extend of my current knowledge). Is there something else I should be looking for?

In the VB Editor, open up the VBA Project Explorer if not already open.
Then, expand the selections underneath the Workbook name.
Under Microsoft Excel Objects, you should see listings for all your sheets, but also one named "ThisWorkbook". You need to check there too.

 

[ddander54]

Joe4,

Blank there as well....

Don

 

[Joe4]

Without seeing the rest of your code, there isn't really much else I can offer.