ActiveX Option Buttons No Longer Function After Hide Then Unhide Rows

[TheArtMan]

I have a spreadsheet with ActiveX option buttons in groups. They are ActiveX buttons, not Form Controls buttons. Those buttons are in rows 7:30. I have a command button (also ActiveX) with the caption "Hide" that will hide rows 7:30 and then change the caption to "Show." When I click on that command button again, it unhides rows 7:30 and changes the caption to "Hide." After hiding/unhiding those rows, the option buttons no longer work. When I click on one, a "ghost" option button appears below the selected option button on mouse-down, and then the ghost option button disappears on mouse-up.

Command button code (pretty basic):

Option Explicit

Private Sub CommandButton1_Click()


If CommandButton1.Caption = "Hide" Then
Rows("2:6").EntireRow.Hidden = True
CommandButton1.Caption = "Show"
Else
Rows("2:6").EntireRow.Hidden = False
CommandButton1.Caption = "Hide"
End If


End Sub




In order to get the option buttons to work again, I must close and reopen the workbook.

I created a second test spreadsheet (attached) to see if this behavior was unique, and the second test spreadsheet displays the same behavior.

If anyone knows what may be causing this and, more importantly, how to keep it from happening, that would be wonderful!

[snb]

Why hiding/unhiding rows ?

Better coding:

Private Sub CommandButton1_Click()
with CommandButton1
.Caption = iif(.caption="Hide","Show","Hide")
Rows("2:6").Hidden = .caption="Show"
end with
End Sub

[TheArtMan]

That is a more elegant form - thanks!

In the sheet where I discovered the problem, there are 45 sets of option buttons being used as filter options for a list. I display results at the bottom of the screen. Those 45 sets of options take up a lot of real estate, so once the user applies the filters selected I hide that real estate so that more results appear on screen, making it easier to view those results in a meaningful way.

[snb]

Use an 'intelligent' table.
45 sets op optionbuttons is ridiculous.

[SamT]

Alternative:
Add all Filter options to a UserForm

Use Ribbon Menu

Sub MenuItem_Click
'Not for real
UserFormFilters.Show
End Sub

Use Command Button "ShowFilters"

Sub cbutShowFilters_Click()
UserFormFilters.Show
End Sub


Use Mouse

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
UserFormFilters.Show
End Sub

[TheArtMan]

I did end up using a UserForm, since this is apparently a defect in Excel. Thanks again for the suggestions.