DESPERATE!! EXCEL ate 650 hand-entered ActiveX textboxes by "Removed Feature: OLE..."

I'm sure this is not what you want to hear, but ActiveX controls are buggy on worksheets.

While you're pondering the detritus of this disaster, you might consider a different approach.

Without going down a rabbit hole, what are you doing that would even suggest that approach?

This is the plan... I first had to go back to the last copy and delete all textboxes from that worksheet. Checked it out. Made two copies. Made another copy on a stick drive.

On one of my previous posts I show a worksheet with every other row having a large merged cell (across C:G). At the bottom of that large area I wanted to insert a textbox that grabbed max of three full lines of "guidance" text for the soul that has to use the printed version. Textboxes weren't big enough and were cumbersome and you know the story with ActiveX Textboxes (Merde!).

My plan: I will find a macro to first insert a third row and format it so that it looks like nothing changed to the large merged block, which would involve resizing the currently odd numbered rows to maintain the overall look I was going for. I will then merge C:G to match that of its "sister cell" in the row immediately above. The only visible difference at this point should be the Excel row numbers. I will insert a formula into the merged cell to reference the appropriate text I was originally trying to put into text boxes from another worksheet ("Questions"). Next (hopefully with the same macro) I will copy the one instance of the new "improved" row into a third row (with formula). I may have to change the minor order of things and do this on multiple passes as I may have burned out my favorite brain cell with my earlier attempts.

For now, I'm searching for a macro to copy that one particular new, formatted row containing a formula and insert it into what will be the third row of a collection of 3 rows for each question. Finally, to keep the maintenance cleaner, I will insert a third row into the other worksheet that is the source of the questions (the content I was trying to insert into textboxes) to make cross-checking the formula references in the "Field Workbook" with the associated content in the "Questions" worksheet easier. It seems like it works doing a few manual test, so wish me luck!!

Just to close the loop and because I have too often run into threads that seemed like it was going to answer my question, then that door gets slammed in my face with the OP saying "Nevermind, I figured it out myself" with no solution or closure. Anyway, my "recovery" scheme, above, worked though it was a little more piecemeal than originally anticipated. Apparently, I had more scrap strewn about my worksheet that had crept in over the last few generations of "improvements" than should have been there. Nevertheless, I got all of my textboxes removed and replaced by a third row and now no limits to amount of text and no apparent glitchiness. For some reason I couldn't get rid of some of the cell outlines in the new rows so I just rolled with that punch and will forevermore claim that I meant for that to happen(!)

I included two macros, below, that allowed me to do what I wanted. I snatched them from across the web and don't know the original authors, though I did add a comment here and there to remind me what parts to change to get the desired effect. I set up a practice worksheet with the same form and format to observe the results then when I was satisfied I tested them on an extra copy of my worksheet. One curiosity about the macros is that for some reason, it (I can't remember which one or if it was both) stopped doing its business before it gets to the end of the file, so a half-way decent Excel coder can fix that, I'm sure. (It may have been something in my file like a limit to the scrolling area, some auto-running macro or some other such self-inflicted, nonsense.) Good luck!

Please Login or Register  to view this content.