Capitalize the 1st letter of text entered in a cell

[Michael Lanier]

I frequently enter text into cells. Sometimes the text is entered in
lower case letters. I want the 1st letter to be capitalized
regardless of my entry. Are there any suggestions? Thanks in
advance.

Michael

 

[Chip Pearson]

You can only do this with code. There is no formatting command that
can be used. Right-click on the appropriate worksheet tab, choose View
Code, and paste in the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ErrH:
If Target.Cells.Count > 1 Then
Exit Sub
End If
If Not Application.Intersect(Target, _
Me.Range("A1:A10")) Is Nothing Then
Application.EnableEvents = False
If Target.HasFormula = False Then
If Target.HasArray = False Then
If Len(Target.Text) > 0 Then
If IsNumeric(Target.Text) = False Then
Target.Value = UCase(Left(Target.Text, 1)) & _
Mid(Target.Text, 2)
End If
End If
End If
End If
End If
ErrH:
Application.EnableEvents = True
End Sub


Change the reference A1:A10 to the range of cells that you want to
automatically change. If you want the have the code automatically
change entries in ALL cells on the worksheet, use the following code
rather than the code above:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ErrH:
If Target.Cells.Count > 1 Then
Exit Sub
End If
Application.EnableEvents = False
If Target.HasFormula = False Then
If Target.HasArray = False Then
If Len(Target.Text) > 0 Then
If IsNumeric(Target.Text) = False Then
Target.Value = UCase(Left(Target.Text, 1)) & _
Mid(Target.Text, 2)
End If
End If
End If
End If
ErrH:
Application.EnableEvents = True

End Sub

The code will not modify numeric values and will not modify cells that
contains formulas or arrays.

Close VBA from the File menu to return to Excel.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Dave Peterson]

Just to add to Chip's code...

If you want to make sure that only the first character is upper and the
remaining characters are lower, then change this line:

Target.Value = UCase(Left(Target.Text, 1)) & _
Mid(Target.Text, 2)

to:

Target.Value = UCase(Left(Target.Text, 1)) & _
lcase(Mid(Target.Text, 2))

 

[Michael Lanier]

Chip and Dave,

I neglected to mention that the cells I enter the text in are Data >
Validation > List. While I can certainly select text from the list, I
also have the option to enter the text directly. At this time, the
macro doesn't want to execute, which makes me wonder if it has to do
with the dropdown List. Of course, it may be just due to the fact
that I need to get a fresh start in the morning. Thank you both for
your help.

Michael

 

[Dave Peterson]

If you're running xl97, then under some circumstances the event won't fire.

Debra Dalgleish shares this:
http://contextures.com/xlDataVal08.html#Change

If you're not running xl97, then this isn't the problem. Maybe you turned off
events while testing and didn't turn them back on???