Toggle Button multiple row ranges

[BrisbaneOz]

Hi all

Great to be here in this forum.

Just a question , i have this VBA for toggle hiding/showing a range of rows as below (rows 268 to 279) which works no problem

Private Sub ToggleButton22_Click()
Dim xAddress As String
xAddress = "268:279"

If ToggleButton22.Value Then
Application.ActiveSheet.Rows(xAddress).Hidden = True
ToggleButton22.Caption = "S"
Else
Application.ActiveSheet.Rows(xAddress).Hidden = False
ToggleButton22.Caption = "H"

End If

End Sub


My question would be how would i get this to have multiple ranges for example as above for
rows 268:279 AND rows 68:79 AND 92:103 all on the same toggle?


Thanks in advance
Steve

[BrisbaneOz]

please ignore , i worked it out , i should have checked before posting

Private Sub ToggleButton22_Click()
Dim xAddress As String
xAddress = "268:279"
xxAddress = "68:79"

If ToggleButton22.Value Then
Application.ActiveSheet.Rows(xAddress).Hidden = True
Application.ActiveSheet.Rows(xxAddress).Hidden = True
ToggleButton22.Caption = "S"
Else
Application.ActiveSheet.Rows(xAddress).Hidden = False
Application.ActiveSheet.Rows(xxAddress).Hidden = False


ToggleButton22.Caption = "H"

End If

End Sub

[Rick Rothstein]

Assuming your initial setting for the button's caption is correct for the hidden state for those rows, this should work...

Please Login or Register  to view this content.

[BrisbaneOz]

Awesome Rick , tested over 5 ranges works like a charm

I have 26 ranges to add , should be ok?

Forgive me for being a novice, i want to make the captions read using your awesome code posted above, can't seem to make it change?
Hide Term 1 (where my original post was "H")
Show Term 1 (where my original post was "S")

Many thanks
Steve

[Rick Rothstein]

I have 26 ranges to add , should be ok?

Maybe... it depends on what the actual rows are. The Range object has a 255 or 256 (can't remember which) limit to the number of characters that can be in its text argument, so if all your rows are less than 1000, it will work fine... if they were all 4-digits long, it would fail... it some but not all are 4-digits long with the rest being 3-digits or less, then it more than likely will work. If your argument is too long, you could alway break the argument into two distinct sets of ranges and use two Range object calls for them.

Forgive me for being a novice, i want to make the captions read using your awesome code posted above, can't seem to make it change?
Hide Term 1 (where my original post was "H")
Show Term 1 (where my original post was "S")

If the problem is that you are displaying H's when you want S's and vice-versa, simply change the "HS" in the Mid function call to "SH" and that should straighten the problem out.

[BrisbaneOz]

Thanks Rick , will check on the limits

Will adopt Caption suggestion , thanks for all your help!