Option Button

[segran]

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

 

[BrianB]

There are 3 sorts of Option Button, which one ... ?
Worksheet - Forms Toolbar
Worksheet - Controls Toolbar
User Form

 

[segran]

option buttons

Hi
Its the one from the control toolbox.
Thank You

 

[BrianB]

A bit fiddly. You need to have a sheet called "Option Results" and run from the sheet with the option buttons ......................

'========================================================
'- 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]

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]

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.