Grouping Option Buttons

DavidWe

New Member
Joined
May 15, 2006
Messages
24
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
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
If you use the OptionButtons from the Control ToolBox you can use their GroupName property to group them.
 

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
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.
 

DavidWe

New Member
Joined
May 15, 2006
Messages
24

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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

New Member
Joined
May 15, 2006
Messages
24

ADVERTISEMENT

[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

New Member
Joined
May 15, 2006
Messages
24
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

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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

Code:
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
 

Forum statistics

Threads
1,136,926
Messages
5,678,610
Members
419,776
Latest member
mikelowski

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top