Using Excel 2000 I have a user who has created a spreadsheet using option
buttons across 4 columns, describing a rating system of 0 - 3. He has 24
rows of criteria being rated, using these option buttons. At the end of the
rows, he would like to total the number of option buttons used in each
column. Is there a function or other method to do this?

Thank you for any help.

--
LPS

Why use buttons? Just use the numbers 0 through 3 and count the number of
each of 0, 1, 2, and 3.

Dave
--
Brevity is the soul of wit.


"LPS" wrote:

Using Excel 2000 I have a user who has created a spreadsheet using option
buttons across 4 columns, describing a rating system of 0 - 3. He has 24
rows of criteria being rated, using these option buttons. At the end of the
rows, he would like to total the number of option buttons used in each
column. Is there a function or other method to do this?

Thank you for any help.

--
LPS

That would make sense but it is not my spreadsheet and the user likes the
look and ease of clicking an option button. Is it not possible to total the
number of option button selected in a column?
--
LPS


"Dave F" wrote:

Why use buttons? Just use the numbers 0 through 3 and count the number of
each of 0, 1, 2, and 3.

Dave
--
Brevity is the soul of wit.


"LPS" wrote:

Using Excel 2000 I have a user who has created a spreadsheet using option
buttons across 4 columns, describing a rating system of 0 - 3. He has 24
rows of criteria being rated, using these option buttons. At the end of the
rows, he would like to total the number of option buttons used in each
column. Is there a function or other method to do this?

Thank you for any help.

--
LPS

See if this is of any help

Dim i As Long
Dim ob As OptionButton
Dim col As Range
Dim aryColLeft
Dim aryCounts

ReDim aryColLeft(1 To 4)
ReDim aryCounts(1 To 4)
For Each col In Columns("B:E")
i = i + 1
aryColLeft(i) = col.Left
Next col

For Each ob In ActiveSheet.OptionButtons
If ob.Value = 1 Then
i = Application.Match(ob.TopLeftCell.Left, aryColLeft, 0)
aryCounts(i) = aryCounts(i) + 1
End If
Next ob

MsgBox aryCounts(1)
MsgBox aryCounts(2)
MsgBox aryCounts(3)
MsgBox aryCounts(4)


--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"LPS" wrote in message
...
Using Excel 2000 I have a user who has created a spreadsheet using option
buttons across 4 columns, describing a rating system of 0 - 3. He has 24
rows of criteria being rated, using these option buttons. At the end of
the
rows, he would like to total the number of option buttons used in each
column. Is there a function or other method to do this?

Thank you for any help.

--
LPS

Sorry I misunderstood--see Bob Phillips' response for a solution.

Dave
--
Brevity is the soul of wit.


"LPS" wrote:

That would make sense but it is not my spreadsheet and the user likes the
look and ease of clicking an option button. Is it not possible to total the
number of option button selected in a column?
--
LPS


"Dave F" wrote:

Why use buttons? Just use the numbers 0 through 3 and count the number of
each of 0, 1, 2, and 3.

Dave
--
Brevity is the soul of wit.


"LPS" wrote:

Using Excel 2000 I have a user who has created a spreadsheet using option
buttons across 4 columns, describing a rating system of 0 - 3. He has 24
rows of criteria being rated, using these option buttons. At the end of the
rows, he would like to total the number of option buttons used in each
column. Is there a function or other method to do this?

Thank you for any help.

--
LPS

Bob... thank you for your help. Sadly I am not knowledgeable with macros and
although I copied and pasted the code into VB, I could not get it to do
anything. All I ended up with was a series of Windows command boxes (with
the OK and Cancel buttons) and nothing else. Thank you for trying.
--
LPS


"Bob Phillips" wrote:

See if this is of any help

Dim i As Long
Dim ob As OptionButton
Dim col As Range
Dim aryColLeft
Dim aryCounts

ReDim aryColLeft(1 To 4)
ReDim aryCounts(1 To 4)
For Each col In Columns("B:E")
i = i + 1
aryColLeft(i) = col.Left
Next col

For Each ob In ActiveSheet.OptionButtons
If ob.Value = 1 Then
i = Application.Match(ob.TopLeftCell.Left, aryColLeft, 0)
aryCounts(i) = aryCounts(i) + 1
End If
Next ob

MsgBox aryCounts(1)
MsgBox aryCounts(2)
MsgBox aryCounts(3)
MsgBox aryCounts(4)


--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"LPS" wrote in message
...
Using Excel 2000 I have a user who has created a spreadsheet using option
buttons across 4 columns, describing a rating system of 0 - 3. He has 24
rows of criteria being rated, using these option buttons. At the end of
the
rows, he would like to total the number of option buttons used in each
column. Is there a function or other method to do this?

Thank you for any help.

--
LPS

That means that none of the option buttons in the nominated columns were
clicked.

I have the code setup to be based upon option buttons in columns B:E, if
yours are elsewhere change this line of code

For Each col In Columns("B:E")

I output the results to message boxes, what do you want to do with them?

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"LPS" wrote in message
...
Bob... thank you for your help. Sadly I am not knowledgeable with macros
and
although I copied and pasted the code into VB, I could not get it to do
anything. All I ended up with was a series of Windows command boxes (with
the OK and Cancel buttons) and nothing else. Thank you for trying.
--
LPS


"Bob Phillips" wrote:

See if this is of any help

Dim i As Long
Dim ob As OptionButton
Dim col As Range
Dim aryColLeft
Dim aryCounts

ReDim aryColLeft(1 To 4)
ReDim aryCounts(1 To 4)
For Each col In Columns("B:E")
i = i + 1
aryColLeft(i) = col.Left
Next col

For Each ob In ActiveSheet.OptionButtons
If ob.Value = 1 Then
i = Application.Match(ob.TopLeftCell.Left, aryColLeft, 0)
aryCounts(i) = aryCounts(i) + 1
End If
Next ob

MsgBox aryCounts(1)
MsgBox aryCounts(2)
MsgBox aryCounts(3)
MsgBox aryCounts(4)


--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"LPS" wrote in message
...
Using Excel 2000 I have a user who has created a spreadsheet using
option
buttons across 4 columns, describing a rating system of 0 - 3. He has
24
rows of criteria being rated, using these option buttons. At the end
of
the
rows, he would like to total the number of option buttons used in each
column. Is there a function or other method to do this?

Thank you for any help.

--
LPS