I have scoured the internet and can't seem to find others having this same problem.
I have a spreadsheet with multiple grouped ActiveX option boxes. The user is to go down the spreadsheet and select options from the boxes (usually yes no). The code simply hides rows that the user shouldn't have to answer based on their previous choices. Example code:
Private Sub OptionButton3_Click()
'High School Diploma YES Option
Application.ScreenUpdating = False
Sheets("Sheet1").Rows("18:38").Hidden = True
Application.ScreenUpdating = True
End Sub
Private Sub OptionButton4_Click()
'High School Diploma NO Option
Application.ScreenUpdating = False
Sheets("Sheet1").Rows("18:38").Hidden = False
Application.ScreenUpdating = True
End Sub
My problem is, whenever I unhide one of these rows (for example, a user changes a "Yes" to a "No") the activeX optionboxes that were previously hidden are now unclickable, and subsequently unuseable. Anyone have any ideas what's causing this and how to fix it?
I even tried independently hiding the control boxes before hiding the rows, but I got the same result
(ActiveSheet.Shapes.Range(Array("Group 1", "Group 2", "Group 3")).Visible = False)
Pretty new to controls, so not sure if I'm making an obvious mistake here. any help is greatly appreciated.
So, I've been playing with this for a few days and this is what I've learned:
If an activeX button (optionbutton, checkbox, etc.) is set to "Move and Size with cells", it is broken whenever the cells it resides on are hidden. The button is no longer able to be clicked (or right clicked) and is unable to be repaired in any way.
I've tried multiple methods for hiding the rows (physically selecting the rows the right-clicking and hiding, using VBA commands "EntireRow.Hidden = True" and using vba command "hiding the rows using VBA RowHeigt =0".) In all instances, once the row is unhidden and the button visible again, it is no longer clickable.
A work-around that i found is to change the settings of the activeX button to "don't move or size with cells". Then, when hiding a row with VBA code, I add additional code to hide the button
ActiveSheet.OLEObjects("OptionButton1").Visible = False
This is not ideal, but it seems to at least give the appearance of the buttons hiding with the rest of the content of the rows.