Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Worksheet changes (XL 2000 SP3)

    I have my UsageLog This Thread working for file opened, file saved, and username, but I cannot get it to record when changes are made anywhere in the workbook.

    I tried:

    Private Sub Worksheet_Change(ByVal Target As Excel.Range)

    ActiveWorkbook.Worksheets("UsageLog").Range("A6553 6").End(xlUp).Offset(0, 1).Value = Time$ & Space(5) & Date$

    End Sub

    saved in ThisWorkbook, without success, so I tried the simpler:

    Private Sub Worksheet_Change(ByVal Target As Excel.Range)

    MsgBox "Range " & Target.Address & "was changed"

    End Sub

    Taken from John Walkenbach, but again no success. Can anyone help please?

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

    Re: Worksheet changes (XL 2000 SP3)

    The Worksheet_Change event procedure should *not* go into ThisWorkbook, but - as the name indicates - into the worksheet module for each worksheet.

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Worksheet changes (XL 2000 SP3)

    Is there anything wrong in altering it to look like this:

    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    ActiveWorkbook.Worksheets("UsageLog").Range("A6553 6").End(xlUp).Offset(0, 1).Value = Time$ & Space(5) & Date$
    End Sub


    This will work across all sheets now.
    Regards,
    Rudi

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

    Re: Worksheet changes (XL 2000 SP3)

    Yep, that's easier.

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Worksheet changes (XL 2000 SP3)

    Thanks Hans and Rudi

    Having to put the code in each sheet would be a bit of a drag, but Rudi's code works beautifully.

    Will someone explain to me what the (ByVal Sh As Object, ByVal Target As Range) does?

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Worksheet changes (XL 2000 SP3)

    When the worksheet change event fires, Excel passes the sheet that was changed and the cell or cells that were changed in those two parameters. Otherwise, the code would only know that one or more cells somewhere in the workbook had changed.
    Legare Coleman

Posting Permissions

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