I just cannot figure out what your code is doing without a lot more study.
- I've modified your code in Module1; your code worked but did not use best practices.
- One of your Subs does not compile
- I've put comments in your code in GPS with minor changes. It will not be hard to find my comments because there were no comments to start with. This is another issue; I recommend using comments even if they just as reminders to yourself. You'll read this code in 6 months and won't remember what you were trying to do.
- I also recommend you go to Tools, Options, and check Require variable declarations.
Your file is too big for me to post back but here is the code.
Module1
Option Explicit
' Clear all checkboxes in all worksheets
Sub CodeForEachSheet()
Dim ws As Worksheet
Application.ScreenUpdating = False
For Each ws In Worksheets
ClrChk2 ws
Next ws
Worksheets(1).Activate
Application.ScreenUpdating = True
Calculate
End Sub
' Clear all checkboxes in worksheet ws
Sub ClrChk2(ws As Worksheet)
Dim myBox As CheckBox
For Each myBox In ws.CheckBoxes
myBox.Value = False
Next myBox
End Sub
GPS, only the first two Subs. The comments and techniques shown here apply to the rest of your code in this module as well.
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
' You are always doing this if the number of advisors changes
' but it is not at all clear why
If Target.Address = "$I$4" Then HideRows4
End Sub
Sub HideRows3()
' I cannot see a pattern as to which sheets you are choosing to show here
' 13-20, 23-27, 32-33
' There is almost certainly a more robust way to do this if you could
' define the logic behind why you need to show these
Sheet13.Visible = True
Sheet14.Visible = True
Sheet15.Visible = True
Sheet16.Visible = True
Sheet32.Visible = True
Sheet17.Visible = True
Sheet18.Visible = True
Sheet19.Visible = True
Sheet20.Visible = True
Sheet23.Visible = True
Sheet24.Visible = True
Sheet33.Visible = True
Sheet25.Visible = True
Sheet26.Visible = True
Sheet27.Visible = True
'With Sheets("GPS") ' this code is in GPS so you don't need this With. References
' to worksheet attributes default to the sheet that the code is in.
' Also omit the leading dot.
Rows("15:29").EntireRow.Hidden = False ' why are you starting on row 15?
If Range("I4").Value = 0 Then
Rows("15:29").EntireRow.Hidden = False ' you already did this in the previous line of code
' so this line doesn't change anything
Else
Rows(Range("I4").Value + 5 & ":29").EntireRow.Hidden = True
End If
' I4 is the number of advisors so I can only guess that you are trying to
' hide all the sheets that are above the number of advisors, implying that
' there is an order to the sheets.
' Sheet13.Visible = Range("I4").Value > 10
' Sheet14.Visible = Range("I4").Value > 11
' Sheet15.Visible = Range("I4").Value > 12
' Sheet16.Visible = Range("I4").Value > 13
' Sheet32.Visible = Range("I4").Value > 14
' Sheet17.Visible = Range("I4").Value > 15
' Sheet18.Visible = Range("I4").Value > 16
' Sheet19.Visible = Range("I4").Value > 17
' Sheet20.Visible = Range("I4").Value > 18
' Sheet23.Visible = Range("I4").Value > 19
' Sheet24.Visible = Range("I4").Value > 20
' Sheet33.Visible = Range("I4").Value > 21
' Sheet25.Visible = Range("I4").Value > 22
' Sheet26.Visible = Range("I4").Value > 23
' Sheet27.Visible = Range("I4").Value > 24
' Because the names in column A are the same as the sheet names, I would replace the above code with the following code BUT only
' if that naming convention is deliberate and dependable!
' This is what I meant by indexing the worksheets. The code below links the worksheets to the corresponding rows
' on GPS, so that it is insulated from changes in what sheets are in what order, or their code names are.
Dim c As Range
For Each c In Range(Cells(5 + Range("I4").Value, 1), Cells(29, 1))
Worksheets(c.Value).Visible = False
Next c
'End With
Calculate ' this should happen automatically unless you've disabled calculation
End Sub
Also, in GPS this is preventing your code from compiling
If Range("I4").Value >= 0 Then Rows("15:29").EntireRow.Hidden = False ' your If statement ends here
Sheet13.Visible = True
Sheet14.Visible = True
Sheet15.Visible = True
Sheet16.Visible = True
Sheet32.Visible = True
Sheet17.Visible = True
Sheet18.Visible = True
Sheet19.Visible = True
Sheet20.Visible = True
Sheet23.Visible = True
Sheet24.Visible = True
Sheet33.Visible = True
Sheet25.Visible = True
Sheet26.Visible = True
Sheet27.Visible = True
End If ' so this End If has no If to match against, causing a compile error
VBA If is a little eccentric in that if you have
If <condition> Then <statement>
all on one line, there is no "End If". If you want the statement after the Then to be included in the code executed when the condition is true, simply move it to the next line.
LinkBack URL
About LinkBacks
Register To Reply
Originally Posted by Excel Forum
Bookmarks