Static Time Stamp?

[paulfisheriii]

Hello, I know how to do a static timestamp by using control + shift + colon, however I am trying to make a spreadsheet do this automatically when a cell is populated.

For example, when someone enters a value in cell A1, I want B1 to stamp the static current time when A1 was populated.

This doesn't seem like it should be too hard but I'm drawing a blank. Please help, thanks!

Paul

 

[JRinderer]

I modified a simple program I use for tracking every time a cell value changes, there's more to it but the basic is:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range
Dim wrkBK As Workbook
Dim wrkSht As Worksheet


Set wrkBK = ThisWorkbook
Set wrkSht = wrkBK.Sheets("Sheet1")


    Set KeyCells = wrkSht.Range("A1:A1") 'Checks to see if this was the cell or range of cells changed (you could have it span the whole column(
    'test to see if changes made are in the range
    For Each Target In Target
        If Not Intersect(Target, KeyCells) Is Nothing Then
            wrkSht.Cells(1, 2).Value2 = Date ' Sets B2 to Date
        End If
    Next Target


Set wrkBK = Nothing
Set wrkSht = Nothing

End Sub

Every time a user enters their cursor into A1, once they leave the date will populate in B1. There may be another way with formula's, but this only works on change events.

You can expand on it even more, the draw back here is the cursor entering the cell is enough to trigger a "change". I have another function that actually stores the value of the cell and then compares it to see if it really changed. You can also expand it to change for A2 changed B2 Date etc. down the line.

 

[paulfisheriii]

This is great for cell A1, thank you! Can you elaborate how to get it to work for cells A2, A3, etc.?

 

[JRinderer]

Sure can, we'd need two new variables. Size and trgtRow (Target Row) Size will get the total number of rows in Column A (column 1). trgtRow will hold the row that was just changed.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range
Dim wrkBK As Workbook
Dim wrkSht As Worksheet
Dim size As Long
Dim trgtRow As Long


Set wrkBK = ThisWorkbook
Set wrkSht = wrkBK.Sheets("Sheet1")


size = wrkSht.Columns(1).Find("*", , , , xlByColumns, xlPrevious).Row




    Set KeyCells = wrkSht.Range("A1:A" & (size + 1)) 'I use size+1 because the header here is blank so you should be able to get away with just size
    For Each Target In Target
        If Not Intersect(Target, KeyCells) Is Nothing Then
            trgtRow = Target.Row
            wrkSht.Cells(trgtRow, 2).Value2 = Date ''this should result in a dynamic change by row.
        End If
    Next Target

Set wrkBK = Nothing
Set wrkSht = Nothing


End Sub

Note, the size code presume the worksheet has SOME data in column A, if none is there an error results. If you don't care about the dynamics, or if maybe column A could be blank, then you could just change:

    Set KeyCells = wrkSht.Range("A1:A" & (size + 1))

to

    Set KeyCells = wrkSht.Range("A:A")

The second option will look at all of column A, and you can drop the size variable as well.

Both will dynamically change, but 1 requires data in Column A (none blank), the other will just look at ALL of column A. The workbooks I work with I limit row size based on data feed to us. I don't want users who might be making notes in some other cell to impact anything. It's a bit more restrictive, but the size of my workbooks is dictated by a database, so any "add new" is done on the backend. If you need to add, and track those adds, on the front end, Range(A:A) is probably better.

 

[paulfisheriii]

This is awesome, thank you so much! Your code works like a charm! It is sooo close to doing exactly what I want but I can't quite figure it out so I need to ask for one more thing.

So this is working perfectly for data in column A to populate the date in column B. I actually need it to do the same thing in columns E and F (input data in column E, then have it populate static date stamp in column F). I didn't mention this at first because I figured once I got the code working for column A I'd be smart enough to duplicate it to work in the second column. I myself too much credit

Please help one more time!

 

[JRinderer]

So I'm not sure if you want E and F to have Date when A changes, or if you want F to Change when E Changes. I'll write out both instances, you can use whichever fits. First let's presume you need E and F to change when A changes. Just change the following:

    Set KeyCells = wrkSht.Range("A1:A" & (size + 1)) 'I use size+1 because the header here is blank so you should be able to get away with just size
    For Each Target In Target
        If Not Intersect(Target, KeyCells) Is Nothing Then
            trgtRow = Target.Row
            wrkSht.Cells(trgtRow, 2).Value2 = Date ''this should result in a dynamic change by row.
            wrkSht.Cells(trgtRow, 5).Value2 = Date 'This will change trgtRow and column E  
            wrkSht.Cells(trgtRow, 6).Value2 = Date 'This will change trgtRow and column F


        End If
    Next Target

The above section of code has been modified to change columns E and F to Date when column A changes. Below will apply the same logic for A & B to E & F. As E changes F assign a date. There might be a better way to do this, but I prefer to be overly clear in my code so when I come back to it I can read it easily.

We would need to add 1 new variable KeyCellsE this will hold the key range of Column E:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range
Dim KeyCellsE As Range
Dim wrkBK As Workbook
Dim wrkSht As Worksheet
Dim size As Long
Dim trgtRow As Long


Set wrkBK = ThisWorkbook
Set wrkSht = wrkBK.Sheets("Sheet1")


size = wrkSht.Columns(1).Find("*", , , , xlByColumns, xlPrevious).Row

    Set KeyCells = wrkSht.Range("A1:A" & (size + 1)) 'I use size+1 because the header here is blank so you should be able to get away with just size
    Set KeyCellsE = wrkSht.Range("E1:E" & (size + 1)) 'I use size+1 because the header here is blank so you should be able to get away with just size
    For Each Target In Target
        If Not Intersect(Target, KeyCells) Is Nothing Then
            trgtRow = Target.Row
            wrkSht.Cells(trgtRow, 2).Value2 = Date ''this should result in a dynamic change by row.
        End If
        '=================Apply logic to new columns=============================
        If Not Intersect(Target, KeyCellsE) Is Nothing Then
            trgtRow = Target.Row
            wrkSht.Cells(trgtRow, 6).Value2 = Date ''this should result in a dynamic change by row.
        End If
    Next Target

Set wrkBK = Nothing
Set wrkSht = Nothing


End Sub

The above code will check to see if Column A changed. If it did it will put data in column B. It will also check if column E has changed, if so it will put data in column F.

 

[paulfisheriii]

You are a genius, my friend. Not only were you able to help me, but you did so even with a poor explanation of what I was trying to accomplish. It was indeed the second scenario described (E to change F) and it worked perfectly. Thank you so much!

One last general question - do you have any advice for someone at a beginner VBA level to get to your level? Is there a professional certification or perhaps a training course you might recommend?

 

[JRinderer]

Lol, definitely not a genius, just experience. It comes from working with it so much; and the best way to learn it is to sit down and do it. I did a lot of programming as a kid, so when I started working (ironically not as a programmer) I started automating aspects of my job. Once I took on a job that pretty much lived in Excel I just started writing code in VBA. If you've got a job that requires you work in Excel a lot just start thinking of how you can solve it with code. Even if it's just some pseudo code, if you understand the logic behind what I explained then you'll have an easy enough time learning it.

if thisworkbook.sheets("Sheet1").cells(1,1).value2 = "Yes" then
      thisworkbook.sheets("Sheet1").cells(1,2).value2 = "No"
end if

The above can take you a long way. It's the basics of all programming languages. ThisWorkbook refers to the workbook you're in .Sheets("Sheet1") is referencing the Worksheet named Sheet1, .Cells references a particular cell (in this case A1) and .value2 extracts the value from the cell.

If you're doing any work in Excel I recommend learning VBA; it's always amazed me that some programmers I've meet look down on VBA. But I can automate almost every aspects of the Microsoft world with Excel, that's pretty darn useful.

This site is a great place for Excel questions, be it formulas or VBA script. In fact I typically come here before StackOverFlow, and if it's VBA related questions I have they can usually be found here. Google also goes a long way.

Another approach is to record macros and then analyze the code; I'll do this for stuff I forget (like clearing filters) in VBA.

 

[paulfisheriii]

Excellent advice, thank you. As it turns out, you and I are very similar in our approach to learning VBA. The big difference is I think I am many years behind you. I got started recording simple macros and analyzing the code to understand how it works, then building upon that for slightly more complex issues. I now have a decent working knowledge of VBA but no where near at the point where I could write my own code from scratch. I love it so I will keep on learning with the help of experts like yourself. Thanks again sir, I greatly appreciate it.

 

[Oxon258]

Hi, sorry to jump on the back of this thread, but because the subject is nearly ideal and it's pretty recent I thought I'd ask the question!

I'm currently trying to add a date stamp (column A) and time stamp (Column B) after inputting data into column D for the first time (i.e. no updates in time or date after the first entry of a specific row in column D). The input, date and time should be on the same row.

I've hunted around and found code to do date only and time only, but with my current lack of skill I'm struggling to figure out how to merge the two. I'd very much appreciate your help on this, if possible! Below are the sections of cod I have so far:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 4 And Target.Offset(0, -3).Value = "" Then
Target.Offset(0, -3) = Format(Now(), "DD/MM/YYYY")
End If
End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 4 And Target.Offset(0, -2).Value = "" Then
Target.Offset(0, -2) = Format(Now(), "HH:MM:SS")
End If
End Sub</pre>