Strange Excel infinite-loop problem

[MidiMagic]

I teach a beginners class using Excel.

During an examination, some of my students misread the instructions (provided by the department, not by me). These are the kinds of errors only a novice could make. This caused the following two errors to occur.

- Instead of just putting data into a group of cells named the "constants table", some of them used the Data / Table function to create this table. We don't even teach about Data / Table, but they had just had tables in Word a few weeks earlier, and did what you do in word.

- The instructions used BASIC-style programming assignment statements to tell the students what kinds of formulas to put in the cells. The novices put in the entire assignment statement, instead of just the right side. This caused a circular reference, since the left side referred to the cell it was in.

- If these students made other errors, I don't know what they were. I can't replicate this trouble by trying to do it on purpose.

The infinite-loop problem happened when the student made BOTH of the first errors, plus some other error I can't think of. When they made one of the circular errors that also had a reference to the constants table, the following error messages occurred in sequence:

1. The circular error dialog box opened, put the cursor on the error cell, and opened it for editing. The colored indicators appeared, with one in the constants table.

2. The error dialog box closed, and a new error dialog box opened saying "Can't edit a table."

Any attempt to do anything anywhere on the worksheet after that caused the "Can't edit a table" error to pop up again. I couldn't find a way to get out of the loop, other than by using the Task Manager to close Excel. This destroyed all of the work the student did so far.

This happened to several different students.

Questions:

1. Has anyone else made Excel lock up like this?

2. Why does it get locked up?

3. Is there any way to get out of the loop without closing Excel?

[MidiMagic]

Nobody has any insights on this?

[MidiMagic]

Can someone at least explain the "Can't Edit a Table" error?

[MidiMagic]

Can someone at least explain the "Can't Edit a Table" error?

The message is actually: "Can't change part of a table."

[bobbyraw]

LOL @ midi

is funny you have to be replying to your own post, what version of excel are they using?

[MidiMagic]

Excel 2003

I have to reply because I can't edit, due to the upidstay time limit.

[bobbyraw]

ok, can you remove the table or edit the code the student put in the cell, i know you state that you would like to know how the error was created but you already have the answer. they enter VB codes in a table, probably link to some other cell. the code will run, but it will be pointing to the same cell the code is written in. hence the circular reference.

remeber in excell one cell can take ALOT of data, so a vb code in a table would be the same thing.

[MidiMagic]

The problem is that, when both errors occur together, I can't get rid of the repeating error message.

[MidiMagic]

I tracked it down.

First, you have to use Esc to undo the edit that started the trouble.

Then, you have to remove all of the cells in the erroneously created table at one time:

Use control ` to display formulas. Now you can see the affected range. Then select the entire range at once, and press Del.

[knutethegreat]

I was having a very similar problem with a data table in Excel 2010 with the message "Cannot change part of a data table." I am so glad that you found the solution. Thank you so much for posting :)

For those of you who didn't catch it, just press the esc button and then delete the table of affected cells.