Custom Spin Button Based On Values Passed From Array
I wish to use CUSTOM buttons for my excel worksheet as below:
[< ] [TEXT ALTERING AS PER LEFT RIGHT CLICK] [>]
LeftCellButtonImage ButtonLinkedText RightCellButtonImage
by passing a variant array:
Dim arr As Variant
arr = Array("Txt1","Txt2","Txt3","Txt4","Txt5","Txt6","T xt7","Txt8","Txt9","Txt10","Txt11","Txt12")
It should work like:
InitialText = [Txt1]
FirstLeftClick LeftCellButtonImage arr(11) FirstRightClick LeftCellButtonImage arr(1)
SecondLeftClick LeftCellButtonImage arr(10) SecondRightClick LeftCellButtonImage arr(2)
ThirdLeftClick LeftCellButtonImage arr(9) ThirdRightClick LeftCellButtonImage arr(3)
FourthLeftClick LeftCellButtonImage arr(8) FourthRightClick LeftCellButtonImage arr(4)
-------------------------------------------------------------------------------------
TwelfthLeftClick LeftCellButtonImage arr(1) FirstRightClick LeftCellButtonImage arr(1)
-------------------------------------------------------------------------------------
In a circular manner.
1 Attachment(s)
Spin Button Based On Values Passed From Array
Code:
Option Explicit
Option Base 1
Private Enum LeftOrRight
LeftMove = 0
RightMove = 1
End Enum
Dim arr As Variant
Dim lngCurrentBound As Long
Sub CusSpin()
If Not IsArray(arr) Then
arr = Array("Txt1", "Txt2", "Txt3", "Txt4", "Txt5", "Txt6", "Txt7", "Txt8", "Txt9", "Txt10", "Txt11", "Txt12")
lngCurrentBound = LBound(arr)
End If
If Application.Caller = "btnLeft" Then
Cells(1, 2).Value = MoveValue(LeftMove)
Else
Cells(1, 2).Value = MoveValue(RightMove)
End If
End Sub
Private Function MoveValue(lngLeftOrRight As LeftOrRight)
If lngLeftOrRight Then
lngCurrentBound = (lngCurrentBound) Mod UBound(arr) + 1
Else
lngCurrentBound = (lngCurrentBound - 1) Mod UBound(arr) + (UBound(arr) * Abs((lngCurrentBound - 1) = 0))
End If
MoveValue = arr(lngCurrentBound)
End Function
