Rename Textbox on Worksheets

[Darren Hill]

I have several worksheets each of which have 4 identical textboxes.

I'd like to rename each of the textboxes (say, TB1, TB2, TB3, TB4) so I
can create code for all the sheets and not have to work with the unique
names each box has at present.
How do I do this, or is there a better way to access the textboxes (say,
by their underlying cell refs)?

Thanks in advance.

 

[bhofsetz]

When in design mode right click on the textbox and select propertie
from the pop-up menu. Then rename as desired.

If you have a few worksheets this won't be too difficult.

If you have many worksheets and want to rename all the textboxes the
you may want to do it with a bit of code to save you from having t
select and rename each individually

 

[Dave Peterson]

Are they textboxes from the Drawing toolbar? Or are they from the Control
toolbox toolbar?

If from the Forms toolbar:

Option Explicit
Sub testme01()

Dim TB As TextBox
Dim wks As Worksheet
Dim iCtr As Long

For Each wks In ActiveWorkbook.Worksheets
iCtr = 1
For Each TB In wks.TextBoxes
TB.Name = "TB" & iCtr
iCtr = iCtr + 1
Next TB
Next wks

End Sub

If from the Control toolbox toolbar:

Option Explicit
Sub testme02()

Dim OLEObj As OLEObject
Dim wks As Worksheet
Dim iCtr As Long

For Each wks In ActiveWorkbook.Worksheets
iCtr = 1
For Each OLEObj In wks.OLEObjects
If TypeOf OLEObj.Object Is MSForms.TextBox Then
OLEObj.Name = "TB" & iCtr
iCtr = iCtr + 1
End If
Next OLEObj
Next wks

End Sub

 

[Darren Hill]

Are they textboxes from the Drawing toolbar? Or are they from the
Control
toolbox toolbar?

The Drawing Toolbar. Are they the same as Forms? I tried both routines
below and both produced errors.
The forms one faulted at: "For each TB in wks.textboxes" with a type
mismatch. * (see below)

The Controls version faulted at "If TypeOf OLEObj.Object Is
MSForms.TextBox Then" saying "User-Defined Type Not Defined"

* In the Locals window, when i Clicked
wks+
to expand it to find what sheet (wks.name) it failed on, a very odd thing
happened. An Outlook wizard started, and it tried to create a mail
profile! What's going on here?

Darren

 

[Dave Peterson]

I have no idea why Outlook started--sounds pretty strange to me.

But since you used the textbox from the drawing toolbar, you can delete/ignore
the second routine.

But I'm not sure what: "are they the same as forms?" mean.

Your subject says the textboxes are on the worksheets. Are they really on
worksheets or are they on something else--maybe a dialog sheet or even a
userform?

======
I don't see what would generate the error, though.

Can you paste the code that you used--I'm guessing a minor typo was added
(maybe????).

 

[Darren Hill]

I have no idea why Outlook started--sounds pretty strange to me.

I've deleted Outlook and the problem has gone. Before I deleted it I tried
it on a couple of other spreadsheets - and whenever running code produced
a worksheet object (whether names wks, mysheet, blibble, or whatever), if
I clicked on that object in the Locals window, outlook was launched! Ah
well, it's gone now and good riddance.

But since you used the textbox from the drawing toolbar, you can
delete/ignore
the second routine.

But I'm not sure what: "are they the same as forms?" mean.

Your subject says the textboxes are on the worksheets. Are they really
on
worksheets or are they on something else--maybe a dialog sheet or even a
userform?

Yes, they are on worksheets. I was asking if the textboxes from the
Drawing toolbar were the same as the ones from the Forms Toolbar - I was
confused

======
I don't see what would generate the error, though.

Can you paste the code that you used--I'm guessing a minor typo was added
(maybe????).

Here's the code. (Option Explicit is in the module)
Sub testme01()

Dim TB As TextBox
Dim wks As Worksheet
Dim iCtr As Long

For Each wks In ActiveWorkbook.Worksheets
iCtr = 1
For Each TB In wks.TextBoxes
TB.Name = "TB" & iCtr
iCtr = iCtr + 1
Next TB
Next wks

End Sub

In stepping through it, the routine nicely skips the sheet which has no
textboxes, then in the next sheet, stalls at the first textbox: Run Time
error '13' - Type Mismatch.

I've just performed the following test:
I added two textboxes to the sheet that didn't have any and stepped
through - it worked fine for that sheet but stumbled at the next.
On that next sheet, I deleted all the existing textboxes, and created a
couple of new ones. I stepped through and still it failed on this sheet.
I tested the other sheets to make sure it was failing on them and it
wasn't just a problem with the one sheet.

So I seem to have several sheets where, when I add textboxes to them, they
don't get recognised as textboxes?

Darren

 

[Darren Hill]

As an update, when I use
ActiveSheet.Shapes("Text Box 22").Name = "TB1"

it works, but this is something I have to do manually - because the text
boxes in every sheet have different numbers.

Darren

 

[Darren Hill]

I found when I renamed the following line:

Dim TB As TextBox
To

Dim TB

It worked.
I also found I had two extra textboxes - I had two callouts, which I
didn't realise where Textboxes. Does this explain the error?

Is it a case where the silly OP didn't give you all the info you needed?

Darren

 

[eugenia]

I think you can use the textbox rename from the control toolbox sinc
you do not have much toolbox..

 

[Dave Peterson]

Yep. That explains the error.

And I'm not sure I would have guessed that callouts would have caused the error
without testing it.

 

[Darren Hill]

Well, despite a silly OP, we got there in the end (though I blame you,
Dave, for not being a good enough mind reader).
Thanks for you help

Darren

 

[Dave Peterson]

Or close to the end...

Option Explicit
Sub testme()
Dim wks As Worksheet
Dim shp As Shape
Dim iCtr As Long

Set wks = ActiveSheet
iCtr = 0
For Each shp In wks.Shapes
If shp.Type = msoTextBox Then
iCtr = iCtr + 1
shp.Name = "TB" & iCtr
End If
Next shp

End Sub


might be a safer way to get to the textboxes.

 

[Darren Hill]

Oh yes, I can see that looks safer.
Excellent - thanks

If I wanted to also catch the callouts (which, to be honest, I'd forgotten
were there before that error), and rename them via a different scheme, how
would I alter it?
I tried the following (I was optimistic!) - what should I use instead of
msoCallout?

Aha - quick test before posting: I replaced msoCallout with msoAutoshape
and it worked - a little odd. Although the Callouts did come from the
Autoshape tool on the Drawing toolbar. Is there another type of Callout?
I'm just wondering why this worked but the msoCallout line didn't.

Option Explicit
Sub testme()
Dim wks As Worksheet
Dim shp As Shape
Dim iCtr As Long, jCtr as long

Set wks = ActiveSheet
iCtr = 0: jCtr = 0
For Each shp In wks.Shapes
If shp.Type = msoTextBox Then
iCtr = iCtr + 1
shp.Name = "TB" & iCtr
End If
If shp.Type = msoCallout Then
jCtr = jCtr + 1
shp.Name = "Call" & jCtr
End If
Next shp

End Sub

Darren

 

[Dave Peterson]

That seems utterly reasonable.

But it's not quite true.

There are some callouts that are still autoshapes, but have an .autoshapetype of
msoShapeRoundedRectangularCallout (for example).

Next time you're in the VBE, hit F2 to see the objectbrowser.

Search for msoautoshapetype.

You'll see lots of constants that can be used in the autoshapetype. And lots of
them have CallOut in their name. I didn't see anyway to group callouts except
by actually using all their constants.

 

[Darren Hill]

There are some callouts that are still autoshapes, but have an

.autoshapetype of
msoShapeRoundedRectangularCallout (for example).

That's a snappy name. Just rolls off the tongue, doesn't it?
And wow, there are a lot of them!
Thanks for illuminating this. I have a question, but it's not that
important if you have other people to help.
In your macro, when I type in the sequence:

For Each shp In wks.Shapes
If shp.Type =

I get a list of possible shape types. The first one is msoAutoshape, the
second is msoCallout. I just wonder which objects that msoCallout actually
applies to?

Darren

 

[Dave Peterson]

Excellent question and when you have time, you can experiment!

But I thought the weird part was that I used the
drawing toolbar|Autoshapes button|Callout category
and dropped a few onto the worksheet.

They didn't all have the same type.

I added all 20 shapes that appear on that callout toolbox.

Then I ran this code:

Option Explicit
Sub testme()
Dim shp As Shape

Debug.Print "Name--Type--AutoShapeType"
For Each shp In ActiveSheet.Shapes
Debug.Print shp.Name & "--" & shp.Type & "--" & shp.AutoShapeType
Next shp

End Sub

Name--Type--AutoShapeType
AutoShape 1--1--105
AutoShape 2--1--106
AutoShape 3--1--107
AutoShape 4--1--108
AutoShape 5--2--109
AutoShape 6--2--110
AutoShape 7--2--111
AutoShape 8--2--112
AutoShape 9--2--113
AutoShape 10--2--110
AutoShape 11--2--111
AutoShape 12--2--116
AutoShape 13--2--117
AutoShape 14--2--118
AutoShape 15--2--119
AutoShape 16--2--120
AutoShape 17--2--121
AutoShape 18--2--122
AutoShape 19--2--123
AutoShape 20--2--124

msoAutoShape is an excel VBA constant for 1.
msoCallOut is an excel VBA constant for 2.

I guess my suggestion would be to know what's on the sheet or do lots of
testing.

 

[Darren Hill]

Thanks for that testing - that's true diligence!
Your findings are very weird.
Yes, I'll probably play around with some of the other things on the
drawing toolbar as well to see what they come out as, just for fun.

Darren