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.