Suppressing screen flicker when sheet is activated

[Raj]

Hi,

I am using Application.Screenupdating = false at the beginning of the
sub and Application.Screeenupdating = true at the end of the sub. The
sub contains a line to activate another sheet to select a range in
that sheet. The sheet activation does flash on the screen, ie.
Application.Screenupdating = false seems to no longer work when a
sheeet is activated. How do I fix this issue?

Thanks in advance for the help.


Regards,
Raj

 

[james]

if all you want to do is swap sheets, you could instead use:

Sheets("Sheet_Name").Select

it seems not to show when screenupdating is false.

 

[Raj]

Hi,

To clarify further, the code in the worksheet_change sub::

1. Activates another sheet, selects a range in that sheet and creates
a pivot table there.
2. It then takes data from the created pivot table and writes it back
to the original sheet and displays this sheet to the user

To achieve 1, I have used sheet activate and range.select of the other
sheet
To achieve 2, I have activated the current sheet again.

All this code is in the worksheet_change event of the current sheet. I
have used screenupdating = false at the beginning of the sub and
screenupdating = true at the end. Anything else I need to do to stop
screen flicker?

Regards,
Raj

 

[Norman Jones]

Hi Raj,

It neither necessary nor efficient to open a
new thread for an existing question.

Referring to your earlier thread, if you have
tried to implement Jon Peltier's approach
without success, then as indicated by Nate,
you should consder posting the problematic
code.

 

[Raj]

Hi Norman,

I am sorry about opening a new thread for an existing question and
note to observe this in future. But, there is a substantial difference
between the issues involved in the two threads. In the earlier thread
subs across different worksheets were involved, whereas in the current
threat there is only one sub that is involved. I thought the
underlying causes for the problem (and the solution) could be
different and hence posted this afresh.

The Code:

Private Sub Worksheet_Activate()
Application.ScreenUpdating = False

'After this many variables are declared and initiated
'ptrange is a range object

Set ptrange = Worksheets(wsname)
Worksheets(wsname).Select
ptrange.Cells(4, 1).Select

' This is followed by code for creating a Pivot table in ptrange and
writing some values from that table into the current worksheet

'Lastly the following code is used to Activate the sheet containing
the code.
Application.EnableEvents = False
Me.Activate
Application.EnableEvents = True

End Sub


Thanks and Regards,
Raj

 

[Norman Jones]

Hi Raj,

In general, a primary method to avoid
screen flicker, is to avoid the selection
of Excel objects, such as ranges, sheets
and workbooks.

With very few exceptions, it is neither
necessary, nor efficient to make such
selections; the preferable alternative is
to create object varisbles and then
manipulate the variable.

Another ploy, which has the incidental
advantage of increasing efficiency and
speed, is to avoid repeated read/write
worksheet operations; better is to read
and write data to and from arrays held
in memory, often as single operations.

Methods such as these, perhapsa allied
to the judicious use of the Applicaton's
ScreenUpdating property should
enhance speed / efficiency and largely
obviate the flicker problem.



---
Regards.
Norman


Hi Norman,

I am sorry about opening a new thread for an existing question and
note to observe this in future. But, there is a substantial difference
between the issues involved in the two threads. In the earlier thread
subs across different worksheets were involved, whereas in the current
threat there is only one sub that is involved. I thought the
underlying causes for the problem (and the solution) could be
different and hence posted this afresh.

The Code:

Private Sub Worksheet_Activate()
Application.ScreenUpdating = False

'After this many variables are declared and initiated
'ptrange is a range object

Set ptrange = Worksheets(wsname)
Worksheets(wsname).Select
ptrange.Cells(4, 1).Select

' This is followed by code for creating a Pivot table in ptrange and
writing some values from that table into the current worksheet

'Lastly the following code is used to Activate the sheet containing
the code.
Application.EnableEvents = False
Me.Activate
Application.EnableEvents = True

End Sub


Thanks and Regards,
Raj