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

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
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
Dim cellA As Range
Worksheets("Menu").Activate
ActiveSheet.Unprotect
For Each cellA In Worksheets("Menu").Range("D8:D21")
If cellA = RemoveVar Then
cellA.Select
Selection.ClearContents
Selection.delete Shift:=xlUp
End If
Next cellA
Application.ScreenUpdating = True
End Sub

 

[crimson_b1ade]

maybe also add Application.ScreenUpdating = False to your AdvocacyOutMenu() procedure as well - that one is missing. Also, try not to use .Select unless necessary (which is probably really never). It is not necessary to .Select a range to work with it.

 

[wigi]

As crimson_b1ade noted, take away *all* .Select statement.

Next, while it all may work, I advise you to use parameters. Why not have a Boolean argument (True or False) for a procedure called AdvocacyInOutMenu. This general procedure could then handle both In and Out.

Moreover, as you have 14 sheets or so, why not make it even more generic by having a parameter for the menu item/sheet name? I bet you're now copy-pasting lots of code.

I guess that the code you put above can be trimmed down to less than half what it's now. And for all 14 sheets, you will end up with about 10 % of the code. Needless to say, it takes time to write this generic code. But afterwards, benefits will be much greater.

Wigi

 

[energizek]

Thank you for your help wigi. I have solved the screen jumping by removing all select statements and condensing my code down into one procedure using boolean arguments. I really appreciate your help!!

 

[wigi]

Thank you for your help wigi. I have solved the screen jumping by removing all select statements and condensing my code down into one procedure using boolean arguments. I really appreciate your help!!

Well done Katie!