1st letter upper case

[ZIPPOMA NEEDS HELP]

I am trying to have what ever is entered in a group of cells format the
same(1st letter upper case, rest lower) I have done =proper(a1) and pasted to
the proper cell. The problem is once I type something else in the cell it
reverts to normal.

Is there a way to make all future entries in the range of cells show as
proper format?

 

[Gord Dibben]

The PROPER function is not meant to go into the source cell.

Say A1 contains ZIPPOMA

In B1 enter =PROPER(A1) to return Zippoma

Leave it there. Do not paste over A1 unless you copy B1 and paste values
onto A1

That's a problem with these types of Functions.........you need a helper
cell.

If you feel you are up to some VBA you could use event code behind the sheet
to change the cells as you type and enter the data.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column > 3 Then Exit Sub 'adjust to suit
On Error GoTo ErrHandler
Application.EnableEvents = False
Target.Formula = Application.Proper(Target.Formula)
ErrHandler:
Application.EnableEvents = True
End Sub

Right-click on the sheet tab and "View Code"

Copy/paste the above into that module.

Edit the range and Alt + q ro return to the Excel window.

Anything typed into columns A:C will be proper case.


Gord Dibben MS Excel MVP

 

[ZIPPOMA NEEDS HELP]

Thanks worked great. I just increased the 3 to 50 to cover the whole sheet
assuming it is counting col across. Now if I put an entry into a specific
cell that I want upper case only within the sheet can I override this?

 

[Dave Peterson]

One way:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column > 3 Then Exit Sub 'adjust to suit
if intersect(target,me.range("x999")) is nothing then exit sub
On Error GoTo ErrHandler
Application.EnableEvents = False
Target.Formula = Application.Proper(Target.Formula)
ErrHandler:
Application.EnableEvents = True
End Sub

If you have a bunch of cells that shouldn't be touched, you could use:

if intersect(target,me.range("x999,y13,w44:z47")) is nothing then exit sub

 

[ZIPPOMA NEEDS HELP]

I am a bit confused...haha oh really!

You added the one line with "X999" in it and the a second option with y13
w44 etc. Could you tell me what each of these will do differently to the
sheet and when I type something how is it determined within the cell if I
want "proper" or all caps

 

[Dave Peterson]

The cells with addresses that are in that line will not be converted to anything
at all. What you type in will be what you end up with. You can change the
addresses to whatever you want. I included the second line so that you would
see how you'd specify more than one cell.

If you want those cells to become upper case no matter how you type them in:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column > 3 Then Exit Sub 'adjust to suit

On Error GoTo ErrHandler
Application.EnableEvents = False

if not (intersect(target,me.range("b99")) is nothing) then
target.formula = ucase(target.formula)
else
Target.Formula = Application.Proper(Target.Formula)
end if
ErrHandler:
Application.EnableEvents = True
End Sub