Toggle button

  • Thread starter Thread starter Brandon
  • Start date Start date
B

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
 
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.
 
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
 
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
 
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
 
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
 
Dan E said:
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
Click to expand...

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
 
Back
Top