Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
1st letter upper case
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? -- Thanks Everyone Mike |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
1st letter upper case
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 On Tue, 14 Oct 2008 17:17:15 -0700, ZIPPOMA NEEDS HELP wrote: 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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
1st letter upper case
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? -- Thanks Everyone Mike "Gord Dibben" wrote: 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 On Tue, 14 Oct 2008 17:17:15 -0700, ZIPPOMA NEEDS HELP wrote: 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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
1st letter upper case
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 wrote: 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? -- Thanks Everyone Mike "Gord Dibben" wrote: 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 On Tue, 14 Oct 2008 17:17:15 -0700, ZIPPOMA NEEDS HELP wrote: 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? -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
1st letter upper case
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 -- Thanks Everyone Mike "Dave Peterson" wrote: 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 wrote: 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? -- Thanks Everyone Mike "Gord Dibben" wrote: 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 On Tue, 14 Oct 2008 17:17:15 -0700, ZIPPOMA NEEDS HELP wrote: 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? -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
1st letter upper case
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 ZIPPOMA NEEDS HELP wrote: 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 -- Thanks Everyone Mike "Dave Peterson" wrote: 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 wrote: 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? -- Thanks Everyone Mike "Gord Dibben" wrote: 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 On Tue, 14 Oct 2008 17:17:15 -0700, ZIPPOMA NEEDS HELP wrote: 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? -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
lower case letter "i" always converts to upper case | Excel Discussion (Misc queries) | |||
Changing upper case characters to upper/lower | Excel Discussion (Misc queries) | |||
Changing file in all upper case to upper and lower case | Excel Discussion (Misc queries) | |||
How do I convert all upper case excel sheet into upper and lower . | Excel Discussion (Misc queries) | |||
How do I change Letter case (lower to Upper) in a spreadsheet??? | Excel Discussion (Misc queries) |