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
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,834
Office Version
  1. 2013
Platform
  1. Windows
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
 

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
3,050
Office Version
  1. 2016
Platform
  1. Windows
Code:
Sub Set_Now()
Cells(Rows.Count, "A").End(xlUp)(2) = Now()
End Sub
 

Batterychook

New Member
Joined
Jul 16, 2018
Messages
11
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:

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,834
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

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??
 

Batterychook

New Member
Joined
Jul 16, 2018
Messages
11
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

 

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
3,050
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

I suggest you change the layout of you sheet:
Column A header : Depart Time
Column B header : Arrive Time
Column C header : Time Away
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,834
Office Version
  1. 2013
Platform
  1. Windows
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 ?
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,834
Office Version
  1. 2013
Platform
  1. Windows
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
 

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
3,050
Office Version
  1. 2016
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,109,146
Messages
5,527,084
Members
409,743
Latest member
adamyang24

This Week's Hot Topics

Top