Select Empty Text Boxes

Hi,

I have files where empty textboxes have been copied over tens if not hundreds of times, thereby slowing down the scrolling speed immensely.

I would be interested if there was a macro to

a) select all empty text boxes

b) select and delete all empty text boxes

thanks,
clint

@Martin,

That is brilliant. That is exactly wanted to accomplish. Thanks for the quick and efficient solution.

@Lewis

Thank you also for your quick response. However I was not able to get the code to work. I am a novice at Macros admittedly so I may have not executed the code correctly. I just go into the VBA Editor (Alt + F11) choose Insert>Module and then run the macro on each page. Suggestions?

You are correct.

1. Alt F11 to go to the VBA Editor.
2. If you don't see the project Tree - press 'CTRL R'.
3.You should see 'ThisWorkbook' under your Excel File Name. If you don't keep on clicking on '+' signs until you do.
4. Insert > Module (should get you Module 1).
5. Cut and paste the macros into Module 1.
6. There are two macros, that act on the entire WORKBOOK, not just one sheet. Select the one you want and run it:
a. CountActiveXTextBoxesInWorkbook()
b. RemoveActiveXTextBoxesFromWorkbook()

I hope this helps.

Lewis

Hi Lewis,

Thanks for your reply. However I fear that I am still doing something wrong. I am attaching a simple workbook that only includes blank text boxes. I pasted in the macro as you described [I think] and the result returns that there are no empty text boxes although I know there are 5-6.

-clint

PS - if I look at the code in the first post the "if" statement is looking for a value of " ". Again I am a way out of my league here but is the value in your code looking for " " or zero "0"?

Hi Clint,

Thanks for sticking with me. You did nothing wrong. There are two types of 'Text Boxes':
a. Active X
b. Forms

The code you tested was for the 'Active X' type.
Attached is code for the 'Forms' type, which you are using. That's why it's always good to post a sample workbook, so that code can be tested on your data.

In response to your question:

is the value in your code looking for " " or zero "0"?

Neither.

Please Login or Register  to view this content.

a. The of contents of the 'Text Box' is being read as text.
b. Leading and trailing spaces are removed using the 'Trim' function.
c. The length of variable 'sText' is being tested for being length 0 (= no text).

Lewis

Code follows:

Please Login or Register  to view this content.

Hi Lewis,

That is pretty slick. Based on your counter I had +1200 empty text boxes in my file (I had several copies of the same page)

However there is one item I may have neglected to note. Sometimes I will reference the contents of a cell so they display in the text box. I don't want these to be deleted. In the first solution provided these text boxes are NOT deleted.

Please Login or Register  to view this content.

However the code you provided does erase them. Is there a quick way to fix? If not no worries, you've been very helpful already.

-clint
ps - you are right I should have included an example from the start. Duly noted for next go around.

Hi Clint,

I never knew you could put a formula in a TextBox on a Worksheet. I would not have figured out how to do it without your sample worksheet.

Try the updated file which deletes a text box if it:
a. Contains no text AND
b. Contains no formula

Lewis

Please Login or Register  to view this content.

Hi Lewis,

That is amazing - works perfectly. The file is so much quicker to navigate through now. Thanks for your help.

-clint

PS - the text box formula comes in handy when you want to automatically update text on a chart. I just write a big formula joining cell references & text strings in quotes in one cell and then reference that cell with the text box. This way at month end I rarely have to update the notes on charts...its done automatically

Hi Clint,

It took a while, but I'm glad it finally worked out for you.

That is really a great tip about the 'Text Box' formulas. Thanks.

Lewis