troubleshooting Question

Can't get Excel VBA from another thread to function properly.

Avatar of Steve_Brady
Steve_BradyFlag for United States of America asked on
Microsoft Excel
8 Comments7 Solutions462 ViewsLast Modified:

This is a follow-up to another thread here:

          Automated date & time stamping in Excel

The gist of the other thread was to find a way to:

a) automatically have a Date & Time stamp appear (with the current date and time) in column A whenever any type of entry is made in column B in the corresponding row,
b) have the Date & Time remain static or fixed so, for example, the date and time the entry was made can be ascertained even if one is looking at the spreadsheet after the fact.

The solution included the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cl As Range
    If Target.Column = 2 Then
        For Each cl In Target.Cells
            cl.Offset(, -1).Value = Now
        Next cl
    End If
End Sub
with instructions to:

          "Put this in the module of the worksheet you want to add the timestamp to."

I'm doing something wrong because I'm not getting the Date & Time stamps I anticipated. Here's what I did:

          1) opened a brand-new workbook and opened Visual Basic
          2) in Visual Basic > Insert menu > Module
          3) pasted the above code in the module
          4) Save As an .xlsm (macro-enabled) file
          5) began making entries in column B

If someone could give me a brief explanation of what I am missing and how to make the code functional, I would appreciate it.

Join our community to see this answer!
Unlock 7 Answers and 8 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 7 Answers and 8 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros