Run macro without "screen flicker"

[alexbat]

Hi,

I´m trying to understand how to run a macro from Sheet 1 in a workbook that changes Sheet 3.

Running this code

Sub Test1()
  
    Worksheets("Sheet3").Activate
    Range("H4").Select
    ActiveCell.FormulaR1C1 = "A"
    Range("H5").Select
    Worksheets("Sheet3").Activate
    
End Sub

Excel switches between Sheet 1 and Sheet 3. How could I do instead to run the script from Sheet 1 without switching to Sheet 3 and back?

Thank you in advance!

 

[JoeMo]

You don't need to activate a sheet and select a cell on it to change the value of that cell or place a worksheet formula in it. But, if that's the way you want to do it try adding this line at the beginning:
Application.ScreenUpdating = False

and just before the End Sub line add this:
Application.ScreenUpdating = True

 

[mumps]

The following should eliminate screen flicker:

Sub Test1()
    Application.ScreenUpdating = False
    Worksheets("Sheet3").Activate
    Range("H4").Select
    ActiveCell.FormulaR1C1 = "A"
    Range("H5").Select
    Worksheets("Sheet3").Activate
    Application.ScreenUpdating = True
End Sub

 

[alexbat]

Thank you, guys!

For this simple example it worked fine. Is this the way to go with more complex code too?

 

[mumps]

It will work with a more complex macro. What it does is it prevents the screen from refreshing itself as each line of code executes. This prevents the flicker and also speeds up the code.

 

[JoeMo]

Thank you, guys!

For this simple example it worked fine. Is this the way to go with more complex code too?

NO. The unnecessary activation and selection just slows things down.

 

[mumps]

Joe is perfectly correct. I was referring to the use of 'Application.ScreenUpdating'.

 

[alexbat]

JoeMo,

how could I change this (or any other) code to perform actions in Sheet 3 while Sheet 1 is selected?

 

[HalfAce]

With your example code above, you can replace all these lines:
Application.ScreenUpdating = False
Worksheets("Sheet3").Activate
Range("H4").Select
ActiveCell.FormulaR1C1 = "A"
Worksheets("Sheet3").Activate
Application.ScreenUpdating = True
with just this:
Sheets("Sheet3").Range("H4").Value = "A"
and get the same results, no matter which (different) sheet might be the active sheet when the code gets run.

Of course if you wanted the user to be left in cell H5 on sheet 3 afterward, you will have to select or activate the sheet first and then select or activate the range.
Sheets("Sheet3").Select
Range("H5").Select

Does that help at all?

 

[alexbat]

This was just a simple example... How should I do in more complex cases, for instance if I trigger a macro with a radio button in Sheet 1, that will sort and do advanced filtering in Sheet 3, but I want to stay in Sheet 1. Maybe hard to answer without me providing code example...

The ScreenUpdating works for now I guess.