Option Button

segran

Active Member
Joined
Aug 20, 2004
Messages
335
Hi All

I would appreciate your assistance with option buttons. I have a series of questions, each of which, has three options - Low, Medium and High. The user must answer each question, by clicking an option. Thereafter, I would like to give a table, counting the total number of options selected.
Thank You
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

BrianB

Well-known Member
Joined
Feb 17, 2003
Messages
8,127
There are 3 sorts of Option Button, which one ... ?
Worksheet - Forms Toolbar
Worksheet - Controls Toolbar
User Form
 

segran

Active Member
Joined
Aug 20, 2004
Messages
335
option buttons

Hi
Its the one from the control toolbox.
Thank You
 

BrianB

Well-known Member
Joined
Feb 17, 2003
Messages
8,127
A bit fiddly. You need to have a sheet called "Option Results" and run from the sheet with the option buttons ......................
Code:
'========================================================
'- GET WORKSHEET OPTION BUTTON RESULTS
'========================================================
Option Base 1
Sub test()
    Dim ws As Worksheet
    Dim Results As Worksheet
    Dim ResultsRow As Long
    Dim objNum As Integer
    Dim obj As Object
    Dim objObject As Object
    '-----------------------------------------------------
    Set ws = ActiveSheet
    ws.Range("A1").Select
    Set Results = Worksheets("Option Results")
    Results.Range("A1:C1").Value _
            = Array("Button", "Group", "Selection")
    ResultsRow = 2
    '----------------------------------------------------
    '- run through all objects in sheet
    For objNum = 1 To ws.OLEObjects.Count
        Set obj = ws.OLEObjects(objNum)
        Set objObject = obj.Object
        '-------------------------------------------------
        '- Check OptionButton and selected option
        If TypeName(objObject) = "OptionButton" Then
            '- record checked buttons
            If objObject.Value = True Then
                Results.Cells(ResultsRow, 1).Value = obj.Name
                Results.Cells(ResultsRow, 2).Value = _
                                    objObject.GroupName
                Results.Cells(ResultsRow, 3).Value = _
                                    objObject.Caption
                ResultsRow = ResultsRow + 1
            End If
        End If
    Next
End Sub
'=============================================
 

segran

Active Member
Joined
Aug 20, 2004
Messages
335
Option Buttons

Hi

I have this list:

1 Poor infrastructure
2 Low productivity
3 Poor business skills
4 Competition
5 Substitutes products
6 Fluctuations in the global economy
7 Products developed internationally , are cheaper that those produced locally.
8 Mechanisation at competitors quarries
9 Product loses its niche market in the tapestry of commerce
10 Supply does not meet consumer demand
11 The quality of product deteriorates
12 Non-compliance to safety standards
13 Disturbance due to employee strikes and conflicts due to political tensions

Each of which has the three options, low, medium, high.
When I used the code you furnished me, the selected choices, say for question one, didnt remain, as i proceeded to select a choice for question 2...
The code only allows me to make one choice for all my list....

Please help....
Thank You
 

BrianB

Well-known Member
Joined
Feb 17, 2003
Messages
8,127
Looks like you have not got your option buttons set up correctly. Unless you separate them into groups (of 3 in your case) they all act as one big set, so you can only select one of them at a time. The code runs through all the buttons to see which ones are checked. You should have one checked for each set.

1. Run the Controls Toolbar.

2. Select each set of 3 option buttons in turn by holding down Shift and clicking (so you don't have to change each button individually)

3. Click the Properties button in the toolbar and change the GroupName property of the set. If you use the (group) names you have devised these names will appear in the results sheet - together with the caption (the visible title of the button on the worksheet) of the clicked option button.

By the way, a word of warning, don't use too many controls from the Controls toolbox in a worksheet. They are bug ridden and can result in workbook corruption. It doesn't always happen immediately. Make sure you keep backups.

The ones from the Forms toolbar are much more reliable. Less features, but less problems.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,684
Messages
5,597,530
Members
414,152
Latest member
ReservoirDodds

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