New Validation option to format 1st letter as Capital letter

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
> >
> >
> >

Thanks Bob.

"Bob Phillips" wrote:

> 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
> > >
> > >
> > >
>
>
>