"Proper" function

[HenyLoc]

I have a running list of names and would like to be able to enter the names without having to -shift- for the first letter of each name.

I found the "=proper" option but was unable to format whole columns. I was able to insert the code with a cell refrence then display the new text in a diffrent cell, but this would force me to have two list.

 

[Yogi Anand]

Hi HenryLoc:

Welcome to the Board!

After using the Proper function, you can paste the values back to the source data, then safely delete the additional column you had to use.

see the worksheet simulation

y021024U1.xls
	A	B	C	D
1	name	Proper		HowtousethePROPERfunctionwithout
2	henry	Henry		havingtolivewithtwocolumns
3	mrexcel	MrExcel		Step1
4	jondoe	JonDoe		usethePROPERfunctionincolumnBtogetallthenamesincolumnAPROPEREd
5	janesmith	JaneSmith		Step2
6	bob	Bob		highlitecellsB2:B9thenEDIT|COPY,thendoan
7	rich	Rich		EDIT|PASTE|SPECIAL_valuesovercellsA2:A9
8	ken	Ken		Step3
9	norm	Norm		nowthecolumnBcanbesafelydeletedandthenamesinColumnAremainProper
Sheet4

Regards!

Yogi Anand

 

[plettieri]

Hello:

There is a function that does that and a lot more on Asaputilities.....free down load...

pll

 

[Paul B]

Right click on the worksheet tab and view code paste this code in it will change whats in column D to Proper case when you hit enter, change target column to the column you need
Private Sub Worksheet_Change(ByVal Target As Range)
'To change letters in column D to Proper
On Error GoTo Error_handler
With Target
If Not .HasFormula Then
Application.EnableEvents = False
If Target.Column = 4 Then Target.Value = Application.Proper(Target.Value)
Application.EnableEvents = True
End If
End With
Error_handler:
Resume Next
End Sub

 

[HenyLoc]

Hay Paul thanks that one worked but I need to do it on two columns. I pasted the code again and change the targets but it gave me

ambiguous name detected: Worksheet_Change

error

I need columns B & C to work

Thanks again- I heard about this site just 20 min ago and I have been looking for this answer for a year now.

 

[HenyLoc]

Nevermind I got it,
had to add a second line

If Target.Column = 2 Then Target.Value = Applicatin.Proper(Target.Value)
If Target.Column = 3 Then Target.Value = Applicatin.Proper(Target.Value)