Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Chapel Hill, North Carolina, USA
    Posts
    138
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Automatically storing date with VBA (2003)

    I want to store the current date in column B on each row each time a value is store in column A for that row. For example, if I enter a value in A1, I want to store the current date in B1 automatically.

    I thought the VBA code

    Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Column = 1 Then
    Range("a1").Offset(Target.Row, 2).Value = Now()
    End If

    End Sub

    would do it, but it does not work. I added a "msgbox" command to verify the code is run when I change a cell, but the code never executes. I've used a similar subroutine in the past and am not sure why this does not automatically run when a change is made to the worksheet.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Automatically storing date with VBA (2003)

    Try this:

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oCell As Range
    If Not Intersect(Range("A:A"), Target) Is Nothing Then
    Application.EnableEvents = False
    For Each oCell In Intersect(Range("A:A"), Target).Cells
    oCell.Offset(0, 1) = Date
    Next oCell
    Application.EnableEvents = True
    End If
    End Sub

    If you want the current date + time instead of just the current date, replace Date with Now.

  3. #3
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Chapel Hill, North Carolina, USA
    Posts
    138
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automatically storing date with VBA (2003)

    Thanks Hans, but no luck. I've tried storing this in the worksheet and in a module. I think my problem is more basic. I've added a "msgbox" statement to verify the macros runs - and it does not. The macro does not appear on the list of macros when I open the list of macros from the "tools" menu item. I've checked the security setting (medium) and enabled macros when I opened the file. Any ideas where I am going wrong?

    Thanks again!

    Larry

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Automatically storing date with VBA (2003)

    Did you put the code in the worksheet code module or in a normal module? It needs to be in the former. The easiest way to access the worksheet's code module is to right-click the sheet tab and choose View Code. If it is there and still not running, try running this in the Immediate window in the VBEditor:
    <code>Application.EnableEvents = True</code>
    HTH
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Chapel Hill, North Carolina, USA
    Posts
    138
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automatically storing date with VBA (2003)

    Thanks - pilot error on my part - I was testing the code on a sheet that was created called "Macro" instead of "Sheet1" - all's good now.

    Thanks to you both!

    Larry

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •