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

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

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/

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