A little simpler IncrementValue sub:

Private Sub IncrementValue()
With Range("B1")
If .Value < 3 Then .Value = .Value + 1 Else .Value = 1
Range("C1") = Columns("A").Cells(CInt(.Value)).Text
End With
End Sub

"Bob Kilmer" wrote in message
...
Mike,
Even so, this code can be used. It does depend on "screws", "nails" or
"tacks" being listed somewhere and an "out of the way cell" to store the
index. This data could be in hidden cells or a hidden workbook, or a
separate text file, the registry, database,.... It could be done other
ways.
What would you prefer?

Paste following code into a worksheet code module that contains a Control
Toolbox button. The code displays one of the three choices in C1. It
stores
the data ("screws", "nails" or "tacks") in A1, A2, A3 and stores the index
in B1. These locations can be changed to where ever you want them to be.

Option Explicit

Private Sub CommandButton1_MouseUp(ByVal Button As Integer, ByVal Shift As
Integer, ByVal X As Single, ByVal Y As Single)
Call IncrementValue
End Sub

Private Sub IncrementValue()
With Range("B1")
If Len(.Text) < 1 Then .Value = 1
If .Value < 3 Then .Value = .Value + 1 Else .Value = 1
Range("C1") = Columns("A").Cells(CInt(.Value)).Text
End With
End Sub

Bob

"Mike Krumel" wrote in message
...
Bob,

Thanks for the quick response. I should have set it up a
little more clearly- my mistake. What I should have said
was this:

I just want a button somewhere on the sheet that when
pressed changes the value in a single cell (lets say C5)
to either display "screws", "nails" or "tacks" and just
keep cyling through them as long as I press the button. If
I close the spreadsheet down, when I open it back up the
last value I stopped on would be displayed, no need to
clear. And then I could cycle through them again.

Sorry for the confusion,

Mike
-----Original Message-----
Option buttons are the usual method for presenting one
choice among three.

Having said that, this code will "toggle" thru and
display the contents of
one of three cells as the command button caption. Of
course, it could
display or relay it to a number of places. The code
presumes the option
values reside in the first three cells in column A of the
active sheet and
stores the cell index of the next choice in B1.

Option Explicit
'Paste into the code module of a UserForm
'that has a command button named cmdBtn.

Private Sub cmdBtn_MouseUp(ByVal Button As Integer, ByVal
Shift As Integer,
ByVal X As Single, ByVal Y As Single)
'MouseUp more reliable than Click
Call IncrementValue
End Sub

Private Sub UserForm_Initialize()
Call IncrementValue
End Sub

Private Sub IncrementValue()
With Range("B1")
If Len(Trim(.Text)) < 1 Then .Value = 1
cmdBtn.Caption = Columns("A").Cells(CInt(.Value)).Text
.Value = .Value + 1
If .Value 3 Then .Value = 1
End With
End Sub

Bob Kilmer

"Mike Krumel" wrote in message
...
Greetings,
Here’s and easy one for ya. I’ve been
toying with a spin button and scroll bar to select one
of
3 product types. There are 2 drawbacks (for me anyway):

1) It takes up too much space if I make the buttons
large
enough to easily select.
2) It uses an out of the way cell to store the numeric
values of the buttons and have a function convert them
back to string values.

I would like to add a single button to toggle through
the
3 products like and endless loop. The starting product
value need not be a particular value, but the last one
used would work best. Would I be better off using a for
next loop? Can I do this without and escape route? Am I
making this harder than it should be?

Thanks,
Mike.



.