Screen Flashing

[ranswert]

I have the following code when a worksheet is started and the exited:

Private Sub Worksheet_Activate()
Dim cst As String
Dim xcell As Range
Dim ycell As Range

unprotectsheet
stopautocalc
cst = Sheets("Databases").Range("currentcost")
Set xcell = Range(cst & "tlr")
Set ycell = Range(cst & "brr").Offset(4, 2)
Range(xcell, ycell).Select
Selection.EntireRow.Hidden = False
HideAroundSelection
Application.Goto Reference:=Worksheets("Cost Summary").Range("A1"), _
Scroll:=True
Range("a1").Select
startautocalc
protectsheet

End Sub

Private Sub Worksheet_Deactivate()
Unprotect
stopautocalc
Range("A:A").EntireRow.Hidden = False
startautocalc
protectsheet
End Sub

Sub stopautocalc()
With Application
.ScreenUpdating = False
.EnableEvents = False
.Calculation = xlCalculationManual
End With
End Sub

Sub startautocalc()
With Application
.ScreenUpdating = True
.EnableEvents = True
.Calculation = xlCalculationAutomatic
End With
End Sub

I would like to make it so that the screen doesn't flash when it was started
and exited. I thought the 'stopautocalc' would work, but it doesn't. Any
ideas on how to stop it from flashing?
Thanks

 

[cht13er]

I have the following code when a worksheet is started and the exited:

Private Sub Worksheet_Activate()
Dim cst As String
Dim xcell As Range
Dim ycell As Range

unprotectsheet
stopautocalc
cst = Sheets("Databases").Range("currentcost")
Set xcell = Range(cst & "tlr")
Set ycell = Range(cst & "brr").Offset(4, 2)
Range(xcell, ycell).Select
Selection.EntireRow.Hidden = False
HideAroundSelection
Application.Goto Reference:=Worksheets("Cost Summary").Range("A1"), _
Scroll:=True
Range("a1").Select
startautocalc
protectsheet

End Sub

Private Sub Worksheet_Deactivate()
Unprotect
stopautocalc
Range("A:A").EntireRow.Hidden = False
startautocalc
protectsheet
End Sub

Sub stopautocalc()
With Application
.ScreenUpdating = False
.EnableEvents = False
.Calculation = xlCalculationManual
End With
End Sub

Sub startautocalc()
With Application
.ScreenUpdating = True
.EnableEvents = True
.Calculation = xlCalculationAutomatic
End With
End Sub

I would like to make it so that the screen doesn't flash when it was started
and exited. I thought the 'stopautocalc' would work, but it doesn't. Any
ideas on how to stop it from flashing?
Thanks

Try Application.ScreenUpdating = False at the beginning of your code
and
Application.ScreenUpdating = True when it's done.

Chris

 

[ward376]

I would like to make it so that the screen doesn't flash when it was started

and exited.  I thought the 'stopautocalc' would work, but it doesn't.

Setting screen updating to false will keep it from flashing while
you're doing stuff, but the screen will update when it's set back to
true and the appearance of the sheet has changed. Is this all your
code?

Cliff Edwards

 

[ranswert]

I tried putting 'Application.ScreenUpdating = False ' for the first line of
the code for the 'worksheet_Activate' sub routine. It still flashes the
sheet contents before it runs the 'HideAroundSelection'. The
'HideAroundSelection' code hides the whole sheet except for the last range
that was being used.
Is there a way to have it blank when the sheet is activated?

 

[ranswert]

It flashes the screen before 'Hidearoundselection' code is run. The
'hidearoundselection' code hides the whole sheet except for the last range of
cells that was being used. Is there a way to not flash the screen before the
code 'hidearoundselection' is run. I already have the screen updatigng set to
false.

 

[cht13er]

It flashes the screen before 'Hidearoundselection' code is run. The
'hidearoundselection' code hides the whole sheet except for the last range of
cells that was being used. Is there a way to not flash the screen before the
code 'hidearoundselection' is run. I already have the screen updatigng set to
false.

You could always be draconian and use Application.Visible = False ??

It's still not perfect (I don't know how to get around an occasional
1/100th of a second screen flash) but it might be better... check it
out.

C

 

[ranswert]

Thanks I'll give that a try

You could always be draconian and use Application.Visible = False ??

It's still not perfect (I don't know how to get around an occasional
1/100th of a second screen flash) but it might be better... check it
out.

C