Results 1 to 4 of 4
  1. #1
    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

    Events not triggered with Forms control (Excel 2000, SP2)

    I am using some controls from the Forms toolbox directly on a worksheet. I then have that control tied to some cells that store the data and the result/selection. I see that when the output cell changes because the user makes a selection, it does not trigger any events. Is that normal?

    For example, I have a list box directly on a worksheet with the list data stored in cells on that same sheet. The user's selection is then reported to another cell which is then used in formula to make some decision. (This is all defined when you right-click on the control, select Format Control then go to the Control tab.)

    Even though there is code in all worksheet_change/selection events, they do not get triggered when Excel writes to that "output" cell. Up until now all my projects used ActiveX controls on forms, so this is new to me. Is this normal behavior? If so then it means I can't use VBA with embedded Form controls.

    Thnx, Deb <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Events not triggered with Forms control (Excel 2000, SP2)

    I tried a control from both sources (listbox) and replicated your problem (worksheet_Change event does not fire due to either of them).

    I guess your best bet is to use a form toolbar control with the Calculate event *or* put some event code behind a controls toolbox control (rightclick, view code):

    Option Explicit

    Private Sub ListBox1_Click()
    MsgBox "Hi, I changed"
    End Sub
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts

    Re: Events not triggered with Forms control (Excel 2000, SP2)

    I think your problem might be related to the bit
    "The user's selection is then reported to another cell which is then used in formula to make some decision"
    I seem to recall a known Excel problem with listbox linked cells being subsequently referenced by other cells which use 'volatile' formulas. (see knowledgebase Q211784)
    For example, if you use a cell to store the current listbox selection index (e.g 7 ) and then use that cell reference directly in a volatile formula elsewhere.
    To get around this problem when using the listbox controls on Forms, I make sure that there are no cells on the sheet that are directly dependant on the listbox ControlSource. I use VBA to 'paste' a copy of the listbox index value into another cell and then have my formula cells use THAT cell as the reference.

    Hope this helps.

    zeddy

  4. #4
    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: Events not triggered with Forms control (Excel 2000, SP2)

    Thanks for that tip but in my case, the events didn't work even when I didn't not have any formulas at all!

    I fixed the problem actually! I just went back to my tried and true ActiveX controls and placed them directly on the worksheet replacing the Forms equivalent controls. To my surprise they worked the same as if they were placed on a form. I had no idea this would work since I'd only used the ActiveX controls on forms. Way Cool!! Why didn't I try this before I posted this so called problem, I'll never know.

    Thnx much,
    Deb <img src=/S/cool.gif border=0 alt=cool width=15 height=15> <img src=/S/fanfare.gif border=0 alt=fanfare width=31 height=23>

Posting Permissions

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