+ Reply to Thread
Results 1 to 2 of 2

Excel time-stamps - per row?

Hybrid View

scottmcglasson Excel time-stamps - per row? 12-08-2008, 09:52 AM
royUK You can do this with VBA ... 12-08-2008, 10:13 AM
  1. #1
    Registered User
    Join Date
    09-02-2008
    Location
    St Louis
    Posts
    5

    Excel time-stamps - per row?

    My company deals with trucking companies as vendors. We are using an Excel 2007 spreadsheet as a defacto database keyed to each company's unique federally-assigned number. This spreadsheet has each company in a single row, with the columns containing critical info like name, contact, address, etc.

    Periodically, a company will change it's info. They move buildings, change phones, change contacts, update their billing info, etc. When this happens, I go in and change what's necessary and all other Excel documents that pull info from that "database" spreadsheet automatically update the next time they're opened. No problem there (thank the Excel gods for v-lookups).

    However, since there are two, and soon to be three, different people using, updating, and requiring info from that database, I would like to try and timestamp any changes made to individual company records.

    Is there any way to add a column, say on the far right side of all current info, that will automatically time-stamp a change made to any of the cells in that row? I know the file itself gets a time stamp any time it's opened and changed, but with over 1000 companies, that's fairly vague.

    With my admittedly intermediate expertise at Excel, I don't see an easy way to do this, but maybe someone out there does...short of telling me to move it all to Access, that is.

  2. #2
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200
    You can do this with VBA

    Option Explicit
    
    Private Sub Worksheet_Change(ByVal Target As Range)
    'don't use multi cell selections
    If Target.Count > 1 Then Exit Sub
    Application.EnableEvents = False
    Cells(Target.Row, Columns.Count).End(xlToLeft).Offset(0,1).Value = Now
    Application.EnableEvents = True
    End Sub
    Worksheet event code is stored on a worksheet module. To add it to your worksheet, do the following:

    Copy the code that you want to use
    Select the worksheet in which you the code to run
    Right click on the sheet tab and choose View Code, to open the Visual Basic Editor.
    Check that the left hand drop down is set to WorkSheet
    Where the cursor is flashing, choose Edit | Paste
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1