Automatic proper capitalization in EXCEL

Microsoft Excel 2003 (full product)
April 22, 2010 at 07:54:51
Specs: Windows XP
I want the text entries that I make in an entire Excel worksheet automatically appear in proper case (1st letter of each word capitalized). Is it possible to do this?

See More: Automatic proper capitalization in EXCEL

Report •

#1
April 22, 2010 at 08:05:57
It can be done 2 ways:

You can use the PROPER function in another cell:

=PROPER(A1)

Or you could use a Worksheet_Change macro:

Private Sub Worksheet_Change(ByVal Target As Range)
 Target = Application.WorksheetFunction.Proper(Target)
End Sub

Note: This code is extremely simplified, has no error checking, only works on one cell at a time, etc.

I'm not sure how interested you are in using a WorksheetChange macro to accomplish your goal, so I kept it simple.

Let me know if you are interested.


Report •

#2
April 22, 2010 at 08:22:53
That was exactly what I needed.

Thanks!!


Report •

#3
April 22, 2010 at 08:30:25
Actually, can you provide a formula to do the same in just column A....or column B?

Report •

Related Solutions

#4
April 22, 2010 at 08:46:05
Are you asking for a macro to work in just Column A, as opposed to a formula?

A formula looks like this:

=PROPER(A1)

A macro looks like this:

Private Sub Worksheet_Change(ByVal Target As Range)
'Determine if change was made to Column A (1)
  If Target.Column = 1 Then
'If yes, apply the Proper function
   Target = Application.WorksheetFunction.Proper(Target)
  End If
End Sub


Report •

#5
April 22, 2010 at 15:34:16
I meant macro and the macro you provided is right for what I need....but is there a way to expand the column range to apply the capitalization to columns A and B....or to columns A, C and F?

Thanks again!


Report •

#6
April 22, 2010 at 19:07:00
re: apply the capitalization to columns A and B....or to columns A, C and F?

What do you want?

A and B?
A, C and F?
A, B, C and F?


Report •

#7
April 23, 2010 at 07:08:53
I was hoping for examples of each so I would get an idea of how it works....but I've figured it out.

Thanks


Report •

#8
April 23, 2010 at 08:12:53
Two options come to mind:

Brute Force:

If Target.Column = 1 or Target.Column = 2 or Target.Column = 4 Then
  Do Stuff...
End if

or, more elegant:

Select Case Target.Column
  Case 1, 2, 4
   Do stuff....
End Select

Which one did you use? ;-)


Report •

#9
April 23, 2010 at 22:49:49
...something less elegant, more crass:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 2 Then
Target = Application.WorksheetFunction.Proper(Target)
End If
If Target.Column = 3 Then
Target = Application.WorksheetFunction.Proper(Target)
End If
End Sub


Report •

Ask Question