Audit Trail

[Mister H]

Hi All:

I am trying to keep track of when a spreadsheet is returned to the sender. What I want is when I click on ht ebutton "Return" that the VBA Code will mark down the time I am sending it.

I need to keep track of when and how many times it was returned. Basically starting in cell A9 I want it to say:

Returned to sender on: mmmm dd yyyy at h:mm:ss AM/PM

eg Returned to sender on : March 15 2011 at 11:21 AM

IF cell A9 is already filled in then I need the info to be placed in cell A10 then A11 etc.

Any idea how to do this?

THANKS
Mark

 

[VoG]

Hi Mark. Try this line of code

Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset.Value = "Returned to sender on: " & Format(Now, "mmmm dd yyyy at h:mm:ss AM/PM")

 

[ShawnPCooke]

Hi Mark,

You've got two issues at work here: How to capture the moment the button is pressed, and where to put it.

Try this code in the macro triggered by the button press.

Public Sub TimeStamp

Dim StampRange as Range
Dim LoopCell as Range

Set StampRange = Range("A9:A1000")
For Each LoopCell in StampRange

If LoopCell.Text = "" Then

LoopCell.Value = "Returned to Sender on" & Now
Exit For

End If

Next

This code starts with cell A9 and checks that cell and each one below it to see if it's blank. If not, it goes to the next. If so, it adds the time stamp and exits.

 

[Mister H]

THANK YOU to BOTH of you for your help. I have only tried VoG's as he replied first and it is just one line of code that seems to do the trick. It is great to have a couple of methods. I will tuck the longer code away for future use in other projects.

THANKS Again and have a GREAT day,
Mark

 

[Mister H]

Hi VoG:

I have run into a little problem using your line of code. If I am understanding your code it is going to the bottom of the spreadsheet and then moving up until it finds a cell in Column A that has data in it then moves down a row and populates with the date.

Contrary to my first post I have data in cell A9 (A8 is Blank) so I had figured your code would post the time in cell A10 but it keeps putting it in A9. Also if I run the code a 2nd time it still puts it in A9 instead of moving down to A10 then A11 then A12 I am assuming I need to add something to the offsetbut I am not realy sure. Did I do something wrong?

THANKS,
Mark

 

[VoG]

My bad

Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1).Value = "Returned to sender on: " & Format(Now, "mmmm dd yyyy at h:mm:ss AM/PM")

 

[Mister H]

THANKS VoG I could not figure out where to place the offset...

My bad

Have a GREAT day,
Mark

 

[VoG]

No problem. I'm just amazed that the code compiled