Check / Un-Check to display or hide a sheet

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • tezarin
    New Member
    • Apr 2007
    • 4

    Check / Un-Check to display or hide a sheet

    Hi all,

    I have an Excel sheet whith 2 sheets called Sheet1 and Sheet 2.
    I added a checkbox to the sheet 2 and would like it to work like this:
    1) When the checkbox is checked the Sheet1 becomes hidden
    2) When the checkbox is un-checked the Sheet1 becomes visible

    I wrote the code below but I keep getting the "run-time error 424 Object required" error. Please help me with that if possible. Thanks.

    Private Sub CheckBox1_Click ()
    If CheckBox1.Value = True Then
    Sheet1.Visible = xlSheetHidden
    Else
    Sheet1.Visible = xlSheetVisible
    End If
    End Sub
  • SammyB
    Recognized Expert Contributor
    • Mar 2007
    • 807

    #2
    Your code works for me.
    1. Did you use the Visual Basic Toolbar to get the checkbox? There also a CheckBox on the old Forms Toolbar that would require different code.
    2. Did you right-click, View Code, to insert the code? The code must be in the Worksheet code that contains the CheckBox. It cannot be in a Module (where you place macros).
    HTH --Sam

    Comment

    • tezarin
      New Member
      • Apr 2007
      • 4

      #3
      Hi Sammy,

      Thanks for your reply. I tried it, but it didn't worked.
      I am doing something wrong... Probably the name of the checkbox is not CheckBox1....
      The code works if I create a UserForm in Visual Basic View, but I would like the checkbox to be on Sheet2.

      1) In Excel 2003, I put a checkbox from the Form toolbar (in sheet view not in visual basic view) on the sheet 2, the name shown there is Check Box 1.

      2) Then I go to "Tools" menu and I select "Macro", then "Visual Basic Editor".

      3) Then I select "Sheet2" from the left panel and double click it.

      4) Then I put my macro there.

      But then, when I come back to the Sheet2 and click the checkbox, nothing happens.

      I even tried the other way:

      1) I right clicked the checkbox and selected "Assign Macro", then "New" and it opens VBA Editor and a new module is created which I can put my code there. That fails, too. After I put my code and go back to the sheet I get an "Object Required error 424" error.

      Please help me. would that be possible for you to email me the excel file please?
      My email is tezarin AT yahoo DOT com

      Thanks

      Comment

      • SammyB
        Recognized Expert Contributor
        • Mar 2007
        • 807

        #4
        There was very little that you did that was correct. Please, do it my way. I will make it simple:
        1. Close any existing Excel workbooks and then open up a new workbook.
        2. Right-click on the menu bar and choose Customize. On the Toolbar tab, make sure that only Standard, Formatting, Visual Basic, and Worksheet Menu Bar have a checkmark before them. Do not use the Forms toolbar. Once you have just the four toolbars, press Close.
        3. Press the Control Toolbox button on the Visual Basic Toolbar.
        4. On the Control Toolbox, depress the Checkbox button and draw a checkbox on Sheet1.
        5. Right-click on the checkbox and select View Code.
        6. Now you are in Excel's VBA IDE. Notice that on the left, you have the Project Explorer and Sheet1 is gray, Also the Taskbar says that you are editing Sheet1 (Code). Notice also that Excel has created an outline of the event code.
        7. Replace the code with :
        Code:
        Option Explicit
        Private Sub CheckBox1_Click()
        	If CheckBox1.Value = True Then
        		Worksheets("Sheet2").Visible = xlSheetHidden
        	Else
        		Worksheets("Sheet2").Visible = xlSheetVisible
        	End If
        End Sub
        1. My code is a little different than yours: (1) Since we are on Sheet1, I hid Sheet2. (2) I used the Worksheets property which is indexed by the names that are on the sheet tabs.
        2. Click the X in the upper-right to close the IDE.
        3. In Excel, un-press the Design mode button on the Control Toolbox to exit Design mode. Also, press the X to close the Control Toolbox.
        4. Notice that all three sheet tabs are visible. Now, click on the CheckBox and notice that Sheet2 Tab disappears. If you want it visible again, than you can either uncheck the checkbox or use the Format, Sheet, Unhide... menu.
        5. Finally, on the Visual Basic toolbar, press the Design Mode button; and then, right-click on the CheckBox and select properties. You can change some of these properties and see what effect they have. Notice that if you change the name, your code no longer works. So, it is better to change the name before you write the code. Use a good name: ChkSheet2Visibl e would be my choice.
        Hope this helps! --Sam

        Comment

        • tezarin
          New Member
          • Apr 2007
          • 4

          #5
          Hey Sammy,

          Thank you very much for your very helpful reply. It worked great. I appreciate your help so much :)

          Have a great weekend,
          Tezarin

          Comment

          • SammyB
            Recognized Expert Contributor
            • Mar 2007
            • 807

            #6
            OK, now we will do it your way:
            1. Start with a new workbook.
            2. Right-click on menu, customize. Just have Standard, Formatting, Forms, and Worksheet Menu Bar. Press Close.
            3. Tools, Macro, Record New Macro. Make the macro name Vis2.
            4. Press the stop recording button.
            5. Tools. Macro, Macros. Press the Edit button to edit Vis2. Notice that you are now editing code in Module1. Change the code to:
            Code:
            Option Explicit
             
            Sub Vis2()
            	Sheets("Sheet2").Visible = Worksheets("Sheet1").Range("A1").Value
            End Sub
            Now for the CheckBox:
            1. On the Forms toolbar, press the CheckBox button and draw the checkbox on Sheet1.
            2. Right-Click on the checkbox and choose Format Control.
            3. For the Value, select Checked. For the Cell Link, enter $A$1. Press OK.
            4. Right-click on the checkbox and choose Assign Macro. Select Vis2 and press OK.
            Additional comments:
            1. It is better to use a named range for the cell link. If you named the cell nVis2, then use nVis2 in the cell link and change Range("A1") to Range("nVis2")
            2. You probably don't want the user to see the link cell, so put it on a configuration sheet. You can also use "white ink" (Font color white), but then you'll not see it either. Dangerous.
            3. Yes, it is possible to not use a cell link, but you will drive you sucessor insane, so I'm not going to show you. A hint is that you must use the OLEFormat property.
            Finally, guidelines for which checkbox to use:
            • For simple workbooks, use the VisualBasic controls. You have more options and they behave like standard controls.
            • For very complex workbooks, use the Forms controls. They are lightweight: ie, they use less memory. If your workbook is crashing weirdly, you need to replace the Visual Basic controls with Forms controls.
            • In any case, be consistent. Don't use both types of comtrols. The maintence programmer may go postal on you.
            Keep smiling! :D Sam

            Comment

            • tezarin
              New Member
              • Apr 2007
              • 4

              #7
              Thanks so much for your great help. It is working great and I have submitted it to my boss :)

              THANKS SO MUCH!

              Comment

              • SammyB
                Recognized Expert Contributor
                • Mar 2007
                • 807

                #8
                Originally posted by tezarin
                Thanks so much for your great help. It is working great and I have submitted it to my boss :)

                THANKS SO MUCH!
                Great! I just thought I'd post both sides so that searchers will get complete information.

                Comment

                • Nadaaaaaa
                  New Member
                  • Apr 2017
                  • 1

                  #9
                  Hi sammy,

                  Your replies helped me a lot.. I have used this code and it worked for me

                  -------
                  Private Sub CheckBox1_Click ()
                  If CheckBox1.Value = True Then
                  Worksheets("She et2").Visible = xlSheetHidden
                  Else
                  Worksheets("She et2").Visible = xlSheetVisible
                  End If
                  End Sub
                  ---------------

                  One last question.. how this code can be modified to hide/unhide multiple sheets at the same time?

                  Many thanks!
                  Nada

                  Comment

                  Working...