Calling Worksheet Change Event Directly from VBA

[biocentrism]

Is there a way to call a Worksheet Change Event using code?

I want to have the Worksheet Change Event execute as if the event actually occurred.

I have tried:

Call Worksheet_Change

but of course this did not work.

I read online that this was not possible. But I neither believe nor like this answer.

Thank you.

 

[Michael M]

Yes, it can be done, but it's messy AND i'd consider other methods if possible
Couple of things to look at.

The question is generally answered here

http://www.mrexcel.com/forum/excel-...-worksheet_change-event-different-module.html

and Chip Pearson gives a description on why and how !!

Programming In The VBA Editor

 

[RoryA]

Why do you want to call it? That sounds like your code should be in a normal module and called from Worksheet_Change and from your current routine.

 

[biocentrism]

Why do you want to call it? That sounds like your code should be in a normal module and called from Worksheet_Change and from your current routine.

The reason I want to do this is in effort to resolve another issue which I posted here.
http://www.mrexcel.com/forum/excel-...r-up-down-buttons-multiple-event-firings.html

Basically, when I click the Spinner Button Event to increment the date, it somehow triggers 20+ Worksheet_Change Events. But when I trigger the Worksheet_Change Event directly by manually changing the date, everything works fine.

So I am thinking that if I have the Spinner Button Event call the Worksheet_Change Event then it will work without the 20+ triggers.

Does this make sense?

 

[biocentrism]

Yes, it can be done, but it's messy AND i'd consider other methods if possible
Couple of things to look at.

The question is generally answered here

http://www.mrexcel.com/forum/excel-...-worksheet_change-event-different-module.html

and Chip Pearson gives a description on why and how !!

Programming In The VBA Editor

Wow that is some nasty looking code! Yes I agree I would prefer to consider another method (just don't have any other ideas at the moment)...

 

[RoryA]

The code in that thread shouldn't trigger any events since you disabled them, but you didn't post the code for the routine it calls (UpdateDashboard).

 

[biocentrism]

The code in that thread shouldn't trigger any events since you disabled them, but you didn't post the code for the routine it calls (UpdateDashboard).

The other routine calls 3 other routines. But the routines are not relevant since these same routines are called by the Worksheet Change event.

You see whether I change the date manually or use a spinner to change the date, it results in the same validation and routines being called. What I cannot figure out is why a manual change works just fine and a Spin Event triggers 20 Change Events.

If the Routine when called by the Spin Event triggers 20 changes also triggered 20 changes when called by the Change Event, I would suspect there is something wrong with the Routine. But since the routine is the same, the problem must lie with the Spin Event?

I have also notice that sometimes when I click the Spin button once, it will trigger the Spin Button Click Events 2 to 5 times. I have a debug.print every time the Sub is called and for no apparent reason it machine gun fires 2-5 Button Click Events per click.

This is why I suspect there may be a glitch in the button.

Are the ActiveX buttons known to have problems?

 

[biocentrism]

The code in that thread shouldn't trigger any events since you disabled them, but you didn't post the code for the routine it calls (UpdateDashboard).

In thinking more about it, there must be something in the Routine which is triggering the Change_Event. But for some reason if the Routine is called within the Change_Event it must not be treated as a "Change", whereas, if it is called from somewhere else it is treated as a Change.

I don't know if this is correct but it seems intuitive to me.

If this is the case, then having the Spin Button Click event calling the Change_Event should solve the problem.

 

[biocentrism]

Ok I figured out the problem. It was the ActiveX Spinner Button. On a lark I tried using the Forms button (which unfortunately points up and down, not left and right). It works perfectly!

So it seems the ActiveX controls are unpredictable.

Wish I had tried this 2 days ago!

 

[RoryA]

ActiveX controls on worksheets have always been buggy. If you have to use them, I generally recommend you do not link them to cells directly (no LinkedCell/ListFilRange) since you'll often find their events triggered when you don't necessarily expect them to be. Was your Spinner linked to a cell?

To answer your original question here, yes you can call the Change event directly but you have to pass a Range object as parameter. Since your spinner was ActiveX and therefore its code is in the same worksheet module, you could simply use:

    Worksheet_Change Range("A1")

for example. From a normal module you could use:

    Application.Run "Sheet1.Worksheet_Change", Sheet1.Range("A1")

or you could make the change event routine public and call it directly.