[Mike Krumel]

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.

[Bob Kilmer]

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.

[Mike Krumel]

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.



.

[Bob Kilmer]

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.



.

[Bob Kilmer]

Mike,
Try something like this. Paste this code into a worksheet code module. It
will roll thru "Screws, Nails, Tacks" in B2 when you double-click on cell
B2. No controls required. The code contains all the necessary data. You can
specify any cell. You could put this code into a button event a change
Target to a particular cell.

Option Explicit
Private mcolFasteners As Collection

Private Sub Worksheet_BeforeDoubleClick( _
ByVal Target As Range, Cancel As Boolean)
'Changes cell B1 value among
'"Screws, Nails, Tacks" on double-click.

'Limit the effect to, oh, uh, cell B2.
If Target.Address < Range("B2").Address Then
Cancel = False
Exit Sub
End If

On Error GoTo ErrHandler
Dim strBuf As String, v As Variant
'Instatiate and populate a collection
'if not already done.
If mcolFasteners Is Nothing Then
Set mcolFasteners = New Collection
With mcolFasteners
.Add "Screws", "Tacks"
.Add "Nails", "Screws"
.Add "Tacks", "Nails"
End With
End If

'Get, then clear the current value.
strBuf = LCase(Trim(Target.Text))
Target.Clear

'If it is in our collection, use it as
'an index to the next item in the collection.
For Each v In mcolFasteners
If LCase(CStr(v)) = strBuf Then
Target.Value = mcolFasteners(v)
Exit For
End If
Next v

'If not, just assign the cell one of the values.
If Len(Target.Value) < 1 Then
Target.Value = mcolFasteners(1)
End If

ErrHandler:
Cancel = True

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.

[Mike Krumel]

Bob,
That works so well it brings tears to my eyes.

Many thanks,
Mike


-----Original Message-----
Mike,
Try something like this. Paste this code into a worksheet
code module. It
will roll thru "Screws, Nails, Tacks" in B2 when you
double-click on cell
B2. No controls required. The code contains all the
necessary data. You can
specify any cell. You could put this code into a button
event a change
Target to a particular cell.

Option Explicit
Private mcolFasteners As Collection

Private Sub Worksheet_BeforeDoubleClick( _
ByVal Target As Range, Cancel As Boolean)
'Changes cell B1 value among
'"Screws, Nails, Tacks" on double-click.

'Limit the effect to, oh, uh, cell B2.
If Target.Address < Range("B2").Address Then
Cancel = False
Exit Sub
End If

On Error GoTo ErrHandler
Dim strBuf As String, v As Variant
'Instatiate and populate a collection
'if not already done.
If mcolFasteners Is Nothing Then
Set mcolFasteners = New Collection
With mcolFasteners
.Add "Screws", "Tacks"
.Add "Nails", "Screws"
.Add "Tacks", "Nails"
End With
End If

'Get, then clear the current value.
strBuf = LCase(Trim(Target.Text))
Target.Clear

'If it is in our collection, use it as
'an index to the next item in the collection.
For Each v In mcolFasteners
If LCase(CStr(v)) = strBuf Then
Target.Value = mcolFasteners(v)
Exit For
End If
Next v

'If not, just assign the cell one of the values.
If Len(Target.Value) < 1 Then
Target.Value = mcolFasteners(1)
End If

ErrHandler:
Cancel = True

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.



.

[Bob Kilmer]

lol

Bob

"Mike Krumel" wrote in message
...
Bob,
That works so well it brings tears to my eyes.

Many thanks,
Mike


-----Original Message-----
Mike,
Try something like this. Paste this code into a worksheet
code module. It
will roll thru "Screws, Nails, Tacks" in B2 when you
double-click on cell
B2. No controls required. The code contains all the
necessary data. You can
specify any cell. You could put this code into a button
event a change
Target to a particular cell.

Option Explicit
Private mcolFasteners As Collection

Private Sub Worksheet_BeforeDoubleClick( _
ByVal Target As Range, Cancel As Boolean)
'Changes cell B1 value among
'"Screws, Nails, Tacks" on double-click.

'Limit the effect to, oh, uh, cell B2.
If Target.Address < Range("B2").Address Then
Cancel = False
Exit Sub
End If

On Error GoTo ErrHandler
Dim strBuf As String, v As Variant
'Instatiate and populate a collection
'if not already done.
If mcolFasteners Is Nothing Then
Set mcolFasteners = New Collection
With mcolFasteners
.Add "Screws", "Tacks"
.Add "Nails", "Screws"
.Add "Tacks", "Nails"
End With
End If

'Get, then clear the current value.
strBuf = LCase(Trim(Target.Text))
Target.Clear

'If it is in our collection, use it as
'an index to the next item in the collection.
For Each v In mcolFasteners
If LCase(CStr(v)) = strBuf Then
Target.Value = mcolFasteners(v)
Exit For
End If
Next v

'If not, just assign the cell one of the values.
If Len(Target.Value) < 1 Then
Target.Value = mcolFasteners(1)
End If

ErrHandler:
Cancel = True

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.



.