Capitilize first letter

[Guest]

How do you get Excel to capitalize automatically the first letter when typing
a name in each cell. Example, when I type a name, I have to manually
capitalize the first letter in the name.

Thank you very much

 

[Jason Morin]

Bob Phillips provided you a solution yesterday:

http://tinyurl.com/6gged

HTH
Jason
Atlanta, GA

 

[Don Guillett]

try right click sheet tab>view code>copy paste this>SAVE. as written works
only in col A (1)

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 1 Then Exit Sub
Application.EnableEvents = False

If Target.HasFormula Then
Target.Formula = Application.Proper(Target.Formula)
Else
Target.Value = StrConv(Target.Value, vbProperCase)
End If
Application.EnableEvents = True
End Sub

 

[JE McGimpsey]

You'd need to do it using VBA. To automatically capitalize text in
column A, one way:


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If .Column = 1 Then
Application.EnableEvents = False
.Value = UCase(Left(.Text, 1)) & Mid(.Text, 2)
Application.EnableEvents = True
End If
End With
End Sub

Put this in the worksheet code module (right-click the worksheet tab and
choose View Code).

 

[Guest]

Hi Don

I pasted code after typing a couple same strings in cells A1 and A2. but
went to run the macro and it wasnt listed as a macro name... I couldnt run
your macro below...please advise...thanks...

 

[Guest]

Don,

followup..I got it to work...thanks...but why dont I have to run it as a
standard macro? how does it know to run after I press Enter in the worksheet
cell.

thanks...great code

craig

 

[Dave Peterson]

This worksheet_change is an event that excel is always looking for. (You can
disable it if you want or you can use it to help.)

Excel has lots of these events and is always looking for something to do.

Chip Pearson has a nice instruction page at his site that explains lots more
about events.

http://www.cpearson.com/excel/events.htm

 

[Don Guillett]

It is an event macro which means that it responds automatically to input in
the referenced cells, which in this case was column A (1). So, anytime you
input something in col A, the macro will fire.

To see some other sheet event macros>look in the right window of the sheet
module.

 

[Guest]

You can try this. I use this for my music listing worksheets. I created a
simple macro (CTRL P) in my PERSONAL.XLS Worksheet as follows:

Click on MACRO's
Macros again
Macro Name Dialog Box Comes Up
Name your macro
Click on edit
Enter the following and exit

' Macro recorded 1/30/2005 by Richard Meszaros
' Keyboard Shortcut: Ctrl+p
ExisText$ = Chr$(34) + ActiveCell.Text + Chr$(34)
ActiveCell.Formula = "=Proper" + ("(" + ExisText$ + ")")
End Sub

Now just click on the cell you want to "Properize" and hit CTRL-P.

This will be available to all of your worksheets.