Help!
I'm trying to create an Excel (2010) tracking tool in which staff can select Objectives via a series of checkboxes on a Scorecard (Sheet 1) which when checked/unchecked will either hide or unhide corresponding rows (of tasks) on the Task List (Sheet 2) in the workbook. If a "Not Pursuing" checkbox is checked (in Sheet 1) then only one row should be visible in Sheet 2 for this specific Objective. If, instead of "Not Pursuing", an "Option" is checked in Sheet 1, then that single row should hide and an adjacent series of rows should appear. The purpose is for teams to select (or ignore) a set of Objectives and have only those tasks that correspond to each Objective listed. There are approximately 40 Objectives to choose from and some of these have 3-4 Options available.
I gleaned, and adjusted, the code below from Scott Williford's YouTube video: https://www.youtube.com/watch?v=-4dcIYwnvpc (THANK YOU SCOTT.)
The code below represents three Objectives, each with one Option selected. I've had some success at making this work but when I Run the macros I receive a Debug error message "Run-time error '3': Return without GoSub". When I hit Debug the "Return" line is highlighted. Earlier I was able to fix this by replacing "Return" with "'final" but now when I do that, "End If" below it is highlighted. In the three samples listed below the first Option (1000) works as intended. The second two (2000 and 2100) both show only the uppermost row, no matter how each checkbox is toggled.
If anyone can troubleshoot where I am off I would be very grateful. I've been struggling with this for a few days now (yes, I am COMPLETELY
a novice, in case it wasn't obvious...) If there is an easy way to do this using a Spin Button or List Form I am open to that.
BTW, the checkboxes are Form Controls, not Active Controls.
Thanks for your help. David
Sub CheckBox_1000()
If Range("I7").Value = "False" Then
Call CheckBox_UNHIDE_1000
ElseIf Range("I7").Value = "True" Then
Call CheckBox_HIDE_1000
Else
Return
End If
End Sub
Sub CheckBox_HIDE_1000()
ActiveWorkbook.Sheets("Sheet2").Rows("30:48").EntireRow.Hidden = True
ActiveWorkbook.Sheets("Sheet2").Rows("29:29").EntireRow.Hidden = False
End Sub
Sub CheckBox_UNHIDE_1000()
ActiveWorkbook.Sheets("Sheet2").Rows("30:48").EntireRow.Hidden = False
ActiveWorkbook.Sheets("Sheet2").Rows("49:49").EntireRow.Hidden = True
End Sub
Sub CheckBox_2000()
If Range("I11").Value = "False" Then
Call CheckBox_UNHIDE_2000
ElseIf Range("I11").Value = "True" Then
Call CheckBox_HIDE_2000
Else
Return
End If
End Sub
Sub CheckBox_HIDE_2000()
ActiveWorkbook.Sheets("Sheet2").Rows("50:53").EntireRow.Hidden = True
ActiveWorkbook.Sheets("Sheet2").Rows("49:49").EntireRow.Hidden = False
End Sub
Sub CheckBox_UNHIDE_2000()
ActiveWorkbook.Sheets("Sheet2").Rows("50:53").EntireRow.Hidden = False
ActiveWorkbook.Sheets("Sheet2").Rows("49:49").EntireRow.Hidden = True
End Sub
Sub CheckBox_2100()
If Range("J11").Value = "False" Then
Call CheckBox_UNHIDE_2100
ElseIf Range("J11").Value = "True" Then
Call CheckBox_HIDE_2100
Else
Return
End If
End Sub
Sub CheckBox_HIDE_2100()
ActiveWorkbook.Sheets("Sheet2").Rows("55:57").EntireRow.Hidden = True
ActiveWorkbook.Sheets("Sheet2").Rows("54:54").EntireRow.Hidden = False
End Sub
Sub CheckBox_UNHIDE_2100()
ActiveWorkbook.Sheets("Sheet2").Rows("55:57").EntireRow.Hidden = False
ActiveWorkbook.Sheets("Sheet2").Rows("54:54").EntireRow.Hidden = True
End Sub
Moderator's note: Please use code tags when posting code (highlight the code and use the # button abobe the post). I have added them for you - this time
Bookmarks