Sub behiving different with different trigger type

[foxguy]

I have a Worksheet_Change that crashes when the user manually changes a cell value, but doesn't crash if a macro changes the cell value.

I have a cell (Range("SelectMtg")) with Data Validation:List. When a selection is made the Worksheet_Change() checks to see if the data has been saved and if it's saved it then copies the Target.Value into another cell (Range("MtgSelected")). If the data hasn't been saved it reverts Target.Value back to it's prior value.

I also have a Form Button on the sheet that runs a macro that locates the next Mtg in the list and replaces .Range("SelectMtg") with the next Mtg.

During normal data entry the button is used to set up the next Mtg, but the dropdown picklist allows the user to select a specific Mtg when necessary. This system has been working for a few months with no problem.

Now today the Sub crashes with an error code 1004 "Application or Object-Defined Error".
But only if the user changed the value manually by picking from the dropdown. If the user clicks the button it works fine.

This file is not mine to post but here a simplified version of Worksheet_Change().

Please Login or Register  to view this content.

The macro attached to the button is basically this

Please Login or Register  to view this content.

Is there some reason that a sub would crash when the user changes a value, but doesn't crash when a different sub changes the value?

[Leith Ross]

Hello foxguy,

The worksheet change event will fire each time a cell value is changed on the worksheet. This will cause a cascade stack overflow. You need to stop Excel from responding to events until your macro in the change event has finished. You can do this using the Application.EnableEvents method. Set the value to false before your macro runs and then set it back to true before you exit the macro.

Please Login or Register  to view this content.

[foxguy]

Leith;
You're right, but that's not my problem.

I overlooked putting in my post. I was typing it in the thread and leaving out all the stuff that doesn't affect anything, and didn't realize I left out the EnableEvents=False
I put it in MtgButton by mistake.
I'm editing my post to reflect the reality.

[Leith Ross]

Hello foxguy,

Chan eil mi 'gad thuigsinn.

[foxguy]

I found what was causing the problem, although I have no idea how or why.
I have been playing around trying to create a Non Volatile Dynamic Range Name that allows the range to be Cut & Pasted. http://www.excelforum.com/excel-work...mic-range.html. The only way I have discovered is to put a UDF() in the Range Name. Since that was the only thing I had changed in the workbook, I decided to get rid of all of the Range Names with a UDF() in the RefersTo, and my problem went away.

[Leith Ross]

Hello foxguy,

Glad to know you got it sorted out. Thanks for sharing your findings.