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.
--
Darren

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

--
bhofset
-----------------------------------------------------------------------
bhofsetz's Profile: http://www.excelforum.com/member.php...fo&userid=1880
View this thread: http://www.excelforum.com/showthread.php?threadid=37985

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 wrote:

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.
--
Darren

--

Dave Peterson

On Thu, 16 Jun 2005 23:43:11 +0100, Dave Peterson
wrote:

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

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 wrote:

On Thu, 16 Jun 2005 23:43:11 +0100, Dave Peterson
wrote:

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

On Fri, 17 Jun 2005 03:50:30 +0100, Dave Peterson
wrote:

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

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

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

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

--
eugeni
-----------------------------------------------------------------------
eugenia's Profile: http://www.excelforum.com/member.php...fo&userid=2435
View this thread: http://www.excelforum.com/showthread.php?threadid=37985

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 wrote:

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

--

Dave Peterson

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

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 wrote:

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

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
On Fri, 17 Jun 2005 21:12:35 +0100, Dave Peterson
wrote:

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 wrote:

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




--
Using Opera's revolutionary e-mail client: http://www.opera.com/m2/

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 wrote:

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
On Fri, 17 Jun 2005 21:12:35 +0100, Dave Peterson
wrote:

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 wrote:

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


--
Using Opera's revolutionary e-mail client: http://www.opera.com/m2/

--

Dave Peterson

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


On Sat, 18 Jun 2005 06:08:53 +0100, Dave Peterson
wrote:

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 wrote:

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
On Fri, 17 Jun 2005 21:12:35 +0100, Dave Peterson
wrote:

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 wrote:

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


--
Using Opera's revolutionary e-mail client: http://www.opera.com/m2/




--
Using Opera's revolutionary e-mail client: http://www.opera.com/m2/

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 wrote:

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

On Sat, 18 Jun 2005 06:08:53 +0100, Dave Peterson
wrote:

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 wrote:

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
On Fri, 17 Jun 2005 21:12:35 +0100, Dave Peterson
wrote:

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 wrote:

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


--
Using Opera's revolutionary e-mail client: http://www.opera.com/m2/


--
Using Opera's revolutionary e-mail client: http://www.opera.com/m2/

--

Dave Peterson

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

On Sat, 18 Jun 2005 20:37:08 +0100, Dave Peterson
wrote:

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 wrote:

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

On Sat, 18 Jun 2005 06:08:53 +0100, Dave Peterson
wrote:

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 wrote:

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
On Fri, 17 Jun 2005 21:12:35 +0100, Dave Peterson
wrote:

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 wrote:

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


--
Using Opera's revolutionary e-mail client: http://www.opera.com/m2/


--
Using Opera's revolutionary e-mail client: http://www.opera.com/m2/




--
------------------
Darren