+ Reply to Thread
Results 1 to 6 of 6

Using form checkbox responses to determine which worksheets to show/hide

  1. #1
    Registered User
    Join Date
    11-20-2013
    Location
    Eugene, Oregon
    MS-Off Ver
    Excel 2010
    Posts
    11

    Using form checkbox responses to determine which worksheets to show/hide

    Hi all,

    I'm a relative beginner at vba and just can't figure out why my code isn't working. I have three cells (E15, B15, & A15 in order of importance) that are tied to three check boxes (form control). When the box is checked, the cell says "True", when unchecked it says "False". There's no formulas in the cells, it's simply the Format Control - Cell Link option that says true or false.

    When E15 or B15 show True, I want OutriggersLG sheet to be visible and OutriggersSM sheet to be hidden.
    When A15 shows True, I want OutriggersSM sheet to be visible and OutriggersLG sheet to be hidden.
    If none show True, then I want both sheets visible.

    Here's my current code:
    Please Login or Register  to view this content.
    What's wrong??? I'm going nuts...

  2. #2
    Forum Expert
    Join Date
    07-31-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4,070

    Re: Using form checkbox responses to determine which worksheets to show/hide

    Clicking your checkboxes isn't going to trigger a worksheet_change event. Did you try assigning your code to them directly as a subroutine?

  3. #3
    Forum Contributor
    Join Date
    04-24-2012
    Location
    Karlstad, Sweden
    MS-Off Ver
    Excel 2016
    Posts
    232

    Re: Using form checkbox responses to determine which worksheets to show/hide

    Try this:

    Please Login or Register  to view this content.
    1. Reply to thread and inform if suggestion was helpful or not
    2. Click on the star (=Add Reputation) if you think someone helped you
    3. Mark [SOLVED] to this thread if solution was found. (On Menu "Thread Tools" > "Mark this thread as solved")

  4. #4
    Registered User
    Join Date
    11-20-2013
    Location
    Eugene, Oregon
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Using form checkbox responses to determine which worksheets to show/hide

    Stnkynts, I'm sorry but I don't understand what you mean by a subroutine. Right now, this is how the cell is changed:
    1.png

    Joakim, unfortunately the code you provided doesn't work either. I had something similar to that in an earlier trial too...

    I'm thinking another solution might be to do a Sub CheckBox09_Click() for each of the three boxes, but I need two of them (E15 & B15) to overwrite A15 even if if A15 is clicked last. So there's priority I don't know how to deal with using checkbox_click...

    Spreadsheet attached if that helps at all.
    Attached Files Attached Files

  5. #5
    Forum Expert
    Join Date
    07-31-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4,070

    Re: Using form checkbox responses to determine which worksheets to show/hide

    I will try to explain what to do:

    1. Under the visual basic editor you have an empty module 1. Select it and paste this code there:
    Please Login or Register  to view this content.
    2. On your main worksheet right click the "COS" checkbox and select Assign Macro. Assign the new Macro Hide_Sheet_byCheckboxes.
    3. Repeat step 2 for the Non-COS and the COS Homecare checkboxes.
    4. Enjoy

  6. #6
    Registered User
    Join Date
    11-20-2013
    Location
    Eugene, Oregon
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Using form checkbox responses to determine which worksheets to show/hide

    stnkynts, you are my HERO. Worked like a gem, except the true/false are backwards so the wrong sheet gets hidden - I can fix that easily. Thank you so very much, especially for explaining it so clearly!!!

    Love having you guys available for 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. Checkbox to show / hide workbook sheets
    By ed_b1 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-09-2013, 07:17 AM
  2. Show/Hide Checkbox
    By kalyanverma in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 01-12-2011, 01:33 PM
  3. Show/Hide columns by checkbox and cell text
    By ksmoore in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-09-2010, 02:20 PM
  4. Use checkbox to show/hide sheets?
    By Zheno in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-03-2010, 02:49 PM
  5. CheckBox to determine which rows to utilise and which to hide
    By nicoli_unt in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-11-2007, 11:48 AM

Tags for this Thread

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