Excel - VB Button

  • Thread starter Thread starter Michael Groves
  • Start date Start date
M

Michael Groves

I use buttons in a spreadsheet to do certain tasks, I
want a button to switch between the following entries in
one cell. The entries are W,2,4,Q,M. Is there some
simple VB code that I can add to the button to switch
between these values.

Thanks in Advance.
 
One way:

Option Explicit
Sub testme02()

'W,2,4,Q,M.

With ActiveCell
Select Case UCase(.Value)
Case Is = "W": .Value = 2
Case Is = 2: .Value = 4
Case Is = 4: .Value = "Q"
Case Is = "Q": .Value = "M"
Case Else: .Value = "W"
End Select
End With

End Sub

But if your list gets longer, it gets boring...

Option Explicit
Sub testme03()

Dim myValues As String
Dim myNextPos As Long

myValues = "W24QM"

With ActiveCell
myNextPos = InStr(1, myValues, .Value, vbTextCompare) + 1
If myNextPos > Len(myValues) Then
myNextPos = 1
End If
.Value = Mid(myValues, myNextPos, 1)
End With

End Sub
 
Back
Top