Back

About MrExcel
Consulting Services
Learn Excel Resources
Challenge of the Month
MrExcel Seminars

Message Board

MrExcel Store
Podcast
Search
Contact
Home

 

 

Past Tip of the Day

 

Long-time readers will recognize a new look at MrExcel.com. MrExcel sends out thanks to Karen at Matrix Business Communications. Karen does writing, editing, document design & layout for clients around the world. She does excellent work at reasonable rates. If you have a document that could use some professional editing, contact Karen and tell her MrExcel sent you.

Bill writes: I have a document that has become very unstable. Every time that my mouse gets near one of the red comment triangles, Excel will crash.

After looking at Bill's worksheet, I found a somewhat innocuous macro that was causing the problem. In an effort to clear out some pictures and command buttons on the sheet, Bill had the following code:
For each sh in ActiveSheet.Shapes
    sh.Delete
Next sh

I set up a new test workbook and was able to duplicate the problem. Apparently, the yellow box that pops up when you hover over a cell's red comment indicator is a Shape. I ran the above code, this time asking for a MsgBox sh.Name. Sure enough, included on among the pictures and buttons were one shape for each comment. Deleting this shape left the worksheet in a corrupt condition.

The workaround for this book was to remove the sheet and insert a new one without the broken comments. I also suggested changing the macro to this:
&Nbsp;&Nbsp;&Nbsp;&Nbsp;If Not Left(sh.Name, 7) = "Comment" Then sh.Delete


By Bill Jelen on 14-Nov-2001

MrExcel.com Consulting can be hired to implement this concept, or many other cool applications, with your data.

MrExcel.com provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Visual Basic procedures on this web site are provided "as is" and we do not guarantee that they can be used in all situations.

 

Excel is a registered trademark of the Microsoft® Corporation.

All contents Copyright 1998-2008 by MrExcel Consulting.