Home |
Search |
Today's Posts |
#1
|
|||
|
|||
I need a form with 5 buttons that correspond with values 1-5
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. |
#2
|
|||
|
|||
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 "Steven J Reddy" <Steven J skrev i melding ... 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. |
#3
|
|||
|
|||
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 Steven J Reddy wrote: 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. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating a Flexible Proposal Form | Excel Discussion (Misc queries) | |||
Display form from an VB application in Excel | Excel Discussion (Misc queries) | |||
Worksheet form design | Excel Discussion (Misc queries) | |||
Add buttons in a form from a datasource | Excel Discussion (Misc queries) | |||
Trying to delete form border, and it deletes all borders | Excel Discussion (Misc queries) |