Change event problem when the user switches worksheet without pressing ENTER

[franklyn]

I have a workbook which involves the user switching between two worksheets. On Sheet 2 I am using dynamic data validation lists which the user can add to. The default list includes the option "Add new comment". When this is selected, a change event is triggered, [cell].ClearContents. The user then enters the new comment. Pressing enter will trigger another change event which compares the new comment to the default list and if it is missing, add it to the list. This process works.

The problem I am having is when the user enters a new comment but then switches worksheet without first pressing enter. The change event still triggers but it is ignoring the new comment, and comparing the cell in its ClearContents state with the default list. With some cells it is giving the ClearContents state a value of '0', but with others a value of 'L' and others a value of '-' !!!! The value of '0' ie, blank cell , I understand but the others?? I've tried a worksheet deactivate event, but it doesn't trigger until the change event has finished. I've also thought of a SendKey return but it returns on the new active window.

CAUSE IDENTIFIED. IF THE USER CHANGES WORKSHEET BEFORE PRESSING ENTER. THE CHANGE EVENT CODE WILL REFERENCE THE CELLS OF THE ACTIVE IE, NEW WORKSHEET.

[TMS]

Someone asked me recently if my crystal ball had failed ... seems it's out of service indefinitely.

You're listing no code and haven't uploaded a sample workbook.

I'm not sure what advice or testing you think we can do without any evidence.

Nice idea by the way.

Regards, TMS

[franklyn]

TMS, humour much appreciated I've added what I believe to be the appropriate code to my original post. Apologies for its naivety.

[TMS]

Sorry, but it would be easier to test in the context of a sample workbook. Only need the salient parts if it's sensitive.

Regards, TMS

[franklyn]

Happy to send you the workbook via email if OK with you. I guess I can do that via your forum membership details page.

[TMS]

It would be simpler to just upload it in the forum. Is there any reason why you can't upload a simplistic example of the workbook? No details are required, just the structure, the list and the cell with the Data Validation being monitored in the WS Change Event.

If you must, it's a hotmail dot com account ... although this is the NOT preferred option.

Regards, TMS

[franklyn]

OK, I'll prep a simplified version for upload. Best regards.