change txtbx font

C

CR

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
 
T

Tom Ogilvy

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
 
B

Bob Phillips

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)
 
D

Dave Peterson

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
 
B

Bob Phillips

then see my response.

--

HTH

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

CR

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
 
M

mudraker

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
 
Top