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