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

 

[MickG]

Perhaps something like this:-

Private [COLOR=navy]Sub[/COLOR] CommandButton1_Click()
[COLOR=navy]With[/COLOR] CommandButton1
    .Caption = IIf(.Caption = "UP", "Down", "UP")
    .BackColor = IIf(.BackColor = vbRed, vbGreen, vbRed)
[COLOR=navy]End[/COLOR] With
Range("A1").Value = Time()
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

 

[kvsrinivasamurthy]

I feel in the above code this line

Range("A1").Value = Time()

is to be changed as

Range("A" & Rows.Count).End(Xlup).Offset(1,0).Value = Time()

since you want chart.

 

[alfaspud]

Perhaps something like this:-

Private [COLOR=navy]Sub[/COLOR] CommandButton1_Click()
[COLOR=navy]With[/COLOR] CommandButton1
    .Caption = IIf(.Caption = "UP", "Down", "UP")
    .BackColor = IIf(.BackColor = vbRed, vbGreen, vbRed)
[COLOR=navy]End[/COLOR] With
Range("A1").Value = Time()
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

Thank you Mick

How do I get the timestamp to save in the next cell after every click (a1, a2, etc)?

 

[alfaspud]

Sorry, just seeing kvrinvasamurthy's reply above

Thank you both

 

[MickG]

You're welcome

 

[alfaspud]

Hi guys
If I wanted to record the caption from the command button in the adjacent cell to the timestamp, how would I go about this?

Cheers

 

[MickG]

Try:-

Private Sub CommandButton1_Click()
Dim Txt As String
With CommandButton1
    .Caption = IIf(.Caption = "UP", "Down", "UP")
    .BackColor = IIf(.BackColor = vbRed, vbGreen, vbRed)
    Txt = .Caption
End With
   With Range("A" & Rows.Count).End(xlUp)
       .Offset(1, 0).Value = Time()
       .Offset(1, 1).Value = Txt
    End With
End Sub

 

[alfaspud]

Thank you Mick

 

[MickG]

You're welcome