Form/ActiveX control to cycle through 6 different statuses

[zhfgtj27]

I'm playing a volleyball tournament recently and was thinking of setting up the lineup for our starters. Since there are rotations in volleyball games, I was wondering if there's an easier way to press a "toggle button" (except I have 6 different statuses instead of 2) and a clock-wise rotation occurs. So basically, I have 6 names in the following cells.
[TABLE="width: 50"]
<tbody>[TR]
[TD]DD
[/TD]
[TD]EE
[/TD]
[TD]FF
[/TD]
[/TR]
[TR]
[TD]CC
[/TD]
[TD]BB
[/TD]
[TD]AA
[/TD]
[/TR]
</tbody>[/TABLE]

After press a button, the following status should appear:
[TABLE="width: 50"]
<tbody>[TR]
[TD]CC
[/TD]
[TD]DD
[/TD]
[TD]EE
[/TD]
[/TR]
[TR]
[TD]BB
[/TD]
[TD]AA
[/TD]
[TD]FF
[/TD]
[/TR]
</tbody>[/TABLE]

And so on. And after pressing the button 6 times, it should go back to the orignial status.

Thought it should be an interesting topic and will appreciate any help provided.

FZ

 

[MickG]

Try this:-
NB:- Your Data in "A1:C2"

[COLOR="Navy"]Sub[/COLOR] MG02Nov34
[COLOR="Navy"]Dim[/COLOR] n       [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Rng     [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] nray(1 To 6) [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] R       [COLOR="Navy"]As[/COLOR] Variant
    [COLOR="Navy"]Set[/COLOR] Rng = Range("A1:C2")
            R = Array(4, 1, 2, 3, 6, 5)
        [COLOR="Navy"]For[/COLOR] n = 0 To 5
            nray(n + 1) = Rng(R(n))
        [COLOR="Navy"]Next[/COLOR] n
            R = Array(1, 2, 3, 6, 5, 4)
        [COLOR="Navy"]For[/COLOR] n = 1 To 6
            Rng(n) = nray(R(n - 1))
        [COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[zhfgtj27]

It works very well. Thanks a lot Mick!
Just curious, do you think any of the form controls or ActiveX controls will work? Just thought it will be more convenient if that works, instead, I will have to open the VBA working window to run this macro for every rotation. I will appreciate if you can share with me any of your thoughts.

Try this:-
NB:- Your Data in "A1:C2"

[COLOR=Navy]Sub[/COLOR] MG02Nov34
[COLOR=Navy]Dim[/COLOR] n       [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long[/COLOR]
[COLOR=Navy]Dim[/COLOR] Rng     [COLOR=Navy]As[/COLOR] Range
[COLOR=Navy]Dim[/COLOR] nray(1 To 6) [COLOR=Navy]As[/COLOR] [COLOR=Navy]String[/COLOR]
[COLOR=Navy]Dim[/COLOR] R       [COLOR=Navy]As[/COLOR] Variant
    [COLOR=Navy]Set[/COLOR] Rng = Range("A1:C2")
            R = Array(4, 1, 2, 3, 6, 5)
        [COLOR=Navy]For[/COLOR] n = 0 To 5
            nray(n + 1) = Rng(R(n))
        [COLOR=Navy]Next[/COLOR] n
            R = Array(1, 2, 3, 6, 5, 4)
        [COLOR=Navy]For[/COLOR] n = 1 To 6
            Rng(n) = nray(R(n - 1))
        [COLOR=Navy]Next[/COLOR] n
[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]

Regards Mick

 

[MickG]

You can place it directly into a "Forms Button" or "ActiveX Command Button", Just remove the first line "Sub MG02Nov34", Place either button on the sheet from the Developer Tab. For "ActiveX" double click the Command button in design Mode and Paste code into VbWindow, or for "Forms" right click the form Select "Assign Macro", "New" and paste code into Module.