First, I think you have a couple of questions in here.
I would break it into pieces. One getting the response from individual users.
And the second would be to get all the responses into one place.
I'd set up a workbook with 4 optionbuttons from the forms toolbar inside a
groupbox--and have 50 of these.
I'd assign a linked cell for the first option button in each groupbox.
I'd put the question number in A2:A51
I'd use B2:B51 for the linked cells.
I'd use column C2:C51 for the name of the user (necessary when you combine them
all).
(actually, I'd use C1 to hold the name of the user. Then use a formula that
"copied" that value to C2:C51.
Then I'd put the optionbuttons in columns D2:G51 (each surrounded by a
groupbox).
And I'd put the questions in I2:I51
And I'd have to have a macro assigned to the first optionbutton in each group to
check that limit of 5 definitely's. (Actually, I'd assign the macro to all the
optionbuttons--just in case I wanted to test other buttons, too.)
Then after the user types in his/her name into C1 and completes the survey, I'd
do the second part.
Copy columns A:C (of each returned survey) and paste special|Values to a new
worksheet in a different (Summary) workbook.
After the data is in one spot, you can use whatever techniques you want to
analyze that data (data|sort, data|subtotals, data|pivottables, charts and
graphs???).
If this sounds promising, here's some code that you can use to layout that
worksheet.
Make sure you either start with a new worksheet or with your questions in
I2:I51.
The first macro should be run just once (discounting testing). The second macro
is the one that does the checking.
Both go into a General module.
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
maxBtns = 4
Set wks = ActiveSheet
With wks
.Range("a:g").ClearContents
With .Range("d1:G1")
.Value = Array("Definately", "Sounds Good", "Maybe", "No Way")
.Orientation = 90
.HorizontalAlignment = xlCenter
End With
Set myRange = .Range("d2:d51")
With myRange.Offset(0, -3) 'column A
.Formula = "=row()-" & myRange.Row - 1
.Value = .Value
End With
With myRange.Offset(0, -1) 'column C
.FormulaR1C1 = "=r1c"
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
Sub CheckOpt()
Dim myLinkedCells As Range
Dim iCtr As Long
Dim myCount As Long
Dim myOptBtn As OptionButton
Dim myVal As Long
With ActiveSheet
Set myOptBtn = .OptionButtons(Application.Caller)
Set myLinkedCells = .Range("b2:b51")
myVal = .Range(myOptBtn.LinkedCell).Value
If myVal = 1 Then
myCount = Application.CountIf(myLinkedCells, myVal)
If myCount > 5 Then
MsgBox "Cannot use this option again"
myOptBtn.Value = xlOff
End If
End If
End With
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
==
In fact, try running this against a blank worksheet. Then see if works for
you. If it does, then you could add your descriptions later.
