Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1:H10"
Dim i As Long
Dim tmp As String
Dim fCapital As Boolean

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
fCapital = True
For i = 1 To Len(.Value)
If fCapital Then
tmp = tmp & UCase(Mid(.Value, i, 1))
fCapital = False
Else
tmp = tmp & Mid(.Value, i, 1)
fCapital = Mid(.Value, i, 1) = " "
End If
Next i
.Value = tmp
End If
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Jeff" <Jeff@discussions.microsoft.com> wrote in message
news:491C537C-25E6-4DCA-A12E-7559E515D7C8@microsoft.com...
> Hi Bob,
>
> Assuming there a company name 'company ABC private limited'. by using VBA
> below, it will change to 'Company Abc Private Limited'. Please advise how
to
> maintain ABC unchange. Eg. Company ABC Private Limited.
>
> Thanks.
>
> Jeff.
>
> "Bob Phillips" wrote:
>
> > 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.
> >
> >
> >
> >
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (replace somewhere in email address with gmail if mailing direct)
> >
> > "Jeff" <Jeff@discussions.microsoft.com> wrote in message
> > news:8C2AF09D-B95A-4FF6-8A66-3C1B0847DD78@microsoft.com...
> > > Hi, I had tried to post general question to find if there is a way to
auto
> > > default 1st letter of a word to be capital letter but so far not
possible.
> > > Pls see reply below.
> > >
> > > AFAIK, this does not exist.
> > > But you can copy/Paste special values from calculated column back into
> > > original column.
> > >
> > > Cheers,
> > > --
> > > AP
> > >
> > > "Jeff" <Jeff@discussions.microsoft.com> a écrit dans le message de
news:
> > > 03B07E23-F91E-4BF3-BCFE-07D6BB4F1135@microsoft.com...
> > > > 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" <Jeff@discussions.microsoft.com> a écrit dans le message de
> > news:
> > > >> 76B09FC7-F85F-4844-BAB7-133E8DF1D54A@microsoft.com...
> > > >> > 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.
> > >
> > >
> > > ----------------
> > > This post is a suggestion for Microsoft, and Microsoft responds to the
> > > suggestions with the most votes. To vote for this suggestion, click
the "I
> > > Agree" button in the message pane. If you do not see the button,
follow
> > this
> > > link to open the suggestion in the Microsoft Web-based Newsreader and
then
> > > click "I Agree" in the message pane.
> > >
> > >
> >
http://www.microsoft.com/office/comm...lic.excel.misc
> >
> >
> >