Small caps?

  • Thread starter Thread starter XP
  • Start date Start date
X

XP

Using Office 2003 and Windows XP;

Is there a way to get Arial font to appear in small caps in XL? If so, how?

Thanks much in advance...
 
If you don't have Arial small caps installed you could probably get it from the
'net.

Otherwise this macro will smallcap whatever text is in the selected cells.

Sub Small_Caps()
Dim o As Object
Dim sCap As Integer, _
lCap As Integer, _
I As Integer
Dim testStr As String
Application.ScreenUpdating = False
For Each o In Selection
With o
If Application.IsText(.Value) Then
lCap = .Characters(1, 1).Font.Size
sCap = Int(lCap * 0.85)
'Small caps for everything.
.Font.Size = sCap
.Value = UCase(.Value)
testStr = .Value
'Large caps for 1st letter of words.
testStr = Application.Proper(testStr)
For I = 1 To Len(testStr)
If Mid(testStr, I, 1) = UCase(Mid(testStr, I, 1)) Then
.Characters(I, 1).Font.Size = lCap
End If
Next I
End If
End With
Next o
Application.ScreenUpdating = True
End Sub


Gord Dibben MS Excel MVP
 
The macro you displayed works! Thank you for making it available. I am wondering, though, if it is possible to modify the macro so that the first letter of each word is NOT a "capital small caps" letter. That is, can each letter be made exactly the same size?

Thank you,

Chuck



Gord Dibben wrote:

If you don't have Arial small caps installed you could probably get it from
19-Jun-08

If you don't have Arial small caps installed you could probably get it from th
'net

Otherwise this macro will smallcap whatever text is in the selected cells

Sub Small_Caps(
Dim o As Objec
Dim sCap As Integer,
lCap As Integer,
I As Intege
Dim testStr As Strin
Application.ScreenUpdating = Fals
For Each o In Selectio
With
If Application.IsText(.Value) The
lCap = .Characters(1, 1).Font.Siz
sCap = Int(lCap * 0.85
'Small caps for everything
.Font.Size = sCa
.Value = UCase(.Value
testStr = .Valu
'Large caps for 1st letter of words
testStr = Application.Proper(testStr
For I = 1 To Len(testStr
If Mid(testStr, I, 1) = UCase(Mid(testStr, I, 1)) The
.Characters(I, 1).Font.Size = lCa
End I
Next
End I
End Wit
Next
Application.ScreenUpdating = Tru
End Su

Gord Dibben MS Excel MV


Previous Posts In This Thread:

Small caps?
Using Office 2003 and Windows XP

Is there a way to get Arial font to appear in small caps in XL? If so, how

Thanks much in advance...

If you don't have Arial small caps installed you could probably get it from
If you don't have Arial small caps installed you could probably get it from th
'net

Otherwise this macro will smallcap whatever text is in the selected cells

Sub Small_Caps(
Dim o As Objec
Dim sCap As Integer,
lCap As Integer,
I As Intege
Dim testStr As Strin
Application.ScreenUpdating = Fals
For Each o In Selectio
With
If Application.IsText(.Value) The
lCap = .Characters(1, 1).Font.Siz
sCap = Int(lCap * 0.85
'Small caps for everything
.Font.Size = sCa
.Value = UCase(.Value
testStr = .Valu
'Large caps for 1st letter of words
testStr = Application.Proper(testStr
For I = 1 To Len(testStr
If Mid(testStr, I, 1) = UCase(Mid(testStr, I, 1)) The
.Characters(I, 1).Font.Size = lCa
End I
Next
End I
End Wit
Next
Application.ScreenUpdating = Tru
End Su

Gord Dibben MS Excel MV



Submitted via EggHeadCafe - Software Developer Portal of Choice
Role-Based .NET Security without COM+
http://www.eggheadcafe.com/tutorial...95ec-fa9de8c57e2c/rolebased-net-security.aspx
 
Wouldn't that mean that you just used all caps in the cell and reduced the font
size?

You wouldn't need this macro at all.
 
Remove these lines...................

' Large caps for 1st letter of words.
testStr = Application.Proper(testStr)
For I = 1 To Len(testStr)
If Mid(testStr, I, 1) = UCase(Mid(testStr, I, 1)) Then
.Characters(I, 1).Font.Size = lCap
End If
Next I


Gord
 
Back
Top