By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,632 Members | 1,499 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,632 IT Pros & Developers. It's quick & easy.

Check / Un-Check to display or hide a sheet

P: 4
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
Apr 5 '07 #1
Share this Question
Share on Google+
8 Replies


SammyB
Expert 100+
P: 807
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
Apr 6 '07 #2

P: 4
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
Apr 6 '07 #3

SammyB
Expert 100+
P: 807
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 :
Expand|Select|Wrap|Line Numbers
  1. Option Explicit
  2. Private Sub CheckBox1_Click()
  3.     If CheckBox1.Value = True Then
  4.         Worksheets("Sheet2").Visible = xlSheetHidden
  5.     Else
  6.         Worksheets("Sheet2").Visible = xlSheetVisible
  7.     End If
  8. 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: ChkSheet2Visible would be my choice.
Hope this helps! --Sam
Apr 6 '07 #4

P: 4
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
Apr 6 '07 #5

SammyB
Expert 100+
P: 807
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:
Expand|Select|Wrap|Line Numbers
  1. Option Explicit
  2.  
  3. Sub Vis2()
  4.     Sheets("Sheet2").Visible = Worksheets("Sheet1").Range("A1").Value
  5. 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
Apr 6 '07 #6

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

THANKS SO MUCH!
Apr 6 '07 #7

SammyB
Expert 100+
P: 807
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.
Apr 6 '07 #8

P: 1
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("Sheet2").Visible = xlSheetHidden
Else
Worksheets("Sheet2").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
Apr 9 '17 #9

Post your reply

Sign in to post your reply or Sign up for a free account.