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

--
Message posted from http://www.ExcelForum.com

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 " wrote in message
...
Is there a way to format a cell so that when I type in "john" it will
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 that
when I type "v9t5t1" it will display as "V9T 5T1" with a space between
the T and the 5.

Thank you.


---
Message posted from http://www.ExcelForum.com/

I cannot get that code to work

--
Message posted from http://www.ExcelForum.com

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

--
Regards
Frank Kabel
Frankfurt, Germany


I cannot get that code to work?


---
Message posted from http://www.ExcelForum.com/

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

--
Message posted from http://www.ExcelForum.com

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


--
Regards
Frank Kabel
Frankfurt, Germany


I followed his directions. Then when I type a word into any cell, I
get 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?


---
Message posted from http://www.ExcelForum.com/

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

--
Message posted from http://www.ExcelForum.com

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



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

--
Regards
Frank Kabel
Frankfurt, Germany

"i8theburger " schrieb im
Newsbeitrag ...
Thank you, thank you, and many thanks yous!!

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

V9T 9T1

when you enter this: v9t5t1

Notice the capitals and the space in the middle.

Thanks,

John


---
Message posted from http://www.ExcelForum.com/

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

--
Message posted from http://www.ExcelForum.com

Hi
i think I posted a solution for this

--
Regards
Frank Kabel
Frankfurt, Germany


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

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

V9T 9T1

when you enter this: v9t5t1

Notice the capitals and the space in the middle.

Thanks,

John


---
Message posted from http://www.ExcelForum.com/