Results 1 to 5 of 5
  1. #1
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Worksheet Chang event (97)

    I try to run a macro when a cell change. I use the on Worksheet Change event and check the address of the target parameter. The event trigger when I change the cell with the keyboard, but when I pick a value form the validation list with the mouse, the event don't trigger. In Excel 2000 it's working like it should. Is this a bug (or MS-feature) is Excel 97 ?
    Francois

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Worksheet Chang event (97)

    This is not working. It give the same result.
    In the help file they say that the change event doesn't occur when cells change during recalculation.
    I can use the Calculate event, but then I have no possibility to check what cell is changing and the macro will run on every change made in the sheet and that's not the intention.
    Francois

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

    Re: Worksheet Chang event (97)

    Your observation is correct. You can work around it by referring to the value of the cell in another cell, and checking that cell in the Worksheet_Change event.

    Say that the cell with validation is A1. In another cell, say B1, enter the formula =A1. You can hide (the column with) B1 if you like. Check B1 in the code.

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

    Re: Worksheet Chang event (97)

    Sorry, that should have been the Worksheet_Calculate event - not very attractive, as you indicate.

    Perhaps you can use a (hidden) text box from the Control Toolbox linked to the cell will validation, and the On Change event of the text box.
    Or use a combo box instead of validation (more code)

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Worksheet Chang event (97)

    Hans,

    Ok that's working.
    I had problems because no events were triggered and have to find out that events don't when you are in design mode and I have to exit design mode.
    Learning the hard way.

    Manny thanks
    Francois

Posting Permissions

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