I need a form with 5 buttons that correspond with values 1-5

[Guest]

I need to create a form with 20 questions. For each question I'd like the
user to check one of five buttons corresponding to the values 1-5. Then I'd
like the form to total the score from 20 (1x20) to 100 (5x20).

If possible I'd like to weight some questions heavier then others.

Thanks for your help.

(e-mail address removed)

 

[Harald Staff]

Hi

Have the form ask one question at the time. Then you need a form with a
question and 5 score buttons plus a "next" button and maybe even a
"previous" button, an invisible question storage area and somewhere to
collect and calculate the returned scores.

HTH. Best wishes Harald

 

[Dave Peterson]

Another option might be to make a worksheet look like that form.

Put 20 groupboxes from the Forms toolbar in (say) rows 2-21 (headers in rows
1??) in columns E:I. (Question number in column D and descriptions in column
J.)

Then put 5 optionbuttons in each groupbox and link an optionbutton from each
groupbox to column C of that each row. Put your weighting factor in column B of
each row and put a formula in column A of each row.

If question 3 is weighted 5, you could put 5 in B4 and =b4*c4 in A4.

If you want a macro that sets up a worksheet like that, you can try this against
a test worksheet:

Option Explicit
Sub SetupOneTime()

Dim grpBox As GroupBox
Dim optBtn As OptionButton
Dim maxBtns As Long
Dim myCell As Range
Dim myRange As Range
Dim wks As Worksheet
Dim iCtr As Long
Dim FirstOptBtnCell As Range
Dim NumberOfQuestions As Long
Dim myBorders As Variant

myBorders = Array(xlEdgeLeft, xlEdgeTop, xlEdgeBottom, _
xlEdgeRight, xlInsideVertical, xlInsideHorizontal)

maxBtns = 5
NumberOfQuestions = 20

Set wks = ActiveSheet
With wks
Set FirstOptBtnCell = .Range("e2")
.Range("a:i").Clear
With FirstOptBtnCell.Offset(-1, -1).Resize(1, maxBtns + 1)
.Value = Array("Question#", "Resp1", "Resp2", _
"Resp3", "Resp4", "Resp5")
.Orientation = 90
.HorizontalAlignment = xlCenter
End With

Set myRange = FirstOptBtnCell.Resize(NumberOfQuestions, 1)

With myRange.Offset(0, -1)
.Formula = "=row()-" & myRange.Row - 1
.Value = .Value
End With

myRange.Offset(0, -3).Value = 1

With myRange.Offset(0, -4)
.FormulaR1C1 = "=rc[1]*rc[2]"
End With

.Range("a1").Formula = "=sum(A2:A" & NumberOfQuestions + 1 & ")"


With myRange.Offset(0, -4).Resize(, 4)
For iCtr = LBound(myBorders) To UBound(myBorders)
With .Borders(myBorders(iCtr))
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Next iCtr
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With

myRange.EntireRow.RowHeight = 28
myRange.Resize(, maxBtns).EntireColumn.ColumnWidth = 4

'clean up existing junk
.GroupBoxes.Delete
.OptionButtons.Delete

End With

For Each myCell In myRange
With myCell.Resize(1, maxBtns)
Set grpBox = wks.GroupBoxes.Add _
(Top:=.Top, Left:=.Left, Height:=.Height, _
Width:=.Width)
With grpBox
.Caption = ""
.Visible = True 'False
End With
End With
For iCtr = 0 To maxBtns - 1
With myCell.Offset(0, iCtr)
Set optBtn = wks.OptionButtons.Add _
(Top:=.Top, Left:=.Left, Height:=.Height, _
Width:=.Width)
optBtn.Caption = ""
'optBtn.OnAction = ThisWorkbook.Name & "!CheckOpt"
If iCtr = 0 Then
With myCell.Offset(0, -2)
optBtn.LinkedCell = .Address(external:=True)
'.NumberFormat = ";;;"
End With
End If
End With
Next iCtr
Next myCell

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm