Ardus

I got reply from Excel Expert Bob Philips, the solution is as follow

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1:H10"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If Not IsNumeric(.Value) Then
If Not .HasFormula Then
.Value = Application.Proper(.Value)
End If
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.

================================================== ==

"Ardus Petus" wrote:

You could use a Worksheet_Change event procedure to "properize" entries in
certain cells.

--
AP

"Jeff" a écrit dans le message de news:
...
Ardus,

Thanks. I will suggest to Microsoft to include this as one of excel
validation option.

Jeff.

"Ardus Petus" wrote:

AFAIK, this does not exist.
But you can copy/Paste special values from calculated column back into
original column.

Cheers,
--
AP

"Jeff" a écrit dans le message de news:
...
Hi Ardus

Thanks for you reply. Your recommended method is to use formula
=PROPER(A1).
It's referring to another cell, update to correct format and appear in
existing cell. Is there a way to update to correct format immediately
in
existing cell withot referring to another field ? Some kind of excel
validation function.

Jeff.

"Ardus Petus" wrote:

=PROPER(A1)

HTH
--
AP

"Jeff" a écrit dans le message de
news:
...
Hi, I am trying to auto format excel entry to default capital letter
for
1st
letter of each word. For example: When user insert '10,
international
building, sunny road', the entry will updated to '10, International
Building,
Sunny Road'.

Please show me the method.

Your help is very much appreciated.

Jeff.