Worksheet_change doesn't fire when linked value changes ?
Worksheet_change doesn't fire when linked value changes ?
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Worksheet_change doesn't fire when linked value changes ?

  1. #1
    New Member
    Join Date
    Mar 2002
    Location
    Karlstad, Sweden
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    I Have a case where I want a macro to start whenever a certain cells changes, regardless of reason for cell change (i.e. user input or link change.
    According to Excel Help, Worksheet_Change() should be my ideal solution, but it doesn't seem to fire when the value in linked cells changes.
    Workbook_Calculate was another shot I tried, but I can't see an easy way to determine whether my cells of interest are changed or not.

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi

    This is fun

    Eventhough I might be wrong

    I do believe that links are governed by the Workbook and not individual sheet modules. Try this event here:
    Notice (((WORKBOOK)))_SheetChange

    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

    End Sub

  3. #3
    New Member
    Join Date
    Mar 2002
    Location
    Karlstad, Sweden
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Instant response !! Thanks !

    I have tried workbook_change() also. The problem is that it does not seem to trig on links either. Example: On Sheet1 I have a cell (A2) containing a link to a cell (B2) on Sheet2. Changing the value in cell B2 on Sheet2 does fire workbook_change, but the target is Sheet2!B2.
    I would need an event with target Sheet1!A2. But I cannot see that this happens.

    Have also tried linking to another workbook, and changing the source in that workbook, does not trig the event (in the first wkb) at all.

    Ok, I think I can solve my problem by using Worksheet_Calculate, but not without some dirty coding to determine if certain cell values are changed or not.

    Btw, are there any Excel settings that might be set to disable event on links ?

    /Claes

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    This is the help file from Office 2000

    SheetChange Event


    Occurs when cells in any worksheet are changed by the user or by an external link.

    Syntax

    Private Sub object_SheetChange(ByVal Sh As Object, ByVal Source As Range)

    object Application or Workbook. For more information about using events with the Application object, see Using Events with the Application Object.

    Sh A Worksheet object that represents the sheet.

    Source The changed range.

    Remarks

    This event doesn't occur on chart sheets.

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On your Worksheet Menu Bar
    Click Tools
    Options
    Calculation Tab

    Check your workbook Options at the bottom...

  6. #6
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Sorry, I misunserstood you

    internal links(calculations) do not set off any change events. You will have to trap it in the calculate event like you already figured...

  7. #7
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I tried what you are trying to do and the macro did fire when Sheet2 B2 was changed by Sheet1 A2

    Are you calling the macro correctly???

    If not, you just simply list the macro name in the calculate_event.

  8. #8
    New Member
    Join Date
    Mar 2002
    Location
    Karlstad, Sweden
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I appreciate Your help !

    Did you use Workbook_change ?
    My eventcode looks like this:
    -------------------
    Private Sub Workbook_SheetChange(ByVal objSheet As Object, ByVal rngTarget As Range)
    Dim wksSheet As Worksheet

    Set wksSheet = objSheet

    MsgBox ("Sheet:[" & wksSheet.Name & "] Range:[" & rngTarget.AddressLocal & "]")

    Set wksSheet = Nothing

    End Sub
    -------------------

    When I change the value in Sheet2!B2, the target in my event is Sheet2!B2.
    I would like to get an event where target is Sheet1!A2.

    /Claes



  9. #9
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Here is all I did

    In the sheet where the change is being trapped.

    Private Sub Worksheet_Calculate()
    MacroNameHere
    End Sub

    That should be all you have to do.

    Just in case were getting stuck.
    When a value is typed into a cell, you must exit the cell before any events or calculations occur

    A little more detail. I might have it backwards, but you'll get the jist of it

    Here is my calc link

    this formula is in cell Sheet2 B2
    =Sheet1!A2

    when i change the value in sheet1 A2 and exit the cell, sheet2 B2 calculates and runs your macro. The calculate event is in sheet 2?

    I hope this helps

  10. #10
    New Member
    Join Date
    Mar 2002
    Location
    Karlstad, Sweden
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Well, when using the Calculate event my problem is not to get it fired, but to find out if the cells of interest,let's say Col 1, has changed or not.

    The original case was to run a macro (that plays a sound), whenever certain cells (Column 1) is changed.

    By using the calculate event I guess I have do some coding to determine if cells are changed or not (i.e. save prev values in hidden column, and then compare upon calcuate event)

    Ok, this might be the only solution, but it would be nice to find an easier one ...

    /Claes

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com