Sheets visible toggle code

  • Thread starter Thread starter Ambassador
  • Start date Start date
A

Ambassador

Running excel 2003 on two machines with different results. I have a form
checkbox to toggle visibility of "sheet2" listed below. It works on one and
I receive a error '9' subscript error on the machine i actually need it to
run on.

Sub CheckBox2_Click()
If Worksheets("sheet2").Visible = True Then
Worksheets("sheet2").Visible = False
Else
Worksheets("sheet2").Visible = True
End If

End Sub

I also want to include more than one sheet so I tried this code which
partially worked - it toggled once and then when I clicked the box again I
received a run-time error '1004':
"Unable to get the Visible property of the Sheets class".

Sub CheckBox2_Click()
If Worksheets(Array("sheet2", "sheet3")).Visible = True Then
Worksheets(Array("sheet2", "sheet3")).Visible = False
Else
Worksheets(Array("sheet2", "sheet3")).Visible = True
End If

End Sub

The code was placed in a module of the ActiveWorkBook. Where am I going
wrong? Thanks for your help.
 
It sounds like your user has renamed the Sheet2 to something else. Assuming
you got the CheckBox from the Control Toolbox toolbar (and not the Forms
toolbar), see if changing your code to this works...

Sub CheckBox2_Click()
With CheckBox2.Parent
If .Visible = True Then
.Visible = False
Else
.Visible = True
End If
End With
End Sub
 
I wouldn't rely on the Activesheet. I'd use the keyword Me instead.

You never know how the code will be run and what sheet will be active.


Rick said:
Good point... using "With Activesheet" should be good enough.

--
Rick (MVP - Excel)

joel said:
Rick: You don't need parent in this case if yo uare manually chaging the
checkbox because the active sheet is the parent sheet. Besiodes the
change function is in the VBA sheet that corresponds to the worksheet
where the checkbox is located.

I occasionally forget this fact.


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread:
http://www.thecodecage.com/forumz/showthread.php?t=164975

Microsoft Office Help
Click to expand...
Click to expand...
 
Back
Top