Help Needed with Checkboxes to Hide or Unhide Rows

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 in the attachment 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

If I understand your problem correctly I believe this should help with you problem. I edited the code a little to get rid of the error and it
seemed to work properly. Another problem I noticed while working with your sample was that the data was not unhidden when you changed the check box from true to false I made a modification so that once you click away from the box in you sheet1 it will unhide the data.
TASK LIST GENERATOR.xlsm

Thanks. This is much closer to what I'm seeking but not quite there, yet. I am trying to make the checkbox function as an on/off toggle. When checking to a "False" status your solution does hide the task rows and unhides the single line "Not Pursuing" rows. But I would like the opposite to happen when unchecking to a "True" status, without having to check outside the range of cells in Sheet 2 (not Sheet 1). This latter feature I don't feel will be as intuitive to people. (I have to distribute this form to dozens of people and I really need it to be as streamlined as possible.) Also, my plan is to hide the False/True columns in Sheet 1 so that users only see the checkboxes.

Do you have time and patience to take another look? I REALLY appreciate your efforts. David

I am a little confused because currently your workbook returns "FALSE" when you box is unchecked and "TRUE" when checked your previous statement makes it seem like the opposite but maybe I am misunderstanding you. In any case I believe this solution will work. I have assigned your macros
to the check box so they will run whenever you change the check box with out having to click outside in sheet1.
TASK LIST GENERATOR.xlsm

Thanks very much! Now, can I ask one more simple question of you? In this line of code:
ActiveWorkbook.Sheets("Tasks").Rows("56:61").EntireRow.Hidden = True
how do I add a single non-consecutive row to the "56:61" range?

Thanks again. David

There may be a way to manipulate the range in that code but I'm not exactly sure it seems to me the simplest solution to that problem would be to add a second line of code hiding the nonconsecutive row. I used your macros to create this example and it worked fine in your sample workbook.

Please Login or Register  to view this content.

Be sure to add to both the hide and unhide other wise it will not unhide the rows. Hope this is the solution you are looking for.

This does the trick. Thanks for your patient help!

Not a problem glad i could help.