+ Reply to Thread
Results 1 to 11 of 11

Help Needed with Checkboxes to Hide or Unhide Rows

  1. #1
    Forum Contributor
    Join Date
    05-23-2014
    Location
    Fredericia, Denmark
    MS-Off Ver
    Windows Office 2013, Excel 10
    Posts
    139

    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 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

    Please Login or Register  to view this content.
    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
    Last edited by FDibbins; 06-23-2014 at 04:24 PM. Reason: Improper (ambiguous) title

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Macro dilemma...

    Hi, and welcome to the forum. Unfortunately your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

    Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

    To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

    (This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

    You've also committed another cardinal sin and not wrapped your macro code in code tags - see rules.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Forum Contributor
    Join Date
    05-23-2014
    Location
    Fredericia, Denmark
    MS-Off Ver
    Windows Office 2013, Excel 10
    Posts
    139

    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
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    06-18-2014
    Location
    Dallas, Texas
    MS-Off Ver
    Home and Business 2010
    Posts
    84

    Re: Macro dilemma...

    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

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Help Needed with Checkboxes to Hide or Unhide Rows

    dgibney welcome to the forum

    Thanks for the title change For future reference, you need to make the change on your 1st post (I changed it there for you)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  6. #6
    Forum Contributor
    Join Date
    05-23-2014
    Location
    Fredericia, Denmark
    MS-Off Ver
    Windows Office 2013, Excel 10
    Posts
    139

    Re: Macro dilemma...

    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

  7. #7
    Registered User
    Join Date
    06-18-2014
    Location
    Dallas, Texas
    MS-Off Ver
    Home and Business 2010
    Posts
    84

    Re: Help Needed with Checkboxes to Hide or Unhide Rows

    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

  8. #8
    Forum Contributor
    Join Date
    05-23-2014
    Location
    Fredericia, Denmark
    MS-Off Ver
    Windows Office 2013, Excel 10
    Posts
    139

    Re: Help Needed with Checkboxes to Hide or Unhide Rows

    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

  9. #9
    Registered User
    Join Date
    06-18-2014
    Location
    Dallas, Texas
    MS-Off Ver
    Home and Business 2010
    Posts
    84

    Re: Help Needed with Checkboxes to Hide or Unhide Rows

    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.

  10. #10
    Forum Contributor
    Join Date
    05-23-2014
    Location
    Fredericia, Denmark
    MS-Off Ver
    Windows Office 2013, Excel 10
    Posts
    139

    Re: Help Needed with Checkboxes to Hide or Unhide Rows

    This does the trick. Thanks for your patient help!

  11. #11
    Registered User
    Join Date
    06-18-2014
    Location
    Dallas, Texas
    MS-Off Ver
    Home and Business 2010
    Posts
    84

    Re: Help Needed with Checkboxes to Hide or Unhide Rows

    Not a problem glad i could help.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Drop down box dilemma
    By mvburg in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 09-11-2012, 11:39 AM
  2. [SOLVED] Capacity dilemma
    By Aland2929 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 08-10-2012, 11:51 AM
  3. [SOLVED] Do Until Loop Dilemma
    By mellowe in forum Excel General
    Replies: 2
    Last Post: 04-17-2006, 12:45 PM
  4. Autofill Dilemma
    By toby83 in forum Excel General
    Replies: 1
    Last Post: 01-23-2006, 09:15 AM
  5. [SOLVED] The Prisoner's Dilemma
    By Scott in forum Excel General
    Replies: 0
    Last Post: 12-08-2005, 04:10 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1