How can I use the "Small Caps" Font option in an Excel speadsheet

[Guest]

In Word, one just formats the font and its a toggle box option, however, I
can't find this option in Excel. Problem is, we have it being used in some
spots (document is being updated, and this isn't the first time in Excel
2003), but when we change it, it changes away from the "Small caps" as well.

 

[Dave Peterson]

Excel doesn't have this kind of formatting.

Maybe you could search the web to find a font that does small caps. Remember
that if you share the workbook with others, they'll have to that font installed,
too.

But you could use a macro that actually changes the font size (character by
character):

http://groups.google.com/[email protected]

 

[Gord Dibben]

See Dave's post for more details.

This macro will give you small caps.

Attribution to Pat Finegan

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