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

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

Hi,
Problem seems to exist in all versions I tested, 2000, XP & 2003.

Excel 2000 - the example works, until I cause the error on my
production sheet - after which the error occurs always.

Excel XP - Error doesn't occur everytime. But when it does, it crashes
Excel. (Open file by double click icon, with excel closed, so icon
launches excel)

Excel 2003 - Error occurs more readily. Sometimes shows error dialog
"MS Forms", sometimes crashes.

Error sometimetime requires precise steps to reproduce. For example I
found that on 2003, if I first change a cell without validation, to
show the popup, then change the cell with validation, the error
doesn't occur. (that's on my example sheet - on my production sheet
fails always)

To produce the error with the example - double click the file to open
(don't open from excel - as that seems to stop the error too.) Then
immediately change the cell with the validation to something from the
list. (Cell A3) Then tick (or untick) the box on the dialog. Then try
to close the dialog box.

I have put the file on my server for download - unfortunately I have
dynamic IP on my DSL service, so I don't know how long it will be
available on this IP address. But for a while at least you can get it
from here...
ftp://203.217.69.31/ftp/controlSourceError.xls

I have made the directory writable, so if anyone wants to offer a fix,
they can put it back there. (Don't replace my example please)

Thanks.

Ocker

I keep getting timed out on that ftp.

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

"Ocker" wrote in message
om...
Hi,
Problem seems to exist in all versions I tested, 2000, XP & 2003.

Excel 2000 - the example works, until I cause the error on my
production sheet - after which the error occurs always.

Excel XP - Error doesn't occur everytime. But when it does, it crashes
Excel. (Open file by double click icon, with excel closed, so icon
launches excel)

Excel 2003 - Error occurs more readily. Sometimes shows error dialog
"MS Forms", sometimes crashes.

Error sometimetime requires precise steps to reproduce. For example I
found that on 2003, if I first change a cell without validation, to
show the popup, then change the cell with validation, the error
doesn't occur. (that's on my example sheet - on my production sheet
fails always)

To produce the error with the example - double click the file to open
(don't open from excel - as that seems to stop the error too.) Then
immediately change the cell with the validation to something from the
list. (Cell A3) Then tick (or untick) the box on the dialog. Then try
to close the dialog box.

I have put the file on my server for download - unfortunately I have
dynamic IP on my DSL service, so I don't know how long it will be
available on this IP address. But for a while at least you can get it
from here...
ftp://203.217.69.31/ftp/controlSourceError.xls

I have made the directory writable, so if anyone wants to offer a fix,
they can put it back there. (Don't replace my example please)

Thanks.

Ocker

I got it to crash XL2003 twice of about 30 attempts. I couldn't get it to
crash XP or 2000. This spreadsheet doesn't really DO anything, so it leads
me to believe that the workbook is corrupted. However, I assume this is
just an example of a problem you're experiencing in a workbook that does
something. If you created this example workbook from scatch and you're
getting the same behavior, then that kind of throws the corrupt theory out
the window. If, on the other hand, you created this example from a copy of
the original workbook, the corruption could have followed it. One thing you
might try is round tripping it through XML. Save as XML, open the XM:L and
resave as Excel Workbook.

Since you're having problems in multiple versions, I guess that it's not
your Excel installation either and I doubt re-installing Excel would get you
anywhere. I couldn't break it in versions prior to 2003, but you could.
Just to be safe in this area, make sure you're up to date with officeupdate.

Sorry I couldn't be more help.


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


Dick Kusleika wrote:
Ocker

I keep getting timed out on that ftp.


"Ocker" wrote in message
om...
Hi,
Problem seems to exist in all versions I tested, 2000, XP & 2003.

Excel 2000 - the example works, until I cause the error on my
production sheet - after which the error occurs always.

Excel XP - Error doesn't occur everytime. But when it does, it crashes
Excel. (Open file by double click icon, with excel closed, so icon
launches excel)

Excel 2003 - Error occurs more readily. Sometimes shows error dialog
"MS Forms", sometimes crashes.

Error sometimetime requires precise steps to reproduce. For example I
found that on 2003, if I first change a cell without validation, to
show the popup, then change the cell with validation, the error
doesn't occur. (that's on my example sheet - on my production sheet
fails always)

To produce the error with the example - double click the file to open
(don't open from excel - as that seems to stop the error too.) Then
immediately change the cell with the validation to something from the
list. (Cell A3) Then tick (or untick) the box on the dialog. Then try
to close the dialog box.

I have put the file on my server for download - unfortunately I have
dynamic IP on my DSL service, so I don't know how long it will be
available on this IP address. But for a while at least you can get it
from here...
ftp://203.217.69.31/ftp/controlSourceError.xls

I have made the directory writable, so if anyone wants to offer a fix,
they can put it back there. (Don't replace my example please)

Thanks.