ExcelBanter - Default Capital letter for 1st letter of a word

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Default Capital letter for 1st letter of a word (https://www.excelbanter.com/excel-discussion-misc-queries/98309-default-capital-letter-1st-letter-word.html)

Default Capital letter for 1st letter of a word

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.

Default Capital letter for 1st letter of a word

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.

Default Capital letter for 1st letter of a word

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.