Single command button to select one of several choices.

  • Thread starter Thread starter Mike Krumel
  • Start date Start date
M

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.
 
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
 
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
 
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
 
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
 
Back
Top