Sheets visible toggle code

[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.

 

[joel]

Assuming the checkbox number and the sheet numbers are the same


Private Sub CheckBox2_Click()
For Each bx In ActiveSheet.OLEObjects
Objtype = TypeName(bx.Object)
If Objtype = "CheckBox" Then
'extract number of box
boxnum = Val(Replace(bx.Name, "CheckBox", ""))
If bx.Object.Value = True Then
Sheets(boxnum).Visible = True
Else
Sheets(boxnum).Visible = False
End If
End If

Next bx

End Su

 

[Rick Rothstein]

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

 

[joel]

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.

 

[Rick Rothstein]

Good point... using "With Activesheet" should be good enough.

 

[Dave Peterson]

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.

Good point... using "With Activesheet" should be good enough.

--
Rick (MVP - Excel)

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