Results 1 to 6 of 6
  1. #1
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Worksheet change (Excel 2000)

    Hi all,
    I have a Worksheet that I would like to add a value to a cell if there has been a change to the sheet, is there a way to figure out if the workbook has been changed?

    Thanks, Darryl.

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

    Re: Worksheet change (Excel 2000)

    Right click the sheet tab and select View Code from the context menu. You can create an event handler for the Worksheet Change event:

    Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    ' code goes here
    Application.EnableEvents = True
    End Sub

    The Target argument is a Range that tells you which cell or cells have changed. It is a good idea to disable the handling of other events temporarily to avoid getting caught in an endless loop.

    What code to use depends on what exactly you want.

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

    Re: Worksheet change (Excel 2000)

    Do you want to add one to a cell every time a change is made to the sheet? In other words, if I change cell A1, and then change cell A2 does one get added to the cell twice? If not, when do you want to do this?
    Legare Coleman

  4. #4
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Worksheet change (Excel 2000)

    Hi Legare,

    I've tried the code that Hans posted, I dont' think it works for me cause I'm already using that event with the intercept. and range. What I would like if the worksheet changes to have Sheets("Mysheet").range("A2").value=Sheets("Myshee t").range("A2").value + 1 - to indicate a revision in no matter what on the sheet.

    Thanks,
    Darryl.

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

    Re: Worksheet change (Excel 2000)

    You can add that line to the existing Worksheet_Change function, outside the part that checks the range.

  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 change (Excel 2000)

    Likie Hans said, you can add your line of code in the existing worksheet change event routing, just make sure that it is not inside any If statement that is checking for what changed. However, it is going to add one to that cell everytime anything changes on the worksheet.
    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
  •