Data Validation error from Deleted Cells

**Lindleman** · 05-05-2010, 11:45 AM

I have a spreadsheet that has multiple data validation cells. Recently, I deleted a column that contained data validation. Now when I make an entry to the column to the right, I am getting validation errors that pertained to the deleted column of data. When I select the cell that is giving the error and check the data validation, it shows "any value" is checked. I have selected all of the cells in that column and cleared the validation, but I am still getting the validation errors. I think this is an Excel 2007 issue, but I cannot locate the data validation from the deleted cells. When I search for validation, the column is NOT highlighted, so Excel believes there is no validation in the cells, but I still get a popup message and the data is cleared. This is driving me crazy and I haven't found anyone else with this issue. I can provide the sheet if necessary.

**JBeaucaire** · 05-05-2010, 11:47 AM

Originally Posted by Lindleman

I can provide the sheet if necessary.

That would be a good start. Click GO ADVANCED and use the paperclip icon to post up your workbook.

**Lindleman** · 05-05-2010, 12:15 PM

Here is the sheet. I removed all of the data except for the cells in question. Curiously, if you delete a column to the left of the validation cells, it moves the validation to the next column to the right. Weird. I am wondering if it a problem with the named range that was deleted as well?

**Lindleman** · 05-05-2010, 12:18 PM

The data validation problem is in the Date column.

**JBeaucaire** · 05-05-2010, 12:48 PM

There is no data validation going on here. There is a Worksheet_Change macro active on this worksheet.

Right-click on the sheet tab and select VIEW CODE. You will find two macros inside the sheet module.

The Worksheet_Change macro is watching column 11 and looking at the column to the left of it. It's a VBA-based kind of validation. If you don't want this macro active, delete it.

There is also a Workbook_Open macro in this same module which should not be there. Either delete it or move the code to the ThisWorkbook module where it belongs.