Form Option buttons within a Group

[Drdave99]

Hi - I have a questionnaire with two text statements on a row with two Option Boxes on a row, only one of which can be chosen because they represent a preference. The whole row is enclosed in a Group and the other 39 questions are treated the same way.
I would like to know how the option boxes can be linked to an adjoining cell showing TRUE/FALSE depending on which button is activated.
Then I would be able to use =COUNTIF(range,”TRUE”) to analyse the responses into categories.

STATEMENT [] True. STATEMENT [] False

Hope someone could put me on the right track after the Option boxes have been installed!

 

[John_w]

I would like to know how the option boxes can be linked to an adjoining cell showing TRUE/FALSE depending on which button is activated.

For one of the option buttons in a group, set the Cell link (context menu -> Format Control...) to a cell on the same row. The same linked cell is used automatically for the other option button in the group. Do the same for the other groups. The linked cell displays 1 for the first option button or 2 for the second option button in a group, so =COUNTIF(range,"=1") gives the count of True responses.

 

[Drdave99]

For one of the option buttons in a group, set the Cell link (context menu -> Format Control...) to a cell on the same row. The same linked cell is used automatically for the other option button in the group. Do the same for the other groups. The linked cell displays 1 for the first option button or 2 for the second option button in a group, so =COUNTIF(range,"=1") gives the count of True responses.

Hi - thank you for such good advice. If I were to set up a cell in the right column first, would that display 1 and the left button 2 and vice versa?
I ask this because my personality questionnaire has, for instance some Extravert statements (Like parties) on the right and some on the left - otherwise people suspect the answers and tick all the boxes on one side! Hope photo might explain .
Best wishes David

 

[Drdave99]

For one of the option buttons in a group, set the Cell link (context menu -> Format Control...) to a cell on the same row. The same linked cell is used automatically for the other option button in the group. Do the same for the other groups. The linked cell displays 1 for the first option button or 2 for the second option button in a group, so =COUNTIF(range,"=1") gives the count of True responses.

Hi John - thank you for your very helpful reply. I enclose photo to ask whether if I programmed the right hand button first would it display1 and the left button display 2.
I ask this because my personality questionnaire measures several dimensions such as Extraversion/Introversion and I don’t want all the Extraversion statements to be on one side otherwise participants may recognise a pattern!
Hope this makes sense
Best wishes David
PS image too large to send - is there another way?
Q1 ACTIVE [ ]. or REFLECTIVE [ ]. (Extraversion on left)
Q4 RESERVED [ ]. or. TALKATIVE. [ ] (Extraversion on right)
Then, for analysis it would be =COUNTIF( cell containing left button associated with ACTIVE : cell containing right button associated with TALKATIVE,”=1”)

 

[Drdave99]

Hi John - thank you for your very helpful reply. I enclose photo to ask whether if I programmed the right hand button first would it display1 and the left button display 2.
I ask this because my personality questionnaire measures several dimensions such as Extraversion/Introversion and I don’t want all the Extraversion statements to be on one side otherwise participants may recognise a pattern!
Hope this makes sense
Best wishes David
PS image too large to send - is there another way?
Q1 ACTIVE [ ]. or REFLECTIVE [ ]. (Extraversion on left)
Q4 RESERVED [ ]. or. TALKATIVE. [ ] (Extraversion on right)
Then, for analysis it would be =COUNTIF( cell containing left button associated with ACTIVE : cell containing right button associated with TALKATIVE,”=1”)

 

[John_w]

If I were to set up a cell in the right column first, would that display 1 and the left button 2 and vice versa?

Yes. The option button you insert first inside the group box displays 1 in the linked cell and the option button you insert second displays 2 in the linked cell. Therefore ensure that all the option buttons for the 'True' analysis are inserted first, like this:

Worksheet form option buttons group counts.xlsx
	A	B
6	Count of option buttons inserted first (analysis = TRUE)	4
7	Count of option buttons inserted second (analysis = FALSE)	0
Sheet3

Cell Formulas
Range	Formula
B6	B6	=COUNTIF($B$2:$B$5,1)
B7	B7	=COUNTIF($B$2:$B$5,2)

 

[Drdave99]

Yes. The option button you insert first inside the group box displays 1 in the linked cell and the option button you insert second displays 2 in the linked cell. Therefore ensure that all the option buttons for the 'True' analysis are inserted first, like this:

View attachment 106521

Worksheet form option buttons group counts.xlsx
	A	B
6	Count of option buttons inserted first (analysis = TRUE)	4
7	Count of option buttons inserted second (analysis = FALSE)	0
Sheet3

Cell Formulas
Range	Formula
B6	B6	=COUNTIF($B$2:$B$5,1)
B7	B7	=COUNTIF($B$2:$B$5,2)

Thank you very much again for your advice. I have been looking for that solution for several weeks for either buttons or checkboxes, so now will press on with the 40 items!

 

[John_w]

so now will press on with the 40 items!

You could add the 40 questions, associated group boxes and option buttons programmatically with the following VBA macro.

First add the following cell values to a blank sheet:

Worksheet form option buttons group counts.xlsm
	A	B	C	D	E	F
1			Linked cell value	True option button text	False option button text	True option button number
2				Active	Reflective	1
3				Sympathetic	Analytic	2
4				Thinking	Feeling	1
5				Talkative	Reserved	2
Sheet4

In the VBA editor, add a new module and paste in the following code:

Public Sub Add_Questions()

    Dim ws As Worksheet
    Dim questionCells As Range
    Dim cell As Range
    Dim gb As GroupBox
    Dim ob As OptionButton
    Dim obCell As Range
    Dim obNum As Long
    
    Set ws = ActiveSheet
    
    With ws
    
        Set questionCells = .Range("A2:A5")
        
        .Columns("A").ColumnWidth = 40
        .Columns("B").ColumnWidth = 40
        
        For Each cell In questionCells
        
            cell.EntireRow.RowHeight = 60
            
            '                        left         , top         , width                                , height
            Set gb = .GroupBoxes.Add(cell.Left + 4, cell.Top + 4, cell.Offset(, 2).Left - cell.Left - 8, cell.Height - 8)
            gb.Text = ""
            
            obNum = cell.Offset(, 5).Value
            If obNum = 1 Then
                'Put first option button in column A cell with text from column D
                Set obCell = cell.Offset(, 0)
                Set ob = .OptionButtons.Add(obCell.Left + 8, obCell.Top + 8, obCell.Width - 16, obCell.Height - 16)
                ob.Text = cell.Offset(, 3).Value
                'Put second option button in column B cell with text fro column E
                Set obCell = cell.Offset(, 1)
                Set ob = .OptionButtons.Add(obCell.Left + 8, obCell.Top + 8, obCell.Width - 16, obCell.Height - 16)
                ob.Text = cell.Offset(, 4).Value
                'Set linked cell to column C cell
                ob.Value = xlOff
                ob.LinkedCell = cell.Offset(, 2).Address
            Else
                'Put first option button in column B cell with text from column D
                Set obCell = cell.Offset(, 1)
                Set ob = .OptionButtons.Add(obCell.Left + 8, obCell.Top + 8, obCell.Width - 16, obCell.Height - 16)
                ob.Text = cell.Offset(, 3).Value
                'Put second option button in column A cell with text from column E
                Set obCell = cell.Offset(, 0)
                Set ob = .OptionButtons.Add(obCell.Left + 8, obCell.Top + 8, obCell.Width - 16, obCell.Height - 16)
                ob.Text = cell.Offset(, 4).Value
                'Set linked cell to column C cell
                ob.Value = xlOff
                ob.LinkedCell = cell.Offset(, 2).Address
            End If
            
        Next
    
    End With
    
End Sub

After running the macro the sheet should look like this:

with the linked cells in column C ready for you to apply the COUNTIF formula to them. You can delete columns D:F.