Grouping Option Buttons

[DavidWe]

I would like to have 12 option buttons grouped together in each row.
The option buttons for each row should be independent of the ones in
the other rows.

I added option buttons from the forms toolbar for the first row and
drew a group box around them. I did the same for the second row. After
selecting a box on the first row, I then select a box on the second
row. Selecting a box on the second row deselects the box on the first
row. Both rows are being treated as one group. I need them to be
independent. The user should be able to select one option for each row.

I've tried setting the cell link to the first cell for each row under
Format Control. But setting the cell link to the first cell in row 2
also changes the cell link to the same value in row 1. In other words,
I'm not able to have A1 as the cell link for row 1 and A2 as the cell
link for row 2 at the same time. It's always A1 for both rows or A2 for
both rows.

What am I doing wrong? Should I be using the forms toolbar or the
control toolbox? It seems that it should be very straightforward.

Thanks

 

[Andrew Poulsom]

If you use the OptionButtons from the Control ToolBox you can use their GroupName property to group them.

 

[NBVC]

Make sure the option buttons are completely enveloped by the group boxes.

Play with the group boxes size to ensure this and it should work out....

Are your rows of groups very close together? You may have to separate them a bit to ensure no overlap.

 

[erik.van.geit]

Hi,

if you want to fill a large range with optionbuttons you can use some code
there enough options to get started
(how many column, rows, rows between groups)

kind regards,
Erik

 

[DavidWe]

Thanks for all the responses and sorry for the double post.

I'm still playing with it. When I use the optionbuttons from the Control Toolbox, I am unable to select the buttons. It is almost like the spreadsheet is in design mode. Do I need to activate the buttons?

I still have not decided between the Control Toolbox or the Forms Toolbar.

 

[erik.van.geit]

Thanks for all the responses and sorry for the double post.

I'm still playing with it. When I use the optionbuttons from the Control Toolbox, I am unable to select the buttons. It is almost like the spreadsheet is in design mode. Do I need to activate the buttons?

I still have not decided between the Control Toolbox or the Forms Toolbar.

use the icon at the topleft of the controls-toolbar to toggle on and off design mode
(did you try the code ?)

 

[DavidWe]

[quote(did you try the code ?)[/quote]

No, I haven't tried the code yet. I will try it once I get a few rows to work. I will have some large spreadsheets, so I can definitely use the code.

 

[DavidWe]

Erik,

Is your code using option buttons from the Control Toolbox or the Forms Toolbar? I am not sure if it matters.

 

[erik.van.geit]

forms toolbar

 

[erik.van.geit]

I was tempted to create a similar code then you will find in the link.
all selected cells will be turned into optionbuttons
the next column will be used as linked cells
this is not a "finished" project, but can give you the idea
many improvements to do
prepare your columnwidths in advance
anyway some experiments required

say you have a sheet with
  A         B         C         
1 answer A1 answer B1 answer C1 
2 answer A2 answer B2 answer C2 
3 answer A3 answer B3 answer C3 
4 answer A4 answer B4 answer C4 
5 answer A5 answer B5 answer C5 
6 answer A6 answer B6 answer C6 

Blad7
[Table-It] version 06 by Erik Van Geit

Option Explicit

Sub replace_cellvalues_by_optionbuttons()
'Erik Van Geit
'060919

'this code is mainly meant to put together a questionary
'selection must be a rectangle
'keep in mind there are other techniques for questionaries

Dim rng As Range
Dim nextColRng As Range
Dim rownr As Long
Dim colnr As Integer
Dim BtnCaption As String
Dim FR As Long, NR As Long, FC As Integer, NC As Integer

On Error Resume Next
Set rng = Application.InputBox("select the range where you want to put optionbuttons", "SELECT RANGE", Selection.Address, Type:=8)
On Error GoTo 0
If rng Is Nothing Then Exit Sub

    With rng
    FR = .Cells(1, 1).Row
    FC = .Cells(1, 1).Column
    NR = .Rows.Count
    NC = .Columns.Count
    Set nextColRng = .Cells(1, 1).Offset(0, FC + NC - 1).Resize(NR, 1)
        If Application.CountA(nextColRng) > 0 Then
        MsgBox "The range " & nextColRng.Address(0, 0) & _
        " will be used for linked cells but is not empty." & vbLf & _
        "Please decide how to solve this. Then run code again.", 48, "ERROR"
        Exit Sub
        End If
    End With
    
ReDim txt(NC) As String

Application.ScreenUpdating = False

Rows(FR & ":" & FR + NR - 1).RowHeight = 18

For rownr = FR To FR + NR - 1
    For colnr = FC To FC + NC - 1
    
        With Cells(rownr, colnr)
        
        If colnr = FC Then ActiveSheet.GroupBoxes.Add(.Left, .Top, .Width * NC, .Height).Text = ""

        BtnCaption = .Value
            With ActiveSheet.OptionButtons.Add(.Left, .Top, .Width, .Height)
            .Characters.Text = BtnCaption
            .LinkedCell = Cells(rownr, FC + NC).Address
            End With

        End With
    Next colnr
Next rownr

'rng.ClearContents

Application.ScreenUpdating = False

End Sub

kind regards,
Erik