3

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.

Chrismas007
  • 6,085
  • 4
  • 24
  • 47
Rookz
  • 81
  • 4
  • Has anyone else had experience with this problem? I stiill can't figure out how to get the activeX buttons to work after having been hidden. – Rookz Aug 17 '15 at 15:27

3 Answers3

2

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.

Rookz
  • 81
  • 4
  • The behaviour you describe does not happen for me (with Excel 2013). Note that in the VBA window each option activex optin button object has a "GroupName" property. It is this which should be used to group the objects so that they toggle each other on and off. Are you using this, or have you selected the butons and added them to a "shape" group by choosing right click group? – HarveyFrench Aug 24 '15 at 14:54
  • Update June 2021. This behaviour is still present in Excel from Office 365 Enterprise as at June 2021. (Equivalent to Excel 2019 I believe). It is also true for the "Move but don't resize with Cells" option. The button ONLY continues to work after the rows are hidden / reshown if using the "Don't move or size with cells" option. – Warren K Jun 17 '21 at 03:18
0

I tried this:

I had two option button in row 1 on sheet1. The code below is from sheet1's module.

I used the procedure HideOrShowRow to hide or show the row.

Both option buttons had their right click>format control>properties>Object positioning property set to "Move and size with cells".
This is what allows the control to be hidden with the row - so I think you must have done this already.

Anyway, it worked ok for me. However, I suspect you have two option buttons for every question. A YES and a NO. These need to toggle each other value as my code does below.

Note that once a option button has a "tick" in it, you can't click it to untick it. Another option button must do that! (ie as my code below)

Not having this in your code may be something to do with your problem.

Also, each ADo control has an enabled property. if this is set to false the user will not be able to change their value or select the control. It sounds like they may have become false? But how!

Private Sub Q1_no_Click()

    MsgBox "hi"
    Me.Q1_No.Value = Not Me.Q1_yes.Value

End Sub


Private Sub Q1_yes_Click()

    Me.Q1_yes.Value = Not Me.Q1_No.Value

End Sub


Sub HideOrShowRow()

    Me.Range("A1").EntireRow.Hidden = Not Me.Range("A1").EntireRow.Hidden

End Sub
HarveyFrench
  • 4,440
  • 4
  • 20
  • 36
  • Thanks for the fast response! I was using activeX optionboxes (added using the "Insert" feature from the developer's tab). I used the actionX buttons over the form buttons. Once inserted, i right-clicked the two boxes and used the "Group" option to have them automatically toggle when the other box was activated. So if I select "Yes", no unselected -- without need of additional code. This functionality works perfectly until I hide the row with the optionboxes present. once unhidden, I can't select either option box anymore. Does this make sense? – Rookz Aug 14 '15 at 18:51
0

I had the same issue: ActiveX controls having a weird behavior after being hidden and unhidden. I found a way to solve this: just put a useless row above the one with your controls, and when unhidding your controls just hide and unhide the useless row. It will "force" your controls to have the right placement. This method doesn't require to know the coordinates of your control: very usefull if it's not a fixed control in your sheet (hidable rows are above, ...)

I recommend you to place this "debug" line at the top of your sheet, as it will affect every ActiveX control underneath it, so every control of the sheet.

Here's a link to an exemple file: https://docs.google.com/spreadsheets/d/1s1PMUA7VXtl0ubtYkgUx99wGl5q-JbcR/edit?usp=sharing&ouid=101340884497702807552&rtpof=true&sd=true

(I'm sorry if my english is not perfect)

Smurb
  • 1
  • 3