Results 1 to 5 of 5
  1. #1
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts

    worksheet change event (Excel2003)

    If I use the worksheet change event to check if anything has changed within a small block of cells, say Block2 (as a named range), will the event trap just look at THIS block for changes or will it have to examine ALL changed cells on the sheet (to see if EACH changed cell is within the defined region?

    zeddy

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

    Re: worksheet change event (Excel2003)

    The Worksheet_Change event occurs whenever any cell in the worksheet is changed. You can use the Target argument to restrict the cells you want to execute code for:

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oCell As Range
    ' Check whether a cell in Block2 has changed
    If Not Intersect(Target, Range("Block2")) Is Nothing Then
    ' Turn off event handling temporarily
    Application.EnableEvents = False
    ' Loop through the changed cells within Block2
    For Each oCell In Intersect(Target, Range("Block2"))
    ' Your code goes here
    ...
    Next oCell
    ' Turn on event handling again
    Application.EnableEvents = True
    End If
    End Sub

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts

    Re: worksheet change event (Excel2003)

    Hi Hans

    Thanks for your speedy response.

    I am happy with being able to restrict the cells I want to execute code for.
    This was really a question about performance.
    I understand that Target refers to the changed cells.
    I am trying to understand what happens when thousands of cells change (e.g. as a result of formula recalc).

    For example if ten thousand cells change on a worksheet (in many ranges), but I am only interested in a very small range,

    zeddy

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts

    Re: worksheet change event (Excel2003)

    Hans

    Many thanks for clarifying this.
    It is now very clear.
    I shall be careful when using a range-pastespecial-values block operation.

    zeddy

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

    Re: worksheet change event (Excel2003)

    The Worksheet_Change event occurs when you manually change the value of cells, not when the value of cells changes as the result of recalculating formulas.

    If you change cells one by one, the Worksheet_Change event occurs for each individual change. Since computers work much faster than you can enter data, this shouldn't be a problem.
    If you change a range of cells in one action, for example by filling down, or by entering a value or formula with Ctrl+Enter, the Worksheet_Change event will occur only once.

    The Worksheet_Calculate event can be used to trap recalculation. Therefore, you should be very careful about code you write for this event.

Posting Permissions

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