I am using Mr McGimpsey date time worksheet macro in a customer calling spreadsheet with a small change:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If .Count > 1 Then Exit Sub
If Not Intersect(Range("k3:k1000"), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
With .Offset(0, 1)
'.NumberFormat = "dd mmm yyyy hh:mm:ss"
.Value = Worksheets("Sheet1").Range("l2").Value
Application.EnableEvents = True
If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Cell l2 has the formula =TEXT(NOW(),"ddd mm mmm yy h:mm ") as I wanted a stamp with that date format and that is static
and does not change in the offset cell and with the ability to add text to it.
I wanted a further capability which
is why I am writing to see if you might be able to put me on the right track.
I would like to keep a history of
the calls made in the offset cells (notes heading column).
The K column (Call Status heading) uses a data
validation list with a drop down box: Call Status
The first thing that I would to be able to do is to add the value in the call status cell to the
end of the date in notes cell, according to the entry made in call status cell as follows:
call status -----notes
engaged --------Mon 09 Sep 05 19:31 - engaged
Then on each subsequent call to add to the notes cell - that is
preserve the contents of the cell present but to add a line break and then record the time stamp with the value in the
selected call status value:
no ans---------Mon 09 Sep 05 19:31 - engaged
--------------- Tue 10 Sep 05 12:00 - no ans
I hope that this makes sense. Is this possible to do this in Excel
without too much trouble?
I wrote to Mr McGimpsey for his advice and I am waiting for his reply but I thought that
some of your experts might also be able to help me with this problem. Thank you in advance for all assistance.