I have three Toggle Buttons on a worksheet, each of which shows/hides a different combination of rows.<o></o>
I press one button to hide a range of rows and then need to press it again to reshow all rows.<o></o>
The problem I have is that when I have got one range of rows hidden, I forget to reset the original button before pressing another button, and end up with a less than useful set of rows showing.<o></o>
I have got round this to some extent, by inserting a 4<sup>th</sup> “reset” button, which, at least re-shows all the rows whichever state the sheet is in.<o></o>
Is there a way of doing this (e.g. different type of button?) so that I can show different ranges of rows, without having to show all rows between each state?<o></o>
<o> </o>
<o> </o>
<o></o>
I press one button to hide a range of rows and then need to press it again to reshow all rows.<o></o>
The problem I have is that when I have got one range of rows hidden, I forget to reset the original button before pressing another button, and end up with a less than useful set of rows showing.<o></o>
I have got round this to some extent, by inserting a 4<sup>th</sup> “reset” button, which, at least re-shows all the rows whichever state the sheet is in.<o></o>
Is there a way of doing this (e.g. different type of button?) so that I can show different ranges of rows, without having to show all rows between each state?<o></o>
<o> </o>
<o> </o>
Code:
Private Sub ToggleButton1_Click()
' this button shows/hides hours
Dim myRange1 As Range, myRange2 As Range, myRange3 As Range, myRange As Range
Set myRange1 = Range("7:9,11:11,13:15,17:17,19:21,23:23,25:27,29:29,31:33,35:35,37:39,41:41,43:45,47:47,49:51,53:53,55:57,59:59,61:63,65:65,68:69,74:74")
Set myRange2 = Range("81:83,85:85,87:89,91:91,93:95,97:97,99:101,103:103,105:107,109:109,111:113,115:115,117:119,121:121,123:125,127:127,129:131,133:133,135:137,139:139,142:143,148:148")
Set myRange3 = Range("155:157,159:159,161:163,165:165,167:169,171:171,173:175,177:177,179:181,183:183,185:187,189:189,191:193,195:195,197:199,201:201,203:205,207:207,209:211,213:213,216:218")
Set myRange = Union(myRange1, myRange2, myRange3)
myRange.Rows.Hidden = Not myRange.Rows.Hidden
End Sub '--
Private Sub ToggleButton2_Click()
' this button shows/hides rooms
Dim myRange1 As Range, myRange2 As Range, myRange3 As Range, myRange As Range
Set myRange1 = Range("7:10,13:16,19:22,25:28,31:34,37:40,43:46,49:52,55:58,61:64,68:70,74:74")
Set myRange2 = Range("81:84,87:90,93:96,99:102,105:108,111:114,117:120,123:126,129:132,135:138,142:144,148:148")
Set myRange3 = Range("155:158,161:164,167:170,173:176,179:182,185:188,191:194,197:200,203:206,209:212,216:216")
Set myRange = Union(myRange1, myRange2, myRange3)
myRange.Rows.Hidden = Not myRange.Rows.Hidden
End Sub '—
Private Sub ToggleButton3_Click()
' this button shows/hides staff
Dim myRange1 As Range, myRange2 As Range, myRange3 As Range, myRange As Range
Set myRange1 = Range("7:8,10:11,13:14,16:17,19:20,22:23,25:26,28:29,31:32,34:35,37:38,40:41,43:44,46:47,49:50,52:53,55:56,58:59,61:62,64:65,68:69,74:74")
Set myRange2 = Range("81:82,84:85,87:88,90:91,93:94,96:97,99:100,102:103,105:106,108:109,111:112,114:115,117:118,120:121,123:124,126:127,129:130,132:133,135:136,138:139,142:143,148:148")
Set myRange3 = Range("155:156,158:159,161:162,164:165,167:168,170:171,173:174,176:177,179:180,182:183,185:186,188:189,191:192,194:195,197:198,200:201,203:204,206:207,209:210,212:213,216:218")
Set myRange = Union(myRange1, myRange2, myRange3)
myRange.Rows.Hidden = Not myRange.Rows.Hidden
End Sub '--
Private Sub ToggleButton4_Click()
'this button re-shows/resets all rows in either state
Range("7:216").Rows.Hidden = Not Range("7:216").Rows.Show
End Sub