+ Reply to Thread
Results 1 to 8 of 8

Using check boxes to hide and unhide worksheets

  1. #1
    Registered User
    Join Date
    04-24-2013
    Location
    Inola, Ok
    MS-Off Ver
    Excel 2007
    Posts
    6

    Using check boxes to hide and unhide worksheets

    I am working on a project in excel 2007 that will include an index worksheet with several checkboxes on it. The check boxes will hide and unhide certain worksheets. I watched a youtube video and wrote the code for one check box and it worked great but when I added another check box it seemed to work in reverse, when the box is checked the worksheet hides and when unchecked it appears. Here is the code I am using.


    Private Sub CheckBox1_Click()

    If Sheets(CheckBox1.Caption).Visible = xlVeryHidden Then
    Sheets(CheckBox1.Caption).Visible = -1
    Else
    Sheets(CheckBox1.Caption).Visible = xlVeryHidden
    End If



    End Sub

    Private Sub CheckBox2_Click()

    If Sheets(CheckBox2.Caption).Visible = xlVeryHidden Then
    Sheets(CheckBox2.Caption).Visible = -1
    Else
    Sheets(CheckBox2.Caption).Visible = xlVeryHidden
    End If

    End Sub

    I also have this in the Immediate box

    ?activesheet.visible
    -1

    ExcelHelp.jpg

    Like I stated before the first check box works just great but check box 2 seeme to do the opposite of what I need. Any idea what I need to do?

    Thanks
    Last edited by J_barber; 04-25-2013 at 03:13 PM.

  2. #2
    Forum Expert
    Join Date
    01-12-2007
    Location
    New Jersey
    Posts
    2,127

    Re: Using check boxes to hide and unhide worksheets

    Hi J_Barber,

    I want to offer this alternative to you because (1) I had just worked on a similar approach on a personal file, so the general code was available, and (2) this approach is more scalable. For example, if you add a new worksheet to the workbook, you don't have to program in a new checkbox and code it (test it out, add some worksheets).

    This isn't to say that my approach is ideal for your case, but it may be worth considering.

    To test it, open the attachment, Enable Conten, then click the giant button that says "Button 1".

    Let me know if this is something you can work with and if you need help adapting it.
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    04-24-2013
    Location
    Inola, Ok
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Using check boxes to hide and unhide worksheets

    That's pretty cool but I really need check box method for a specific reason. Appreciate the help though!

  4. #4
    Forum Expert
    Join Date
    01-12-2007
    Location
    New Jersey
    Posts
    2,127

    Re: Using check boxes to hide and unhide worksheets

    No problem. It was just a suggestion.

    More specifically, with respect to your question:

    The CheckBox_Click event can actually be quite confusing at times. That event simply tells the program to run the code whenever the CheckBox is clicked, irrespective of whether or not the box is actually checked off. It is up to the programmer to write code to determine the state of the checkbox.

    Try adapting the below code and let me know if it helps.

    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    04-24-2013
    Location
    Inola, Ok
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Using check boxes to hide and unhide worksheets

    That worked great! Thanks!

  6. #6
    Registered User
    Join Date
    04-12-2017
    Location
    Qatar
    MS-Off Ver
    2016
    Posts
    10

    Re: Using check boxes to hide and unhide worksheets

    Delete................................
    Last edited by nada.A; 04-12-2017 at 04:51 AM.

  7. #7
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Using check boxes to hide and unhide worksheets

    Quote Originally Posted by nada.A View Post
    Hey.. hope you can help!

    i want to hid/unhide multiple sheets with a checkbox click.. i wrote this code.. but i got this error... ( error 9 : subscript out of range )

    Private Sub CheckBox1_Click()

    If CheckBox1.Value = True Then
    Worksheets("Lusail_DC1_2S_UC").Visible = xlSheetVisible
    Else
    Worksheets("Lusail_DC1_2S_UC").Visible = xlSheetHidden
    End If

    If CheckBox1.Value = True Then
    Worksheets("Lusail_DC1_2S_S").Visible = xlSheetVisible
    Else
    Worksheets("Lusail_DC1_2S_S").Visible = xlSheetHidden
    End If

    If CheckBox1.Value = True Then
    Worksheets("Lusail_DC1_2S_E").Visible = xlSheetVisible
    Else
    Worksheets("Lusail_DC1_2S_E").Visible = xlSheetHidden
    End If

    End Sub



    Thanks a lot !
    Hi nada.A

    This post does not comply with Rules 3 & 5 of Forum rules....
    https://www.excelforum.com/forum-rul...rum-rules.html

    Herewith link to other post:https://www.excelforum.com/excel-pro...ml#post4628108
    Good Luck
    I don't presume to know what I am doing, however, just like you, I too started somewhere...
    One-day, One-problem at a time!!!
    If you feel I have helped, please click on the star to left of post [Add Reputation]
    Also....add a comment if you like!!!!
    And remember...Mark Thread as Solved.
    Excel Forum Rocks!!!

  8. #8
    Registered User
    Join Date
    04-12-2017
    Location
    Qatar
    MS-Off Ver
    2016
    Posts
    10

    Re: Using check boxes to hide and unhide worksheets

    sorry i posted it before reading the rules.. i am trying to delete it now.
    Thanks

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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