Screen Flickers with screenupdating set to false

[Salsasteve]

I have a Workbook with four worksheets, one of which, called "Rota", flickers as if it is writing the whole screen when it is activated, despite the fact that I have turned off screen updating in worksheet activation. I can't figure it out. The other sheets don't flicker when activated and it is beginning to annoy me!

Any help would be appreciated! Code for worksheet activation below.


Private Sub Worksheet_Activate()
Application.ScreenUpdating = False
'On Error GoTo ErrorHandler
If Not HolidaysAssigned Then
Call DeleteHolCodes
Call AssignHol
End If
'Check if Rota toolbar exists
Dim MyBar As CommandBar
Dim RotaBarExists As Boolean
For Each MyBar In Application.CommandBars
If MyBar.Name = "Rota" Then RotaBarExists = True
Next MyBar
If Not RotaBarExists Then Call CreateToolbar
Application.CommandBars("Rota").Visible = True
Call EnableToolbar
Application.ScreenUpdating = True
Exit Sub
ErrorHandler:
Application.ScreenUpdating = True
MsgBox "Error in Rota Activation code, please notify programmer ", vbExclamation, "Error!"

End Sub

[GTO]

Greetings,

Without seeing what's in the two called procedures, I do not see how we could venture even a guess as to what's happening.

From the above however, I believe you can speed up processing. As soon as it finds the Rota toolbar, exit the For:

Option Explicit

Private Sub Worksheet_Activate()
Dim MyBar As CommandBar
Dim RotaBarExists As Boolean

Application.ScreenUpdating = False

'On Error GoTo ErrorHandler
If Not HolidaysAssigned Then
Call DeleteHolCodes
Call AssignHol
End If

'Check if Rota toolbar exists
For Each MyBar In Application.CommandBars
If MyBar.Name = "Rota" Then RotaBarExists = True
Exit For
Next MyBar

If Not RotaBarExists Then Call CreateToolbar
Application.CommandBars("Rota").Visible = True
Call EnableToolbar

Application.ScreenUpdating = True

Exit Sub
ErrorHandler:
Application.ScreenUpdating = True
MsgBox "Error in Rota Activation code, please notify programmer ", vbExclamation, "Error!"
End Sub


When posting code, it is easier to read if you use the little green/white VBA button atop the message box that you are typing your post in. Press the VBA button and paste the code between the tags.

Hope that helps,

Mark

[Salsasteve]

Thanks for tidying up my code and the tip on showing the code clearer on this site. I'm new to this, as you can tell!

I tried jumping out of the loop once the toolbar is found, but this makes the screen jump! I've stepped through the code and the screen flickers on each line of code as though it is being re-written each time - weird. Maybe this is a Windows issue rather than an Excel one (I'm using XP with Excel 2000)?
Guess I'll have to put up with it!

Steve

[mdmackillop]

Try without the loop

Dim mybar

On Error Resume Next
Set mybar = Toolbars("Rota")
If Not mybar Is Nothing Then RotaBarExists = True
On Error GoTo 0

[GTO]

:banghead: ACK!


:bow: Yes Sir. (slunk..slunk...off to the corner, tail tucked...)