freezing and passing SelectionChange event value

**Hammer_757** · 03-21-2012, 01:16 PM

I created a macro that adds the current date to a cell if anouther range has a change. I did this using the Worksheet_Change event. I also setup a list for the cells using data validation. If the user enters an incorrect value a warning occurs providing the option to continue or cancel. One problem is that the date cell updates even if the user cancels from a Data Validation Warning for an incorrect value entry, and makes no ACTUAL change. I want to have the date update only when a real change to the value is made. To prevent this I am trying to capture the Target cell value before the change and compare it to the value after the change.

I set the public variable selChgTgt in the Worksheet_SelectionChange event
and make the comparison, in the Worksheet_Change, right before calling the date change code.

However, the variable is being changed while the Worksheet_Change event is running even though it was set during the Worksheet_SelectionChange event. This is causing my test "If Not Target = selChgTgt Then" to always return "0" and exit.

Im attaching the workbook, if you care to look. For ease of testing, the area to make changes to is Input!$B$3:$B$1245 the date column is a few columns to the right.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

So far I cannot upload the file. I can email it if anyone is interested.
Thanks
Robert

**Hammer_757** · 03-22-2012, 09:50 AM

ive tried to upload the file several times. the status bar advances and looks like it complete but the file does not show up in the selection pane. It may be a network issue here at work. I will try to upload from home tonight.