Results 1 to 9 of 9
  1. #1
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Worksheet Change Event Doesn't work? (EXCEL 97)

    I wrote some code which basically triggers a macro if the user makes a selection from a cell-validation list-dropdown. I trap this via a Worksheet.Change Event and check in the code if indeed the cell containing the validation is changed (after which I run my special code).

    This works fine (of course...) in EXCEL 2000 +. However when a user reported that for him the code didn't seem to run, I found out that the event is NOT TRIGGERED in EXCEL97...

    This is what I found (in EXCEL97):
    * If I change any cell on the sheet, the event triggers
    * If I manually type-in one of the possible selections in the validated cell, the event triggers
    * If I use the drop-down in the validated cell (and make a different selection), <font color=red>the event does not trigger</font color=red>

    Obviously I can work around this but I was wondering if this behavior was known...

    Erik Jan

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

    Re: Worksheet Change Event Doesn't work? (EXCEL 97)

    Hi Erik Jan,

    Yes, this is a known problem in Excel 97 - see <!mskb=172832>Microsoft Knowledge Base Article 172832<!/mskb>.

  3. #3
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Worksheet Change Event Doesn't work? (EXCEL 97)

    That's quick... and yes, that is exactly what happens. Thanks for your reply!

  4. #4
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Worksheet Change Event Doesn't work? (EXCEL 97)

    OK, here I'm going on my face again... like I said in my original post "obviously I can work around this".... Well... it appears it is not so obvious at all, allow me to explain:

    Recapture:
    (1) I use a cell-validation drop-down list
    (2) I want action (VBA module) when the user changes something in that cell
    (3) A bug in EXCEL97 prevents the Worksheet_Change event to fire (see reply by Hans)

    So I thought, let's link any cell on the sheet to that "cell with the validation". If the latter is changed by the user, the linked one changes too and THAT change will fire the event.... WRONG: it appears that a change like that is not triggering the event ("only user changes and changes as a result of an external link").

    Therefore the question would be: how DO I work around this and make sure I do trigger the event???

    Un-related, but a way to a possible fix would be my problem with the sheet zoom-size and the character-size in a validation drop-down. When one uses a validation drop-down and when a lot of information needs to be visible on the sheet the zoom-factor is often low (e.g. 50%). Regretfully the fixed-font in the drop down will then soon become unreadable (because too small). Is there another way to do that?

    (of course another way might at the same time solve the issue above)

    Maybe I should just let the user click a button, then in my code present a dialog with a drop-down and go from there. Just am hoping there's another way.

    Erik Jan

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

    Re: Worksheet Change Event Doesn't work? (EXCEL 97)

    A UserForm may be the way to go. AFAIK, the font in the validation dropdown list can't be enlarged. So if you set the sheet to 50%, the font in the validation dropdown list will be shown at 50% too.

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Worksheet Change Event Doesn't work? (EXCEL 97)

    Another option:
    Link a cell to the validated cells (use a sum to capture a change in any of them) and trigger on a WORKSHEET CALC event.

    About the size in validation pulldown and small zoom:
    SInce excel won't let you enlarge the pulldown font, DON'T reduce the ZOOM:
    Reduce the font size of the workbook the col width and row size.

    Steve

  7. #7
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Worksheet Change Event Doesn't work? (EXCEL 97)

    I too have noticed the same problem you have where events aren't triggered (there are other cases where they aren't triggered either). I recommend using the listbox from the 'Form tool box' (actually the one in the Controls tool box will work too, they're similar but depending on your needs one is better than the other). <img src=/S/blackteeth.gif border=0 alt=blackteeth width=20 height=20>

    This listbox looks similar to the data validation list and you can pre-populate it with a list of items to be displayed. When the user selects one, the index to the item in the list (its position) is written to a cell of your choice. You can then use formulas to take action on that cell (e.g., if A14 = 3 then ... blah blah). Since writing the index of the selection to a cell will trigger the change event, you can continue to use your code as is (but now you're testing against a number not the actual string selected (I'm assuming the contents of the data validation list are strings but they don't have to be).

    Does that help? <img src=/S/boxedin.gif border=0 alt=boxedin width=25 height=20>

    Deb

  8. #8
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Worksheet Change Event Doesn't work? (EXCEL 97)

    Thanks for your reply (and that of the others). I have chosen to program the whole thing using a listbox in a UserForm afterall...

    Wrt your suggestion, that was indeed what I thought of first. However as far as I could test it, the fact that you write to a cell from your listbox did NOT trigger the event for me... that's why -in the end I did what I did <img src=/S/crazy.gif border=0 alt=crazy width=15 height=15> (and it works fine now)-

    Erik Jan

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Worksheet Change Event Doesn't work? (EXCEL 97)

    IMO, the BIGGEST difference between the 2 is that:
    FORMS items can NOT be changed to increase the font size (Control Toolboxes are much more customizable) so will have the same problem with zoom as data validation.

    FORMS items can be placed onto a chart, which can help make dynamic charts

    Steve

Posting Permissions

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