change txtbx font
 

I have a workbook with 13 sheets, each sheet has 25 to 30 textboxes.
I would like to run a macro that will search the sheets, find the textboxes
and change the font size. As usual I have had no luck coming up with one on
my own.
Can anyone help?

Thanks
CR

change txtbx font
 

What kind of Textboxes

for activeX textboxes

Sub AAAA()
Dim oObj As OLEObject
For Each oObj In ActiveSheet.OLEObjects
If TypeOf oObj.Object Is MSForms.TextBox Then
oObj.Object.Font.Size = 12
End If
Next

End Sub


--
Regards,
Tom Ogilvy



"CR" wrote in message
...
I have a workbook with 13 sheets, each sheet has 25 to 30 textboxes.
I would like to run a macro that will search the sheets, find the
textboxes
and change the font size. As usual I have had no luck coming up with one
on
my own.
Can anyone help?

Thanks
CR

change txtbx font
 

If perchance they are drawing object textboxes then you could use

Sub BBBB()
Dim oObj As Shape
For Each oObj In ActiveSheet.Shapes
If oObj.Type = msoTextBox Then
oObj.Select
Selection.Font.Size = 12
End If
Next

End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Tom Ogilvy" wrote in message
...
What kind of Textboxes

for activeX textboxes

Sub AAAA()
Dim oObj As OLEObject
For Each oObj In ActiveSheet.OLEObjects
If TypeOf oObj.Object Is MSForms.TextBox Then
oObj.Object.Font.Size = 12
End If
Next

End Sub


--
Regards,
Tom Ogilvy



"CR" wrote in message
...
I have a workbook with 13 sheets, each sheet has 25 to 30 textboxes.
I would like to run a macro that will search the sheets, find the
textboxes
and change the font size. As usual I have had no luck coming up with one
on
my own.
Can anyone help?

Thanks
CR

change txtbx font
 

And without the selecting:

Option Explicit
Sub BBBB2()
Dim oObj As Shape
For Each oObj In ActiveSheet.Shapes
If oObj.Type = msoTextBox Then
oObj.DrawingObject.Font.Size = 12
End If
Next oObj
End Sub

And just using the textbox collection.
Sub ccccc()
Dim myTB As TextBox
Dim wks As Worksheet
For Each wks In ActiveWorkbook.Worksheets
For Each myTB In ActiveSheet.TextBoxes
myTB.Font.Size = 12
Next myTB
Next wks
End Sub

And I could even get them all at once:

Sub ccccc2()
Dim wks As Worksheet
For Each wks In ActiveWorkbook.Worksheets
wks.TextBoxes.Font.Size = 12
Next wks
End Sub

Bob Phillips wrote:

If perchance they are drawing object textboxes then you could use

Sub BBBB()
Dim oObj As Shape
For Each oObj In ActiveSheet.Shapes
If oObj.Type = msoTextBox Then
oObj.Select
Selection.Font.Size = 12
End If
Next

End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Tom Ogilvy" wrote in message
...
What kind of Textboxes

for activeX textboxes

Sub AAAA()
Dim oObj As OLEObject
For Each oObj In ActiveSheet.OLEObjects
If TypeOf oObj.Object Is MSForms.TextBox Then
oObj.Object.Font.Size = 12
End If
Next

End Sub


--
Regards,
Tom Ogilvy



"CR" wrote in message
...
I have a workbook with 13 sheets, each sheet has 25 to 30 textboxes.
I would like to run a macro that will search the sheets, find the
textboxes
and change the font size. As usual I have had no luck coming up with one
on
my own.
Can anyone help?

Thanks
CR





--

Dave Peterson

change txtbx font
 

I'm Sorry, they are textboxes from the Drawing toolbar

Thanks
CR


"Tom Ogilvy" wrote in message
...
What kind of Textboxes

for activeX textboxes

Sub AAAA()
Dim oObj As OLEObject
For Each oObj In ActiveSheet.OLEObjects
If TypeOf oObj.Object Is MSForms.TextBox Then
oObj.Object.Font.Size = 12
End If
Next

End Sub


--
Regards,
Tom Ogilvy



"CR" wrote in message
...
I have a workbook with 13 sheets, each sheet has 25 to 30 textboxes.
I would like to run a macro that will search the sheets, find the
textboxes
and change the font size. As usual I have had no luck coming up with one
on
my own.
Can anyone help?

Thanks
CR

change txtbx font
 

then see my response.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"CR" wrote in message
...
I'm Sorry, they are textboxes from the Drawing toolbar

Thanks
CR


"Tom Ogilvy" wrote in message
...
What kind of Textboxes

for activeX textboxes

Sub AAAA()
Dim oObj As OLEObject
For Each oObj In ActiveSheet.OLEObjects
If TypeOf oObj.Object Is MSForms.TextBox Then
oObj.Object.Font.Size = 12
End If
Next

End Sub


--
Regards,
Tom Ogilvy



"CR" wrote in message
...
I have a workbook with 13 sheets, each sheet has 25 to 30 textboxes.
I would like to run a macro that will search the sheets, find the
textboxes
and change the font size. As usual I have had no luck coming up with
one
on
my own.
Can anyone help?

Thanks
CR

change txtbx font
 

Sorry Bob, must be my server or something but all I see for this thread is
Tom's post, the two from me and this one from you. Would you mind reposting?

Thanks
CR



"Bob Phillips" wrote in message
...
then see my response.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"CR" wrote in message
...
I'm Sorry, they are textboxes from the Drawing toolbar

Thanks
CR


"Tom Ogilvy" wrote in message
...
What kind of Textboxes

for activeX textboxes

Sub AAAA()
Dim oObj As OLEObject
For Each oObj In ActiveSheet.OLEObjects
If TypeOf oObj.Object Is MSForms.TextBox Then
oObj.Object.Font.Size = 12
End If
Next

End Sub


--
Regards,
Tom Ogilvy



"CR" wrote in message
...
I have a workbook with 13 sheets, each sheet has 25 to 30 textboxes.
I would like to run a macro that will search the sheets, find the
textboxes
and change the font size. As usual I have had no luck coming up with
one
on
my own.
Can anyone help?

Thanks
CR

change txtbx font
 

How about using google:

http://groups.google.com/groups?thre...C9C8%40msn.com



CR wrote:

Sorry Bob, must be my server or something but all I see for this thread is
Tom's post, the two from me and this one from you. Would you mind reposting?

Thanks
CR

"Bob Phillips" wrote in message
...
then see my response.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"CR" wrote in message
...
I'm Sorry, they are textboxes from the Drawing toolbar

Thanks
CR


"Tom Ogilvy" wrote in message
...
What kind of Textboxes

for activeX textboxes

Sub AAAA()
Dim oObj As OLEObject
For Each oObj In ActiveSheet.OLEObjects
If TypeOf oObj.Object Is MSForms.TextBox Then
oObj.Object.Font.Size = 12
End If
Next

End Sub


--
Regards,
Tom Ogilvy



"CR" wrote in message
...
I have a workbook with 13 sheets, each sheet has 25 to 30 textboxes.
I would like to run a macro that will search the sheets, find the
textboxes
and change the font size. As usual I have had no luck coming up with
one
on
my own.
Can anyone help?

Thanks
CR









--

Dave Peterson

change txtbx font
 

This is Bob's response



If perchance they are drawing object textboxes then you could use

Sub BBBB()
Dim oObj As Shape
For Each oObj In ActiveSheet.Shapes
If oObj.Type = msoTextBox Then
oObj.Select
Selection.Font.Size = 12
End If
Next

End Su

--
Message posted from http://www.ExcelForum.com

change txtbx font
 

Doh, that worked. And so did both replies.

Thanks All



"Dave Peterson" wrote in message
...
How about using google:

http://groups.google.com/groups?thre...C9C8%40msn.com



CR wrote:

Sorry Bob, must be my server or something but all I see for this thread
is
Tom's post, the two from me and this one from you. Would you mind
reposting?

Thanks
CR

"Bob Phillips" wrote in message
...
then see my response.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"CR" wrote in message
...
I'm Sorry, they are textboxes from the Drawing toolbar

Thanks
CR


"Tom Ogilvy" wrote in message
...
What kind of Textboxes

for activeX textboxes

Sub AAAA()
Dim oObj As OLEObject
For Each oObj In ActiveSheet.OLEObjects
If TypeOf oObj.Object Is MSForms.TextBox Then
oObj.Object.Font.Size = 12
End If
Next

End Sub


--
Regards,
Tom Ogilvy



"CR" wrote in message
...
I have a workbook with 13 sheets, each sheet has 25 to 30
textboxes.
I would like to run a macro that will search the sheets, find
the
textboxes
and change the font size. As usual I have had no luck coming up
with
one
on
my own.
Can anyone help?

Thanks
CR









--

Dave Peterson