Using Command Button to populate column with Timestamp, without over-write

Batterychook

New Member
Joined
Jul 16, 2018
Messages
11
Hi all,
Newbie here and to VBA in general. (using Excel 2016)
I need to log the departure times for a delivery van from a depot over the course of a working day, and record the results in a column.
I wish to use a Command Button to deliver a NOW() Timestamp into a specified cell in a column. (I get this)
How do I then use the same Command Button to record the next departure time in the next cell in the column, while still maintaining (not overwriting) the previous recorded departure time ?
Thanks in advance !
Batterychook
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Assuming you want to enter the Time stamp in column A
Try this:
Code:
Sub Set_Now()
'Modified  7/16/2018  8:59:41 PM  EDT
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row + 1
Cells(Lastrow, 1).Value = Now()
End Sub
 
Upvote 0
Code:
Sub Set_Now()
Cells(Rows.Count, "A").End(xlUp)(2) = Now()
End Sub
 
Upvote 0
Hi,
Many thanks for replying but as this is my very first attempt at VBA you will need to spell this out a bit more for the dummy
So far I have the following :

Code:
-- corrupted image removed --

I'm sure you will know this is simply timestamping all entries at once in the "A" range, rather than on sequential button clicks.
What do I need to change ?
Appreciate your time
Batterychook
 
Last edited by a moderator:
Upvote 0
We need specific details what you want.
Thus is not specific:

You said:
How do I then use the same Command Button to record the next departure time in the next cell in the column,

What column ??
You want many time stamps all at once??
 
Upvote 0
Thanks foryour patience. Yes, this is a littlemore complex in coding than first anticipated – particularly for a complete novice.

The scenariois that I have a delivery van departing a shop and returning later, around 5times a day. I need to log the actualdeparture times the van leaves the shop, and arrival times for when the van arrivesback at the shop and calculate the elapsed time the van was away from the shop. If I have a command button for “Depart” andanother for “Arrive”, that triggers a timestamp in the spreadsheet for theseevents, the gate guy at the shop only needs to press the appropriate buttoneach time the van either departs from the shop, or arrives back at the shopduring the day. The appropriate command buttonenters the current time into the respective cell location in Column B in thespreadsheet as described below.

So, thespreadsheet could look like this :

ColumnA Column B
Row 10 Depart time 1 Timestamp
Row 11 Arrive time 1 Timestamp
Row 12 Elapsed calc timeaway
Row 13
Row 14 Depart time 2 Timestamp
Row 15 Arrive time 2 Timestamp
Row 16 Elapsed calc timeaway
Row 17
Row 18 Depart time 3 Timestamp
Row 19 Arrive time 3 Timestamp
Row 20 Elapsed calc timeaway

…..and soon, for say maximum of 6 round trips per day.

So, only onetimestamp at a time progressively down Column B, triggered either by the “Depart”command button, or “Arrive” command button.

I hope thisis a little clearer.

BC

 
Upvote 0
I suggest you change the layout of you sheet:
Column A header : Depart Time
Column B header : Arrive Time
Column C header : Time Away
 
Upvote 0
From looking at this I do not know what you have in column A

Do you actually have Row10 in column A

So the script put's nothing in column A only column B

Why do you need Row 10 in column A ?
 
Upvote 0
Try this:
I added a part where column C will show information also.
It will show in out and duration will give a better viewing effect
Remove that part if you want:

Code:
Sub Sign_Out()
'Modified  7/18/2018  12:01:19 AM  EDT
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "B").End(xlUp).Row + 2
Cells(Lastrow, "B").Value = Now(): Cells(Lastrow, "B").Offset(, 1).Value = "Out"
End Sub

Code:
Sub Sign_In()
'Modified  7/18/2018  12:01:19 AM  EDT
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "B").End(xlUp).Row + 1
Cells(Lastrow, "B").Value = Now(): Cells(Lastrow, "B").Offset(, 1).Value = "In"
Cells(Lastrow + 1, "B").Value = Format(Cells(Lastrow + 1, "B").Offset(-1).Value - Cells(Lastrow + 1, "B").Offset(-2).Value, "hh:mm:ss"): Cells(Lastrow + 1, "B").Offset(, 1).Value = "Duration"
End Sub
 
Upvote 0
If the sheet is formatted like post #7 :
Code:
Sub Sign_Out()
Cells(Rows.Count, "A").End(xlUp)(2) = Now()
End Sub


Sub Sign_In()
With Cells(Rows.Count, "B").End(xlUp)(2)
    .Value = Now()
    .Offset(, 1) = .Value - .Offset(, -1)
End With
End Sub

Format column C : hh:mm
 
Upvote 0

Forum statistics

Threads
1,213,539
Messages
6,114,221
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top