Dynamic command button and timestamp

[alfaspud]

Hi

I am trying to set up a simple system to track equipment availability

I want to place a number of command buttons on a screen which will change colour (green to red and vice versa) and change caption (UP to DOWN and vice versa) every time the button is clicked
I also need to timestamp every click so that I can graph the equipment status

I have found VBA to do both of these separately on the site but I can't figure out how to combine into one

Many thanks

 

[alfaspud]

is it possible to have more than the two states (up, down) - my boss wants to break the downtime into "wait part", "wait tech" etc
clicking the button would cycle through them until the correct status was displayed
Also would need to record the status in an adjacent cell as before
I tried adding extra texts in the .caption line but i get an error

 

[MickG]

Try this:-
NB:- In the array "Caps" you can change/Add any of the captions, after the first value "UP".
NB:- This code is based on "CommandButton1"

Private [COLOR=navy]Sub[/COLOR] CommandButton1_Click()
[COLOR=navy]Dim[/COLOR] Txt [COLOR=navy]As[/COLOR] [COLOR=navy]String,[/COLOR] n [COLOR=navy]As[/COLOR] [COLOR=navy]Integer,[/COLOR] Caps [COLOR=navy]As[/COLOR] Variant
Caps = Array("UP", "Down", "Wait Part", "Wait Tech")
[COLOR=navy]With[/COLOR] CommandButton1
    [COLOR=navy]For[/COLOR] n = 0 To UBound(Caps)
        [COLOR=navy]If[/COLOR] .Caption = "CommandButton1" Or .Caption = Caps(UBound(Caps)) [COLOR=navy]Then[/COLOR]
         .Caption = Caps(0)
        [COLOR=navy]ElseIf[/COLOR] Caps(n) = .Caption [COLOR=navy]Then[/COLOR]
            .Caption = Caps(n + 1)
            [COLOR=navy]Exit[/COLOR] For
     [COLOR=navy]End[/COLOR] If
    [COLOR=navy]Next[/COLOR] n
    .BackColor = IIf(.Caption = "UP", vbRed, vbGreen)
    Txt = .Caption
[COLOR=navy]End[/COLOR] With
   [COLOR=navy]With[/COLOR] Range("A" & Rows.Count).End(xlUp)
       .Offset(1, 0).Value = Time()
       .Offset(1, 1).Value = Txt
    [COLOR=navy]End[/COLOR] With
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick