Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheets visible toggle code
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheets visible toggle code
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 Sub -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=164975 Microsoft Office Help |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheets visible toggle code
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 -- Rick (MVP - Excel) "Ambassador" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheets visible toggle code
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/sh...d.php?t=164975 Microsoft Office Help |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheets visible toggle code
Good point... using "With Activesheet" should be good enough.
-- Rick (MVP - Excel) "joel" wrote in message ... 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/sh...d.php?t=164975 Microsoft Office Help |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheets visible toggle code
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 Rothstein wrote: Good point... using "With Activesheet" should be good enough. -- Rick (MVP - Excel) "joel" wrote in message ... 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/sh...d.php?t=164975 Microsoft Office Help -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Code to make only certain sheets visible | Excel Programming | |||
Code to make sheets in a workbook visible | Excel Programming | |||
Toggle text to be visible and not visible | Excel Programming | |||
Toggle Text to be visible and Not visible | Excel Programming | |||
Toggle Text in a column to be visible or not visible | Excel Programming |