Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Column All Upper Case
Is there a way to set up say Col F to be all upper case when I type in it. I
would just like to continue to enter data and not have to think that I am entering a Canadian Postal Code and manually change the text to upper case. TIA -- Regards Michael Koerner |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Column All Upper Case
Right click the tab at the bottom of the worksheet where you want this
functionality, select View Code from the pop up menu that appears, and then copy/paste the following into the code window that appeared... Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 6 Then Application.EnableEvents = False Target.Formula = UCase(Target.Formula) Application.EnableEvents = True End If End Sub Now, go back to your worksheet and type something into Column F in lower case and watch it change to upper case. -- Rick (MVP - Excel) "Michael Koerner" wrote in message ... Is there a way to set up say Col F to be all upper case when I type in it. I would just like to continue to enter data and not have to think that I am entering a Canadian Postal Code and manually change the text to upper case. TIA -- Regards Michael Koerner |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Column All Upper Case
Rick;
Absolutely fantastic, that is two in a row you have provided with great success. Thank you very much. I do have one question. Would it be possible to change the "If Target.Column = 6 Then" to read (going out on a limb here) If Target.Column = 5 or If Target Column=6 Then.... I would like to include column 5 which contains prov/state -- Regards Michael Koerner "Rick Rothstein" wrote in message ... Right click the tab at the bottom of the worksheet where you want this functionality, select View Code from the pop up menu that appears, and then copy/paste the following into the code window that appeared... Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 6 Then Application.EnableEvents = False Target.Formula = UCase(Target.Formula) Application.EnableEvents = True End If End Sub Now, go back to your worksheet and type something into Column F in lower case and watch it change to upper case. -- Rick (MVP - Excel) "Michael Koerner" wrote in message ... Is there a way to set up say Col F to be all upper case when I type in it. I would just like to continue to enter data and not have to think that I am entering a Canadian Postal Code and manually change the text to upper case. TIA -- Regards Michael Koerner |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Column All Upper Case
Yes, you can do that. Here is the statement...
If Target.Column = 5 Or Target.Column = 6 Then -- Rick (MVP - Excel) "Michael Koerner" wrote in message ... Rick; Absolutely fantastic, that is two in a row you have provided with great success. Thank you very much. I do have one question. Would it be possible to change the "If Target.Column = 6 Then" to read (going out on a limb here) If Target.Column = 5 or If Target Column=6 Then.... I would like to include column 5 which contains prov/state -- Regards Michael Koerner "Rick Rothstein" wrote in message ... Right click the tab at the bottom of the worksheet where you want this functionality, select View Code from the pop up menu that appears, and then copy/paste the following into the code window that appeared... Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 6 Then Application.EnableEvents = False Target.Formula = UCase(Target.Formula) Application.EnableEvents = True End If End Sub Now, go back to your worksheet and type something into Column F in lower case and watch it change to upper case. -- Rick (MVP - Excel) "Michael Koerner" wrote in message ... Is there a way to set up say Col F to be all upper case when I type in it. I would just like to continue to enter data and not have to think that I am entering a Canadian Postal Code and manually change the text to upper case. TIA -- Regards Michael Koerner |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Column All Upper Case
Thanks very much, greatly appreciated.
-- Regards Michael Koerner "Rick Rothstein" wrote in message ... Yes, you can do that. Here is the statement... If Target.Column = 5 Or Target.Column = 6 Then -- Rick (MVP - Excel) "Michael Koerner" wrote in message ... Rick; Absolutely fantastic, that is two in a row you have provided with great success. Thank you very much. I do have one question. Would it be possible to change the "If Target.Column = 6 Then" to read (going out on a limb here) If Target.Column = 5 or If Target Column=6 Then.... I would like to include column 5 which contains prov/state -- Regards Michael Koerner "Rick Rothstein" wrote in message ... Right click the tab at the bottom of the worksheet where you want this functionality, select View Code from the pop up menu that appears, and then copy/paste the following into the code window that appeared... Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 6 Then Application.EnableEvents = False Target.Formula = UCase(Target.Formula) Application.EnableEvents = True End If End Sub Now, go back to your worksheet and type something into Column F in lower case and watch it change to upper case. -- Rick (MVP - Excel) "Michael Koerner" wrote in message ... Is there a way to set up say Col F to be all upper case when I type in it. I would just like to continue to enter data and not have to think that I am entering a Canadian Postal Code and manually change the text to upper case. TIA -- Regards Michael Koerner |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Column All Upper Case
Canadian postal codes like a space between 3rd and 4th character.
Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Target.Column < 7 Then Exit Sub 'adjust column to suit On Error GoTo ErrHandler Application.EnableEvents = False With Target .Formula = UCase(Target.Formula) .Value = (Left(Target.Value, 3) & " " & Right(Target.Value, 3)) End With ErrHandler: Application.EnableEvents = True End Sub Gord Dibben MS Excel MVP On Fri, 15 May 2009 16:37:06 -0400, "Michael Koerner" wrote: Is there a way to set up say Col F to be all upper case when I type in it. I would just like to continue to enter data and not have to think that I am entering a Canadian Postal Code and manually change the text to upper case. TIA |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Column All Upper Case
Coming from Ottawa I automatically put in the space <g
-- Regards Michael Koerner "Gord Dibben" <gorddibbATshawDOTca wrote in message ... Canadian postal codes like a space between 3rd and 4th character. Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Target.Column < 7 Then Exit Sub 'adjust column to suit On Error GoTo ErrHandler Application.EnableEvents = False With Target .Formula = UCase(Target.Formula) .Value = (Left(Target.Value, 3) & " " & Right(Target.Value, 3)) End With ErrHandler: Application.EnableEvents = True End Sub Gord Dibben MS Excel MVP On Fri, 15 May 2009 16:37:06 -0400, "Michael Koerner" wrote: Is there a way to set up say Col F to be all upper case when I type in it. I would just like to continue to enter data and not have to think that I am entering a Canadian Postal Code and manually change the text to upper case. TIA |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Column All Upper Case
Coming from a small town in B.C. I do the same when hand entering on an
envelope. When entering in a worksheet I just let the event code do it all. Gord On Sat, 16 May 2009 16:53:49 -0400, "Michael Koerner" wrote: Coming from Ottawa I automatically put in the space <g |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Column All Upper Case
After a good nights sleep, your suggestion really made a lot of sense. thank
you very much. But, when I tried to insert the macro you provided, I received a compile error indicating I had an ambiguous statement. I also have a couple of questions. The column with the postal code is column 6. Your macro I believe indicated <7 I would also like to have column 5 if possible in Upper Case as this is the prov/state column. -- Regards Michael Koerner "Gord Dibben" <gorddibbATshawDOTca wrote in message ... Coming from a small town in B.C. I do the same when hand entering on an envelope. When entering in a worksheet I just let the event code do it all. Gord On Sat, 16 May 2009 16:53:49 -0400, "Michael Koerner" wrote: Coming from Ottawa I automatically put in the space <g |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Column All Upper Case
A worksheet cannot have multiple events of the same type.
Different types...........yes.........same type........no. Sounds like you have more than one worksheet_change event in that sheet module. Either combine or change one of them to a different type. BTW...............postal codes are in column 6...........is column 5 simply province names? I can't imagine you want the same format on both columns in that case. Assumes you enter a province name in column 5 then a postal code in column 6 After the column 6 entry the code will run on both columns. Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Intersect(Range(Target(1).Address), _ Range("F:F")) Is Nothing Then Exit Sub On Error GoTo ErrHandler Application.EnableEvents = False With Target .Formula = UCase(Target.Formula) .Value = (Left(Target.Value, 3) & " " & Right(Target.Value, 3)) End With With Target.Offset(0, -1) .Formula = UCase(.Formula) End With ErrHandler: Application.EnableEvents = True End Sub Gord On Sun, 17 May 2009 06:51:15 -0400, "Michael Koerner" wrote: After a good nights sleep, your suggestion really made a lot of sense. thank you very much. But, when I tried to insert the macro you provided, I received a compile error indicating I had an ambiguous statement. I also have a couple of questions. The column with the postal code is column 6. Your macro I believe indicated <7 I would also like to have column 5 if possible in Upper Case as this is the prov/state column. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Column All Upper Case
Gord;
Absolutely correct, I did just want the Prov in upper case. Works like a charm until I enter a US zip code. But, that is not a problem, I have fewer of those and it is easy to delete a character. Thank you very much -- Regards Michael Koerner "Gord Dibben" <gorddibbATshawDOTca wrote in message ... A worksheet cannot have multiple events of the same type. Different types...........yes.........same type........no. Sounds like you have more than one worksheet_change event in that sheet module. Either combine or change one of them to a different type. BTW...............postal codes are in column 6...........is column 5 simply province names? I can't imagine you want the same format on both columns in that case. Assumes you enter a province name in column 5 then a postal code in column 6 After the column 6 entry the code will run on both columns. Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Intersect(Range(Target(1).Address), _ Range("F:F")) Is Nothing Then Exit Sub On Error GoTo ErrHandler Application.EnableEvents = False With Target .Formula = UCase(Target.Formula) .Value = (Left(Target.Value, 3) & " " & Right(Target.Value, 3)) End With With Target.Offset(0, -1) .Formula = UCase(.Formula) End With ErrHandler: Application.EnableEvents = True End Sub Gord On Sun, 17 May 2009 06:51:15 -0400, "Michael Koerner" wrote: After a good nights sleep, your suggestion really made a lot of sense. thank you very much. But, when I tried to insert the macro you provided, I received a compile error indicating I had an ambiguous statement. I also have a couple of questions. The column with the postal code is column 6. Your macro I believe indicated <7 I would also like to have column 5 if possible in Upper Case as this is the prov/state column. |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Column All Upper Case
Why not let the code handle that for you too? You can enter your Canadian
postal codes with or without the space and you can enter any 9-digit US Zip Codes (assuming you use those) with or without the dash between the first 5 digits and the last 4 digits... Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim Temp As String If Intersect(Range(Target(1).Address), _ Range("F:F")) Is Nothing Then Exit Sub On Error GoTo ErrHandler Application.EnableEvents = False With Target If Not .HasFormula Then Temp = Replace(UCase(Target.Value), " ", "") If Len(Temp) = 6 Then .Value = Format(Temp, "@@@ @@@") ElseIf Len(Temp) = 9 Then .Value = Format(Temp, "@@@@@-@@@@") End If End If End With With Target.Offset(0, -1) .Formula = UCase(.Formula) End With ErrHandler: Application.EnableEvents = True End Sub -- Rick (MVP - Excel) "Michael Koerner" wrote in message ... Gord; Absolutely correct, I did just want the Prov in upper case. Works like a charm until I enter a US zip code. But, that is not a problem, I have fewer of those and it is easy to delete a character. Thank you very much -- Regards Michael Koerner "Gord Dibben" <gorddibbATshawDOTca wrote in message ... A worksheet cannot have multiple events of the same type. Different types...........yes.........same type........no. Sounds like you have more than one worksheet_change event in that sheet module. Either combine or change one of them to a different type. BTW...............postal codes are in column 6...........is column 5 simply province names? I can't imagine you want the same format on both columns in that case. Assumes you enter a province name in column 5 then a postal code in column 6 After the column 6 entry the code will run on both columns. Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Intersect(Range(Target(1).Address), _ Range("F:F")) Is Nothing Then Exit Sub On Error GoTo ErrHandler Application.EnableEvents = False With Target .Formula = UCase(Target.Formula) .Value = (Left(Target.Value, 3) & " " & Right(Target.Value, 3)) End With With Target.Offset(0, -1) .Formula = UCase(.Formula) End With ErrHandler: Application.EnableEvents = True End Sub Gord On Sun, 17 May 2009 06:51:15 -0400, "Michael Koerner" wrote: After a good nights sleep, your suggestion really made a lot of sense. thank you very much. But, when I tried to insert the macro you provided, I received a compile error indicating I had an ambiguous statement. I also have a couple of questions. The column with the postal code is column 6. Your macro I believe indicated <7 I would also like to have column 5 if possible in Upper Case as this is the prov/state column. |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Column All Upper Case
.Formula = UCase(Target.Formula)
.Value = (Left(Target.Value, 3) & " " & Right(Target.Value, 3)) In your above two consecutive lines of code, there is no real need to protect the value by using the Formula property (of course it doesn't hurt either) when any formula that might be in the cell will be overwritten by your second line of code. While I'm guessing the OP doesn't actually have any formulas in his cells in this column, I still added a protection in the modification of your code that I posted (to handle US Zip Codes as well as Canadian Postal Codes) for formulas via a test of the HasFormula property. -- Rick (MVP - Excel) "Gord Dibben" <gorddibbATshawDOTca wrote in message ... A worksheet cannot have multiple events of the same type. Different types...........yes.........same type........no. Sounds like you have more than one worksheet_change event in that sheet module. Either combine or change one of them to a different type. BTW...............postal codes are in column 6...........is column 5 simply province names? I can't imagine you want the same format on both columns in that case. Assumes you enter a province name in column 5 then a postal code in column 6 After the column 6 entry the code will run on both columns. Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Intersect(Range(Target(1).Address), _ Range("F:F")) Is Nothing Then Exit Sub On Error GoTo ErrHandler Application.EnableEvents = False With Target .Formula = UCase(Target.Formula) .Value = (Left(Target.Value, 3) & " " & Right(Target.Value, 3)) End With With Target.Offset(0, -1) .Formula = UCase(.Formula) End With ErrHandler: Application.EnableEvents = True End Sub Gord On Sun, 17 May 2009 06:51:15 -0400, "Michael Koerner" wrote: After a good nights sleep, your suggestion really made a lot of sense. thank you very much. But, when I tried to insert the macro you provided, I received a compile error indicating I had an ambiguous statement. I also have a couple of questions. The column with the postal code is column 6. Your macro I believe indicated <7 I would also like to have column 5 if possible in Upper Case as this is the prov/state column. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
change data of entire column from small case to upper case | Excel Worksheet Functions | |||
Changing file in all upper case to upper and lower case | Excel Discussion (Misc queries) | |||
convert to upper case - entire column ? | Excel Discussion (Misc queries) | |||
excel'03 how to convert a column from upper case to proper case | Excel Discussion (Misc queries) | |||
How do I change a column in Excel from upper case to lower case? | Excel Worksheet Functions |