I have a macro using the Worksheet_Change [Ws_Chg] event, which looks to see if the cell changed is in a particular range.
The macro turns off Events, and calculation on the Active and Other sheet.
It stores a copy of the new value (or empty cell if the contents are deleted) to a cell on another sheet as a criteria for a data filter.
Then it should do the following:
If the changed cell is in the particular range, then
If the target cell was cleared, then the cell to the right is cleared & locked
Else the cell to the right is unlocked and cleared
When I clear the target cell, the Worksheet_Change macro seems to run OK.
However when add/change/reenter a value in the target cell, the Ws_Chg macro runs but once it stores the value to the other sheet, a UDF on the active sheet then runs and when it finishes, the main macro stops rather than returns back to the position in the macro when it was invoked (so it never gets to the unlock/clear code and the code to turn back on Events and Calculation (of both the active and other sheets).
I'm at a loss as to why this is happening. I can't stop the UDF from being called when a non-empty value is stored to the 2nd sheet, and can't get the main macro to continue once the UDF kicks in.
I've not pasted my code as it's a bit difficult to understand out of context.
I'd be very appreciative of any solutions or helpful suggestions please. I've had this sort of problem at other times so if I can get a solution to this one, I'll be able to reapply it later.
Many thanks. PJ
The macro turns off Events, and calculation on the Active and Other sheet.
It stores a copy of the new value (or empty cell if the contents are deleted) to a cell on another sheet as a criteria for a data filter.
Then it should do the following:
If the changed cell is in the particular range, then
If the target cell was cleared, then the cell to the right is cleared & locked
Else the cell to the right is unlocked and cleared
When I clear the target cell, the Worksheet_Change macro seems to run OK.
However when add/change/reenter a value in the target cell, the Ws_Chg macro runs but once it stores the value to the other sheet, a UDF on the active sheet then runs and when it finishes, the main macro stops rather than returns back to the position in the macro when it was invoked (so it never gets to the unlock/clear code and the code to turn back on Events and Calculation (of both the active and other sheets).
I'm at a loss as to why this is happening. I can't stop the UDF from being called when a non-empty value is stored to the 2nd sheet, and can't get the main macro to continue once the UDF kicks in.
I've not pasted my code as it's a bit difficult to understand out of context.
I'd be very appreciative of any solutions or helpful suggestions please. I've had this sort of problem at other times so if I can get a solution to this one, I'll be able to reapply it later.
Many thanks. PJ
