Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Feb 2008
    Location
    Munster, Indiana, USA
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Worksheet_Change event (Excel 2003)

    I created a workbook that contains a worksheet_change event. The event is not the problem. The problem is even when I close the file the event wants to trigger in a different opened file. I have to exit Excel then start it again to fix the problem. Am I making any sense?

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

    Re: Worksheet_Change event (Excel 2003)

    Hi Alabmez, welcome to Woody's lounge.

    Two things to check here:
    1. Is the Worksheet_Change Event specified in the sheet module or in the workbook module?
    2. Do you have linked calculations that are being updated between the file with the event and other files you are opening/closing?

    It could be the case that by opening/closing files, there may be linked formulas that are updating and causing the event to fire. If the event is in the workbook module, it will fire irrespective of which worksheet has linked formulas.
    Regards,
    Rudi

  3. #3
    New Lounger
    Join Date
    Feb 2008
    Location
    Munster, Indiana, USA
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Worksheet_Change event (Excel 2003)

    The worksheet change event is in the sheet module. I opened up the file that contains the change event then did a Ctrl+N. As long as I do not activate the change event I can enter numbers in the new file as normal. However if I go to the file with the change event and trigger it, I can close that file and in the new file if I enter a number, I will get the info box asking me if I want to enable the macros from the closed file. Just to let you know, when it comes to VBA I know just enough to be dangerous.

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

    Re: Worksheet_Change event (Excel 2003)

    This is very strange - a Worksheet_Change event procedure should only run when a cell is changed on the sheet whose code module contains the code, not when a cell anywhere else - whether in the same workbook or in another workbook - is changed.

    Could you post a copy of the workbook? You can remove all data from the copy.

  5. #5
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Worksheet_Change event (Excel 2003)

    I suspect that something in the code downstream is somehow the cause. I would be inclined to insert the following two lines of code as the first code in the event procedure to test my theory.
    <pre> MsgBox "Event ""XXX"" has fired."
    Exit Sub</pre>

    Regards
    Don

  6. #6
    New Lounger
    Join Date
    Feb 2008
    Location
    Munster, Indiana, USA
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Worksheet_Change event (Excel 2003)

    The downstream theory is right on the money. I wrote a little multiplication exercise for a friend of mine. When an answer was entered in an answer cell, I did not want the cursor to advance down to the next cell unless the answer was correct. To do this I used this in the procedure... Application.OnKey "{ENTER}", "CURSOR" (I'm sure there is probably a better way)
    Application.OnKey "~", "CURSOR"
    Application.OnKey "{Return}", "CURSOR"

    At the end of the procedure I used ....Application.OnKey "{ENTER}" to undo the above.
    Application.OnKey "~"
    Application.OnKey "{Return}"

    Problem happened when I interupted the procedure before its conclusion. I also failed to put this into a Workbook before close just in case.
    This my first time in this type of forum and I must say the experience has been great. Thanks to all that came to the aid of this novice!

Posting Permissions

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