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

Steve_Brady
Steve_Brady used Ask the Experts™
on
Hello,

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,
          AND
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

Open in new window

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.

Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Top Expert 2012
Commented:
Don't insert a new module, just double-click on one of the Sheet codepages where you want to use the code.

In VBA, look to the left, find VBAProject(yourworkbook) then expand the stuff below it.  Under Microsoft Excel Objects, you can expand that to find Sheet1 (or 2 or whichever sheet you want this to work in).  Then, paste your code in the editor pane to the right.

See attached example r1.xls and find the code for the first example (using the code you have in Sheet1's codepage).

----------------------------------------------------------------------------

PS - if you want this to work in EVERY sheet in your workbook, use:
Private Sub Workbook_SheetChange(ByVal Sh As Object, 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

Open in new window


And put THAT code in the ThisWorkbook codepage (don't paste anything under the individual Sheet codepages).  With THIS code, of course, you have the Sh parameter, and you can use Sh.Name to determine if some worksheets should be omitted.

See example r2.xls for the example that works on ALL sheets, as the code is associated with ThisWorkbook events.

Cheers,

Dave
example-r1.xls
example-r2.xls
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013
Commented:
Steve,
You should also turn events off before attempting to change the value of a cell--otherwise your code runs recursively.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cl As Range, targ As Range
Set targ = Range("B:B") 'Watch these cells for changes
Set targ = Intersect(targ, Target)
If targ Is Nothing Then Exit Sub

Application.EnableEvents = False
For Each cl In targ.Cells
    cl.Offset(0, -1).Value = Now
Next cl
Application.EnableEvents = True
End Sub

Open in new window


Brad
Most Valuable Expert 2012
Top Expert 2012
Commented:
Brad - actually, that's not needed in this case (but a good idea!)

Because the cells being affected with the time stamp are NOT the cells being examined for the change.

E.g., check for change in column B, then write something to column A.  Will not recurse.

Cheers,

Dave
Become a Microsoft Certified Solutions Expert

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013
Commented:
Dave,
It recurses, but only once. I agree that it makes no practical difference, however.

I turn events off as a matter of habit. Because when I forget--and needed to do it--that's when I curse and recurse.

Brad
Most Valuable Expert 2012
Top Expert 2012
Commented:
I'm with You, re cursing the recurring

It's a good practice agreed

Dave
NorieAnalyst Assistant
Commented:
Steve

Looks like I was beaten to it by Dave.:)

Connection went down for a few hours for some reason, second time this week at the about the same time as well.

Anyway, the code belongs in a worksheet module or if it's for every worksheet then in the workbook module.
Most Valuable Expert 2011
Top Expert 2011
Commented:
BTW, the quick way to the worksheet's code module is to right-click the sheet tab in the main Excel window and choose View Code. That way you know you're in the right sheet. :)

Author

Commented:
Many thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial