VB stopwatch with form

stevehorton09 · May 26th 2004

Hi People,

I am currently working with a stopwatch function (using a form)

At present the function i have created has two command buttons, one start and one stop which gives the date and time from clicking each button (one in a1 the other b1)

What i want it to do is not overwrite the information each time but just go to the next line (a2 and b2 etc etc), how do i do that?

Cheers

Steve

Code currently is

Private Sub CommandButton1_Click()
Range("A1").Value = Date
Range("A2").Value = Time
End Sub

Private Sub CommandButton2_Click()
Range("b1").Value = Date
Range("b2").Value = Time
End Sub

yjoshi · May 26th 2004

I will suggest following code

Code

Private Sub CommandButton1_Click()
Range("A65536").End(xlUp).Offset(1, 0).Value = Now
End Sub


Private Sub CommandButton2_Click()
Range("A65536").End(xlUp).Offset(0, 1).Value = Now
End Sub

The Reason for replacing now with Date & time is that its combination
of both and you can take out all the information you need with this.

Anonymous · May 26th 2004

There are several ways to do it. Here's one to try.

Code

Private Sub CommandButton1_Click()
With Cells(65536, 1).End(xlUp).Offset(1, 0)
    .Value = Date
    .Offset(1, 0).Value = Time
End With
End Sub


Private Sub CommandButton2_Click()
With Cells(65536, 2).End(xlUp).Offset(1, 0)
    .Value = Date
    .Offset(1, 0).Value = Time
End With
End Sub

Display More

stevehorton09 · May 26th 2004

ok cool,

how would i then stop someone from pushing the stop button then the start button as this would mean the stop time is earlier than the start time

Anonymous · May 26th 2004

Quote from stevehorton09

ok cool,

how would i then stop someone from pushing the stop button then the start button as this would mean the stop time is earlier than the start time

Code

Private Sub CommandButton2_Click()
    With Cells(65536, 2).End(xlUp).Offset(1, 0)
        If IsEmpty(.Offset(0, -1)) Then
            MsgBox "You forgot to push the Start Button first"
        Else
            .Value = Date
            .Offset(1, 0).Value = Time
        End If
    End With
End Sub

stevehorton09 · May 26th 2004

i have tried this but it keeps returning the msgbox whatever, im not great on the indexing number, the code is below

thanks

steve

Private Sub ComboBox1_Change()
ComboBox1.AddItem "Setting up a new Bank"
ComboBox1.AddItem "New Account"
ComboBox1.AddItem "Change Account"
ComboBox1.AddItem "Close "
'do not hard code
End Sub

Private Sub CommandButton1_Click()

Dim Rng As Range
Set Rng = Cells(Rows.Count, "A").End(xlUp)(2, 1)
Rng(1, 1).Value = Date
Rng(1, 2).Value = Time
Rng(1, 3).Value = ComboBox1.Value

End Sub

Private Sub CommandButton2_Click()

With Cells(65536, 2).End(xlUp).Offset(1, 0)
If IsEmpty(.Offset(2, -1)) Then
MsgBox "You forgot to push the Start Button first"
Else
.Value = Date
.Offset(1, 0).Value = Time
End If
End With
End Sub

Private Sub UserForm_Click()

End Sub

Anonymous · May 26th 2004

'm confused about the layout of your data that you want. The first button puts data in columns A, B, C. Where should the second button put it? The following does it in D and E.

Code

Private Sub CommandButton2_Click()
    With Cells(65536, 4).End(xlUp).Offset(1, 0)
        If IsEmpty(.Offset(0, -1)) Then
            MsgBox "You forgot to push the Start Button first"
        Else
            .Value = Date
            .Offset(0, 1).Value = Time
        End If
    End With
End Sub

stevehorton09 · May 26th 2004

thats correct d,e, shou;ld have this.

I have inserted your code but it still always says the msgbox

Private Sub CommandButton1_Click()

Dim Rng As Range
Set Rng = Cells(Rows.Count, "A").End(xlUp)(2, 1)
Rng(1, 1).Value = Date
Rng(1, 2).Value = Time
Rng(1, 3).Value = ComboBox1.Value

End Sub

Private Sub CommandButton2_Click()
With Cells(65536, 4).End(xlUp).Offset(2, 1)
If IsEmpty(.Offset(0, -1)) Then
MsgBox "You forgot to push the Start Button first"
Else
.Value = Date
.Offset(0, 1).Value = Time
End If
End With
End Sub

yjoshi · May 27th 2004

I will suggest a simpler solution

Just use one button with Caption "Start" and in the run time change it
to "Stop" once it is clicked.

If the caption is Start when clicked, it will enter date and time in Cols A & B respectively
If the caption is Stop when clicked, it will enter date and time in Cols C & D respectively, in the same row it started, thats why i have used (1,1) in first part and (0,3) in second part

Let me know if this does not solve your problem

Code

Private Sub CommandButton1_Click()
If CommandButton1.Caption = "Start" Then
    CommandButton1.Caption = "Stop"
    Set Rng = Cells(Rows.Count, "A").End(xlUp)(2, 1)
    Rng(1, 1).Value = Date
    Rng(1, 2).Value = Time
Else
    CommandButton1.Caption = "Start"
    Set Rng = Cells(Rows.Count, "A").End(xlUp)(2, 1)
    Rng(0, 3).Value = Date
    Rng(0, 4).Value = Time


End If
End Sub

Display More

stevehorton09 · May 27th 2004

hi,

this works great but when i first load up the sheet, when you push the button it is loading stop time first. then it works after the first time, can you set this so it resets itself to start each time it starts and stops?

the code is as follows

If CommandButton1.Caption = "Start" Then
CommandButton1.Caption = "Stop"
Set Rng = Cells(Rows.Count, "A").End(xlUp)(2, 1)
Rng(1, 1).Value = Date
Rng(1, 2).Value = Time
Rng(1, 3).Value = ComboBox1
Else
CommandButton1.Caption = "Start"
Set Rng = Cells(Rows.Count, "A").End(xlUp)(2, 1)
Rng(0, 4).Value = Date
Rng(0, 5).Value = Time

stevehorton09 · May 27th 2004

ignore please worked it out already, thanks for all your help

yjoshi · May 27th 2004

Your query was not irrelevant

Put this code so that when you start the form for the first time, even if the caption is
something different, it will be set right.

Code

Private Sub UserForm_Initialize()
    CommandButton1.Caption = "Start"
End Sub

VB stopwatch with form

Participate now!

Share