Results 1 to 11 of 11

Help Needed with Checkboxes to Hide or Unhide Rows

Threaded View

  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

    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
    Last edited by FDibbins; 06-23-2014 at 04:24 PM. Reason: Improper (ambiguous) title

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