Ocker

What version of Excel are you using? I couldn't reproduce it with Excel
2000, so mail me the workbook and I'll take a look at it.

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

"Ocker" wrote in message
om...
This is driving me nuts, I think its a bug in Excel, but am happier to
shown the error of my ways. It is easily reproduced, and I have an
example xls that will demonstrate the error.

I have an error occurring when I use the worksheet_Change event to
show a dialog containing controls with ControlSource. The error only
occurs if the cell that was changed has a Data Validation list. (As
set in the Data-Validation menu)

The dialog contains a textbox and checkbox with a controlsource
pointing to cells located on a different worksheet from the one being
changed by the user (so no recursion issue here - event disabling does
not fix it).
I use the worksheet_Change event to determine if the cell being
changed is one that is required to show the dialog, and if so, show
it.

These cells that are used to make the dialog show contain a Data
Validation list - so the user can only select values from a
predetermind list of items.

When the dialog opens and I change the value of one of the controls
with a controlsource on the dialog, I get the error "Exception
Occurred" in a window titled "Microsoft Forms".

If I remove the Data Validation from the cell, to allow text entry, it
works perfectly. Or if I invoke the dialog with a button, it works
too. Only when the cell being changed has validation, does it fail.

It seems that a dialog containing controlsources will not run when the
cell that invoked the worksheet_Change event has a Data Validation
list on it.

I can work around by not using controlsource, and using a macro in the
"OK" button to set the cell values - but I'd prefer to understand why
this error occurs.

If you want to see it for yourself, I can mail an example.

Thanks