Removing a text box that doesn't appear to be a text box

[nickharr]

i there - I'm having trouble deleting a "text box" from my worksheet. I have copied and pasted a large amount of information from a website - it has copied the information into one column but about every 10th cell it has inserted a box that looks like a text box but when I hover over the border I dont get to highlight the edges to then delete it. The cursor changes from the usual cross when I go from cell to cell but changes to an I when I come to the text boxes. I can add text into them but i just need to get rid of them. Any suggestions as how to do this would be most helpful.

 

[Alex Blakenburg]

When you hover over one of them and the cursor turns into an "I" click on the text box.
Then hover over the border of the text box and you should get the cross. Click with it on the border
Then hit Ctrl+A this should select ALL the text boxes
Hit Delete

Let me know if that works.

 

[Fluff]

To get rid of them & you have no other objects on the shhet
F5, Special, Objects, Ok. That will select all objects on the sheet & you can then delete them.

 

[Alex Blakenburg]

Another way is to hit Alt+F10 which will open up the object selection panel.
If you select one in the panel and then hit Ctrl+A it will select all of them there.
Then hit delete.

 

[nickharr]

Hi there - sorry not to have got back sooner but only just managed to get to the computer again. Many thanks for your suggestions but I'm still struggling! Fluff - I've tried pressing F5 but nothing is happening - I can't see Special, Objects etc. Alex - I've tried your first suggestion but when I hover the cursor near the border nothing changes until I'm into the cell next to it when it changes to a cross. If I click then, I'm just into the cell rather than the "text box". I've tried your second suggestion and select the sheet, press Alt+F10 and I see some numbers and letters appear along the top of the page - I press 1 and then CTRL+A but nothing happens. So I'm probably doing something wrong - I'm afraid you are dealing with a novice!

 

[Fluff]

You need to press the F10 function key whilst you holding down the Alt key

 

[nickharr]

Hi Fluff - when I press ALT+F10 I see numbers 1-4 appear as well as lots of letters all along the top but still don't know how to access the object selection panel.

 

[Alex Blakenburg]

I was just Googling to make sure that the Selection pane was available in your version and stumbled across the 5 min video below
Since it is 5 years old your Excel 2019 should definitely have it.
Instead of Alt+<F10> you can use Home>Find & Select > Selection Pane
(At 1min mark in video)

 

[nickharr]

Hi Alex - Many thanks for sending the video link - it has helped me find the Selection Pane! So the list contained 2 different objects - Autoshapes and Controls. The Autoshapes were easy to highlight and then delete but the Controls had a formula in them (=EMBED("Forms.HTML:Text.1","") - when I highlight one of these and press delete it appears to delete the formular but not the "text box". However I can "Hide" all of these Controls and the "text box" dissappears so that is fine. Once again, I really appreciate your time to help me.

 

[Alex Blakenburg]

No problem, happy to help.

You might want to give this code from Debra at Contextures a try to get rid of your Text Box
Ref: How to Create Excel Worksheet VBA Text Box ActiveX (and Remove All TextBoxes)

Sub RemoveAllTextBoxes()
    'remove all textboxes
    ' on active sheet
    Dim ws As Worksheet
    Dim oTB As Object
    Dim myID As String
    myID = "Forms.TextBox.1"
    Set ws = ActiveSheet
    For Each oTB In ws.OLEObjects
      If oTB.progID = myID Then
        oTB.Delete
      End If
    Next oTB
End Sub