Excel Format Cell - first letter upper case

[i8theburger]

Is there a way to format a cell so that when I type in "john" it wil
display as "John"
Or if I type in "jOhN" it will display as "John"
The PROPER function works if I refernce it to another cell.

I also want to format a cell to display a Canadian Postal Code so tha
when I type "v9t5t1" it will display as "V9T 5T1" with a space betwee
the T and the 5.

Thank you

 

[Bob Phillips]

Worksheet event code

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Columns(1)) Is Nothing Then
With Target
.Value = WorksheetFunction.Proper(.Value)
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

Right-click the sheet tab, select View Code, and paste the code in.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[i8theburger]

I cannot get that code to work

 

[Frank Kabel]

Hi
you have to put it in a worksheet module (as Bob has described it).
What problem did you exactly encounter?

 

[i8theburger]

I followed his directions. Then when I type a word into any cell, I ge
a Microsoft Visual Basic dialog box pop up saying Compile error
Expected: expression

When I click the ok button, the word event is highlighted in the code

 

[Frank Kabel]

Hi
sounds like you also pasted the first line of Bob's posting. Just
insert the lines between the dashed lines below:

'----------
Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Columns(1)) Is Nothing Then
With Target
.Value = WorksheetFunction.Proper(.Value)
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub
'---------------

 

[i8theburger]

Thank you, thank you, and many thanks yous!!

My other question was is there a way to format one cell so that i
displays a Canadian Postal code like this:

V9T 9T1

when you enter this: v9t5t1

Notice the capitals and the space in the middle.

Thanks,

Joh

 

[Frank Kabel]

Hi
lets say you enter the values for proper cases in column A and the ZIP
values in column B then try the following event macro

'-----------------
Private Sub Worksheet_Change(ByVal Target As Range)
Dim zip_code
On Error GoTo ws_exit:
Application.EnableEvents = False
with target
select case .column
case 1 'Proper column
.Value = WorksheetFunction.Proper(.Value)
case 2 'Zip code
zip_code = UCase(.value)
zip_code = Left(zip_code,3) & " " & _
Right(zip_code,3)
.value = zip_code
end select
End With

ws_exit:
Application.EnableEvents = True
End Sub



'-------------------

 

[i8theburger]

Thank you, thank you, and many thanks yous!!

My other question was is there a way to format one cell so that i
displays a Canadian Postal code like this:

V9T 9T1

when you enter this: v9t5t1

Notice the capitals and the space in the middle.

Thanks,

Joh

 

[Frank Kabel]

Hi
i think I posted a solution for this