Toggle button

[Brandon]

I have 2 macros, one that hides a bunch of columns and one that unhides
those columns and it looks like this


---------------------------------
Sub HideRow()
'
' Hide Macro
' Macro recorded 4/3/03 by Brandon
'

'
Range("2:2,3:3,4:4,6:6,7:7,8:8,9:9,11:11,12:12").Select
Selection.EntireRow.Hidden = True
Range("A1").Select
End Sub
-----------------------a line here seperating them-------
Sub UnHideRow()
'
' unhide Macro
' Macro recorded 4/3/03 by Brandon
'

'
Range("2:2,3:3,4:4,6:6,7:7,8:8,9:9,11:11,12:12").Select
Selection.EntireRow.Hidden = False
Range("A1").Select
End Sub

----------------------------
i would like a way to do this with one toggle button. can someone help me
turn this to a toggle, or is there a better way to do this all together? i
dont like using groups because of the lines at the top..

Brandon

 

[Vasant Nanavati]

Hi Brandon:

First of all, you don't need to select the range that you want to
hide/unhide. Now that that's out of the way:

Private Sub ToggleHidden()
Range("2:2,3:3,4:4,6:6,7:7,8:8,9:9,11:11,12:12").EntireRow.Hidden _
= Not Range("2:2,3:3,4:4,6:6,7:7,8:8,9:9,11:11,12:12").EntireRow.Hidden
End Sub

Regards,

Vasant.

 

[Brandon]

ok, i created a toggle button and i went to the code and coppied and pasted
what you got here but nothing happens..
now what am i doing wrong?

thanks for the quick reply...

brandon

 

[Dan E]

Brandon,

Private Sub ToggleButton1_Click()
If ToggleButton1.Value = True Then
Range("2:2,3:3,4:4,6:6,7:7,8:8,9:9,11:11,12:12").EntireRow.Hidden =
True
Else
Range("2:2,3:3,4:4,6:6,7:7,8:8,9:9,11:11,12:12").EntireRow.Hidden =
False
End If
End Sub

watch out for word wrap!!!
should be like:

Private
If
Range
Else
Range
End If
End Sub

Dan E

 

[Bob Phillips]

Brandon,

This applies to a control toolbox button named ToggleButton1 (just in case
<g>).

Also, never write a statement like that twice, use with

Private Sub ToggleButton1_Click()
With Range("2:2,3:3,4:4,6:6,7:7,8:8,9:9,11:11,12:12")
If ToggleButton1.Value = True Then
.EntireRow.Hidden = True
Else
. EntireRow.Hidden = False
End If
End With
End Sub

easier to read, more maintainable

 

[Vasant Nanavati]

Works fine for me too, Gord! <g>

Regards,

Vasant.

Brandon

Vasant's code works fine for me.

Gord Dibben Excel MVP XL2002

 

[Dana DeLouis]

I also like the technique of listing the range only once. Less chance of a
typo.

Another option that I like might be...

With Range("2:4,6:9,11:12")
..etc

 

[Brandon]

Brandon,

Private Sub ToggleButton1_Click()
If ToggleButton1.Value = True Then
Range("2:2,3:3,4:4,6:6,7:7,8:8,9:9,11:11,12:12").EntireRow.Hidden =
True
Else
Range("2:2,3:3,4:4,6:6,7:7,8:8,9:9,11:11,12:12").EntireRow.Hidden =
False
End If
End Sub

watch out for word wrap!!!
should be like:

Private
If
Range
Else
Range
End If
End Sub

Dan E

I used dans version and it worked fine, i used it right after he
posted it before i saw any others, i also added some to hide colums as
well and this code here worked great on my xp machine w/ office xp but
i sent it to work where i am going to use it and i previously failed
to mention it is office '97 and this code wont work

Private Sub Hide_Click()
If Hide.Value = True Then
Range("2:4,6:9,11:14,16:19,21:24,26:28,30:30").EntireRow.Hidden
= True
Range("C:C,F:F,H:H,L:L,N:N,R:R,S:S").EntireColumn.Hidden =
True
Else
Range("2:4,6:9,11:14,16:19,21:24,26:28,30:30").EntireRow.Hidden
= False
Range("C:C,F:F,H:H,L:L,N:N,R:R,S:S").EntireColumn.Hidden =
False
End If
End Sub

and it gives me this error

Run-time error '1004':
Unable to set the Hidden property of the Range class


and it only gives me the error on the rows, the colums work fine...

a little more help please, can i fix this w/o first selecting it like
this

If Hide.Value = True Then
Range("2:4,6:9,11:14,16:19,21:24,26:28,30:30").Select
Selection.EntireRow.Hidden = True
Range("C:C,F:F,H:H,L:L,N:N,R:R,S:S").EntireColumn.Hidden = True
Hide.Caption = "Show"
Else
Range("2:4,6:9,11:14,16:19,21:24,26:28,30:30").Select
Selection.EntireRow.Hidden = False
Range("C:C,F:F,H:H,L:L,N:N,R:R,S:S").EntireColumn.Hidden = False
Hide.Caption = "Hide"
End If


this is one of our vb guys solution but he knows nothing about excel,
i dont want to have to select it first.. if this is possible please
let me know.. thanks for all the help on this... again it is excel '97

Brandon