Screen shudder and jumping during macro run

[energizek]

Hi,

I have written a program in which the user uses check boxes to select/deselect worksheets that they want to have visible their application. As users check/uncheck the boxes, the names of the worksheets are updated on the "menu" (aka table of contents) page. The functionality of this macro works just fine...however whenever a check box is selected the screen goes through multiple shudders and flashes to the "Menu" page briefly before it finishes. I have included code to turn off screen updating, but this doesn't seem to help.

I'm all ears!!!

Thanks,
Katie

[VBA]
Private Sub cbAdvocacy_Click()
Application.ScreenUpdating = False
If Range("B14") = True Then
Application.Run "AdvocacyInMenu"
Worksheets("Advocacy_SP").Visible = True
End If

If Range("B14") = False Then
Application.Run "AdvocacyOutMenu"
Worksheets("Advocacy_SP").Visible = False
End If
Application.ScreenUpdating = True
End Sub

Sub AdvocacyInMenu()
Application.ScreenUpdating = False
Sheets("Advocacy_SP").Visible = True
'Worksheets("Menu").Unprotect
Sheets("Menu").Visible = True
Sheets("Menu").Select
'ActiveSheet.unProtect

Dim i As Integer
For i = 1 To 14
'Worksheets("Menu").Select
If IsEmpty(Cells(7 + i, 4)) Then
Cells(7 + i, 4).Select
ActiveCell.FormulaR1C1 = "Advocacy and Strategic Planning"
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
"Advocacy_SP!A1", ScreenTip:= _
"Click here to go to the ""Advocacy and Strategic Planning"" page", _
TextToDisplay:="Advocacy and Strategic Planning"
Selection.Font.Size = 12
'ActiveSheet.Protect
Sheets("Base Parameters").Select
Exit For
End If
Next i

Sheets("Base Parameters").Select
Application.ScreenUpdating = True
End Sub

Sub AdvocacyOutMenu()
Sheets("Advocacy_SP").Visible = False
RemoveVar = "Advocacy and Strategic Planning"
Call FindReplace
Sheets("Base Parameters").Select
End Sub

Private Sub FindReplace()
Application.ScreenUpdating = False
Sheets("Menu").Visible = True
' Where the variable "RemoveVar" represents the cell name to be removed.
On Error Resume Next
' Application.ScreenUpdating = False
Dim cellA As Range
' Sheets("Menu").Unprotect
'Sheets("Menu").Select
''ActiveSheet.unProtect
Worksheets("Menu").Activate
ActiveSheet.Unprotect
For Each cellA In Worksheets("Menu").Range("D821")
If cellA = RemoveVar Then
cellA.Select
Selection.ClearContents
Selection.delete Shift:=xlUp
End If
Next cellA
'ActiveSheet.Protect
Sheets("Base Parameters").Select
Application.ScreenUpdating = True
End Sub
[/VBA]

Edit: VBA tags added. energizek, select your code and hit the vba button to format it for the forum.

[lucas]

You are selecting a lot of sheets at various times and I can't see a reason for why you are doing it.

[energizek]

That is true. The only reason why I am selecting each sheet is because it the code didn't run properly otherwise. I will try to remove some of the sheet selections and see if the code will still run.

[Paul_Hossler]

Not sure about the overall flow, but you don't need to Select or Activate the sheets just to write to them. Instead of letting the code default to the Activesheet using just Cells ( ...), you could use Worksheet (name).Cells without selection.

That would save a lot of flipping back and forth


[vba]

Option Explicit
Sub AdvocacyInMenu()

Application.ScreenUpdating = False

Sheets("Advocacy_SP").Visible = True

With Worksheets("Menu")
.Unprotect
.Visible = True

Dim i As Integer
For i = 1 To 14
If IsEmpty(.Cells(7 + i, 4)) Then ' note the '.'. that ties it to the 'With'
.Cells(7 + i, 4).FormulaR1C1 = "Advocacy and Strategic Planning"
.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
"Advocacy_SP!A1", ScreenTip:= _
"Click here to go to the ""Advocacy and Strategic Planning"" page", _
TextToDisplay:="Advocacy and Strategic Planning"
.Font.Size = 12
.Protect
Worksheets("Base Parameters").Select ' needed????
Exit For
End If
Next i
End With

Application.ScreenUpdating = True
End Sub

[/vba]

Paul

[energizek]

Thanks! I think I've been able to fix it by removing all "Select" statements. I had no idea that was slowing things down.