Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    2 Star Lounger
    Join Date
    Jan 2007
    Posts
    123
    Thanks
    13
    Thanked 1 Time in 1 Post

    Are event macros triggerable only by certain kinds of actions (2003)

    From my experiments with event-driven macros – specifically those which are supposed to run whenever any change is made on a worksheet – I believe that “any change” means ONLY changes caused by actually typing in cells, but not changes caused by, for example, altering the contents of an input cell via a spinner control, nor changes caused by a formula. Does this sound correct?

    I ask because I use spinners and other controls a lot to determine model inputs. I would like to create event-driven macros which are triggered whenever a designated formula-filled cell or cells containing the model result changes as a result of using a spinner. If anyone knows how to do this I would be most grateful to know. Otherwise I guess the only workaround is to assign the macro in question to every spinner, which is not quite what I’m looking for.

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Lingyai,

    Try putting the code here:
    Code:
    Private Sub Spin1_Change()
    
    End Sub
    Of course change Spin1 to the name of your spin control.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. The Following User Says Thank You to RetiredGeek For This Useful Post:

    lingyai (2011-06-23)

  4. #3
    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
    Yes that is correct
    Events are triggered by a change to the contents, so a change to the value of a formula would not be expected to trigger a change since the formula is not being changed.

    Spinners, scrollbars, comboboxes in both FORMS and Control Toolbox (and even selections data validation) could be expected to trigger a change event, but for whatever reason, the MS programmers, deemed that they do not.

    It should be noted that RetiredGeek's response is not for controls on the Forms toolbar item. These only have the option to assign a macro, they are not event driven.

    Steve

  5. The Following User Says Thank You to sdckapr For This Useful Post:

    lingyai (2011-06-23)

  6. #4
    2 Star Lounger
    Join Date
    Jan 2007
    Posts
    123
    Thanks
    13
    Thanked 1 Time in 1 Post
    Thanks both of you. I thought I was losing my mind... (which miught still be happening, but this is VBA issue is not a symptom of it )

  7. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Data validation does trigger a change event, but the others don't - I think it is treated as a formula link effectively - unless you use code to assign the control value to the cell (which is my preference anyway, as I don't like binding controls to worksheets).
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #6
    2 Star Lounger
    Join Date
    Jan 2007
    Posts
    123
    Thanks
    13
    Thanked 1 Time in 1 Post
    By the way...this is probably not worthy of a seperate post, but it took me a while to work out how to make a change entered anywhere on a given sheet (except changes triggered by a control, as just discusssed) trigger a pre-existing macro which is in a standard VBA module. I had tried using the Call command within the code on the worksheet, which hadn't worked. Then I discovered that you should use the Run command instead. So, for example, if in a standard VBA module you have the macro "My-Macro", you should go to the worksheet in question, right click on the tab, choose View code, and enter the following:

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Run "My_macro"
    End Sub

    Seems to me a somewhat arbitrary distinction between Call and Run, but I guess that's what gives VBA its unique character....

  9. #7
    2 Star Lounger
    Join Date
    Jan 2007
    Posts
    123
    Thanks
    13
    Thanked 1 Time in 1 Post
    Tanks for clarifying that Rory

    By the way, what is "HTH"?

  10. #8
    2 Star Lounger
    Join Date
    Jan 2007
    Posts
    123
    Thanks
    13
    Thanked 1 Time in 1 Post
    Thanks for clarifying that Rory

    By the way, what is HTH?

  11. #9
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    HTH = Hope That Helps

    Run is not necessary unless the code is in a different workbook, or has been made private in some way. Call works from event code (though it is unnecessary) just as it does any other code.
    Regards,
    Rory

    Microsoft MVP - Excel

  12. #10
    2 Star Lounger
    Join Date
    Jan 2007
    Posts
    123
    Thanks
    13
    Thanked 1 Time in 1 Post
    Looks like I spoke too soon... the code using "Run" which I described a few posts above seems to be having some unwanted side effects!

    I noticed that after I inserted the code in the worksheet, I started doing some mundane tidying up on the worksheet. Specifically, I wanted to cut and paste a cell from one place to the empty cell above. Normally, as you know, when you select the cell to be moved and press Control + x, it appears activated i.e. has the "marching ants" border around it. It remains until you then either paste it somewhere or press "Esc". Well, this time, with the code in the worksheet, I selected the cell, pressed Control+x, and pressed the up arrow key to go to where I wanted to paste the cell. But as soon as I pressed the up arrow key, the cell which was to be moved became deactivated -- the marching ants diappared, and when I tried to paste, there was nothing to paste. It seems that the macro was triggered by the up arrow key, erasing the clipboard.

    Sigh... does this have to be so complicated? All I want is a macro which is triggered when the user makes a change, either by using a spinner (which I now know how to do) or enters a change the normal way (i.e. by typing somewhere and pressing Enter), while retaining basic Excel functionality, such as cutting and pasting. Ansy suggestions?

  13. #11
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    That should only happen if you have SelectionChange event code running. Also, you don't need Run as I mentioned (and better to avoid it)
    Regards,
    Rory

    Microsoft MVP - Excel

  14. #12
    2 Star Lounger
    Join Date
    Jan 2007
    Posts
    123
    Thanks
    13
    Thanked 1 Time in 1 Post
    Quote Originally Posted by rory View Post
    That should only happen if you have SelectionChange event code running. Also, you don't need Run as I mentioned (and better to avoid it)
    Unfortunately, I need (or believe I need) SelectionChange event code running

    Here is another strategy: is it possible in VBA to trigger a macro any time a specified cell(preferably a named cell ) recalculates, yielding a new result? As opposed to any time the specified cell is changed by typing in it?

  15. #13
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Not as such - there is a worksheet calculate event but it is not cell specific, nor does it tell you what changed. What you could to is link an activex control (e.g. a textbox) to the specific cell and use the change event of the control.
    Regards,
    Rory

    Microsoft MVP - Excel

  16. #14
    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
    Unfortunately, I need (or believe I need) SelectionChange event code running
    SelectionChange and Change code can severely limit excel features. As you noted it can clear the clipboard and it can also clear the UNDO stack, eliminating the undo feature in excel. You will have to balance the need for the events vs the desire for the other auto excel features, and the overhead of constantly running code...

    Steve

  17. #15
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    FYI copying cells can usually be worked around with a DataObject or locking the clipboard, but cutting is a problem.
    Regards,
    Rory

    Microsoft MVP - Excel

Page 1 of 2 12 LastLast

Posting Permissions

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