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

On Thu, 19 Jun 2008 06:25:00 -0700, XP wrote:

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...

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

On Thu, 19 Jun 2008 06:25:00 -0700, XP wrote:

Previous Posts In This Thread:

On Thursday, June 19, 2008 9:25 AM
X wrote:

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...

On Thursday, June 19, 2008 1:44 PM
Gord Dibben wrote:

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

On Thu, 19 Jun 2008 06:25:00 -0700, XP wrote:


Submitted via EggHeadCafe - Software Developer Portal of Choice
Role-Based .NET Security without COM+
http://www.eggheadcafe.com/tutorials...-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.



Chuck, Smith wrote:

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

On Thu, 19 Jun 2008 06:25:00 -0700, XP wrote:

Previous Posts In This Thread:

On Thursday, June 19, 2008 9:25 AM
X wrote:

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...

On Thursday, June 19, 2008 1:44 PM
Gord Dibben wrote:

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

On Thu, 19 Jun 2008 06:25:00 -0700, XP wrote:

Submitted via EggHeadCafe - Software Developer Portal of Choice
Role-Based .NET Security without COM+
http://www.eggheadcafe.com/tutorials...-security.aspx

--

Dave Peterson

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

On Thu, 04 Feb 2010 07:48:18 -0800, Chuck Smith wrote:

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

On Thu, 19 Jun 2008 06:25:00 -0700, XP wrote:

Previous Posts In This Thread:

On Thursday, June 19, 2008 9:25 AM
X wrote:

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...

On Thursday, June 19, 2008 1:44 PM
Gord Dibben wrote:

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

On Thu, 19 Jun 2008 06:25:00 -0700, XP wrote:


Submitted via EggHeadCafe - Software Developer Portal of Choice
Role-Based .NET Security without COM+
http://www.eggheadcafe.com/tutorials...-security.aspx