1

On my spreadsheet I set up a series of (form control) option buttons, six per row, each row answers one question. Each row was supposed to be linked to the same cell, but I was having problems with the links changing arbitrarily.

I read that I should enclose each row of buttons in a Group Box, and I'm trying that. But since most of the buttons worked correctly before, I can't be sure I'm doing this right. The rows of buttons are close together; I could accidentally group two rows in the same box.

Is there a way to tell which buttons are in a given group?

Also, can you assign the group box to the linked cell, or do you have to assign the buttons individually?

Shawn V. Wilson
  • 1,002
  • 3
  • 17
  • 42

2 Answers2

1

I guess you use the form control radio buttons, because, with the activeX radio btutton, you simply go into design mode, rightclick the control, select properties and check if the groupnames of the buttons match.

Alex
  • 779
  • 7
  • 15
  • So should I replace them all with ActiveX buttons? – Shawn V. Wilson Jan 24 '17 at 23:35
  • I think this is a good link for further information -> http://stackoverflow.com/questions/15455179/what-is-the-difference-between-form-controls-and-activex-control-in-excel-20#15479852 – Alex Jan 24 '17 at 23:38
1
Sub Tester()

    Dim o As OptionButton, gb

    For Each o In ActiveSheet.OptionButtons

        gb = "no Group"
        If Not o.GroupBox Is Nothing Then gb = o.GroupBox.Name

        Debug.Print o.Name, "Location: " & o.ShapeRange(1).TopLeftCell.Address, _
        "Group:" & gb, _
        "Link: " & ActiveSheet.Shapes(o.Name).OLEFormat.Object.LinkedCell

    Next o
End Sub

Useful: http://wellsr.com/vba/2016/excel/complete-guide-to-excel-vba-form-control-option-button/

Tim Williams
  • 154,628
  • 8
  • 97
  • 125
  • That's very useful, thanks. But it confirms my real problem: When I draw a group box around ungrouped buttons, they don't actually become part of the group. Is there a way to do it progammatically? – Shawn V. Wilson Jan 25 '17 at 17:08
  • I think Tango_Mike's answer here is what you're looking for: http://stackoverflow.com/questions/30545772/how-to-add-option-buttons-to-group-in-excel-2010-sheet-using-vba – Tim Williams Jan 25 '17 at 17:29
  • I actually had looked at that, and it does show how to add a new box to existing buttons. But is there a way to move existing buttons, or new buttons, to an existing box? – Shawn V. Wilson Jan 25 '17 at 19:21
  • If you have an existing box then I don't think you can just move the buttons into it, or the box over the buttons: you need to create the box over the existing buttons. So, if you have an existing box with no associated buttons, all you can do is delete it, then recreate it *around* the buttons you want to group together. You should be able to do that in code. – Tim Williams Jan 25 '17 at 19:53