I want to take a poll with 4 answer options displayed as radio buttons. I
want to tally the number of each option chosen for a specific question and I
also want one of the options to be limited to the number of times it can be
chosen . i.e. Options are Definately, Sounds Good, Maybe and No Way there are
50 rows of activities only 5 may be answered as Definately When all user
answer I want to paste answers into one spreadsheet then look speciically at
activity X and count how many answered Definately, Sounds Good, Maybe or No
Way.

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.

klintonselkirk wrote:

I want to take a poll with 4 answer options displayed as radio buttons. I
want to tally the number of each option chosen for a specific question and I
also want one of the options to be limited to the number of times it can be
chosen . i.e. Options are Definately, Sounds Good, Maybe and No Way there are
50 rows of activities only 5 may be answered as Definately When all user
answer I want to paste answers into one spreadsheet then look speciically at
activity X and count how many answered Definately, Sounds Good, Maybe or No
Way.

--

Dave Peterson