Capitalise Letters

[John]

How can I write code that will capitalise the First Letter in two names that
will be typed in to a single cell? I want this to be performed automatically
without user intervention. I can use the 'Proper' function but this has to
be typed in to the cell first as part of a formula, don't want to do that

I assume it will be a Private Sub within the Worksheet triggered by an event
i.e. a value in the appropriate cell

Thanks

 

[Harald Staff]

Rightclick sheet tab, choose View Code, paste this in:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Shouldbe As String
With Target
If .Address = "$B$2" Then
If .HasFormula = False Then
Shouldbe = StrConv(.Value, vbProperCase)
If .Value <> Shouldbe Then _
.Value = Shouldbe
End If
End If
End With
End Sub

Alter $B$2 to desired absolute cell address.

HTH. Best wishes Harald

 

[John]

Thanks Harald

Rightclick sheet tab, choose View Code, paste this in:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Shouldbe As String
With Target
If .Address = "$B$2" Then
If .HasFormula = False Then
Shouldbe = StrConv(.Value, vbProperCase)
If .Value <> Shouldbe Then _
.Value = Shouldbe
End If
End If
End With
End Sub

Alter $B$2 to desired absolute cell address.

HTH. Best wishes Harald

 

[Paul Robinson]

Hi John
This should get you started. Applies Proper to column A.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A:A")) Is Nothing Then
Target.Value = Application.WorksheetFunction.Proper(Target.Value)
End If
End Sub

Paste into the code module behind the sheet where you want the edit to
be performed (go to VB Editor and double click the sheet name in the
Project Window.
If you want to edit in all sheets, use the equivalent sub from the
ThisWorkBook code module.
If you want to restrict to a particular named range "MyRange" on a
sheet use Range("MyRange") instead of Range("A:A").

regards
Paul