Show/hide different combinations of rows

[barkie]

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^th “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>

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

<o></o>

 

[jasonb75]

Edited out, just realised the code modification would hide 2 of the 3 ranges every time.

 

[jasonb75]

Ok, now I'm back with the sensible answer that should work, the obvious one that I overlooked to start off.

Enter this as the first line of code in each of the 3 button code modules, this will run your reset before hiding the rows selected by the button being clicked.

Call ToggleButton4_Click

Hope this helps

JB

 

[GTO]

Greetings barkie,

If I am catching what you want corretly, you want to be able to display/hide different combos of rows - but - would like not to have two combos accidently hidden at once. If I got that part correct, have you tried option buttons?

Mark

 

[barkie]

Thanks JB
That does the trick
I'm a bit new to vba so hadn't come across this before - but I'm learning fast!
Thanks once again