Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
UPPER and Proper Case
I've used the the following to format the one cell to
change to upper case: Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If Not Application.Intersect(Target, Range("J4")) Is Nothing Then Target(1).Value = UCase(Target(1).Value) End If Application.EnableEvents = True End Sub Now I need another cell, in the same Worksheet, to default to Proper Case. I tried copying and pasting the above, then changing: Target(1).Value = UCase(Target(1).Value) to Target(1).Value = StrConv(Target(1).Value, vbProperCase) But I get an error directed at the "Private Sub" line. Any suggestions? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
UPPER and Proper Case
Hi Tim
only one procedure with the name worksheet_change is allowed per worksheet. So in your case try the following combined function (assumption A1 is your other cell): Private Sub Worksheet_Change(ByVal Target As Range) On Error goto CleanUp Application.EnableEvents = False If Not Application.Intersect(Target, Range("J4")) Is Nothing Then Target(1).Value = UCase(Target(1).Value) Else I Not Application.Intersect(Target, Range("A1) Is Nothing Then Target(1).Value = StrConv(Target(1).Value, vbProperCase) End If CleanUp: Application.EnableEvents = True End Sub HTH Frank Tim wrote: I've used the the following to format the one cell to change to upper case: Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If Not Application.Intersect(Target, Range("J4")) Is Nothing Then Target(1).Value = UCase(Target(1).Value) End If Application.EnableEvents = True End Sub Now I need another cell, in the same Worksheet, to default to Proper Case. I tried copying and pasting the above, then changing: Target(1).Value = UCase(Target(1).Value) to Target(1).Value = StrConv(Target(1).Value, vbProperCase) But I get an error directed at the "Private Sub" line. Any suggestions? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
UPPER and Proper Case
Frank, I tried that but it gives me a syntex error on
" Else I Not Application.Intersect(Target, Range("AC4) Is Nothing Then" line. Any suggestions? Thanks Tim -----Original Message----- Hi Tim only one procedure with the name worksheet_change is allowed per worksheet. So in your case try the following combined function (assumption A1 is your other cell): Private Sub Worksheet_Change(ByVal Target As Range) On Error goto CleanUp Application.EnableEvents = False If Not Application.Intersect(Target, Range("J4")) Is Nothing Then Target(1).Value = UCase(Target(1).Value) Else I Not Application.Intersect(Target, Range("A1) Is Nothing Then Target(1).Value = StrConv(Target(1).Value, vbProperCase) End If CleanUp: Application.EnableEvents = True End Sub HTH Frank Tim wrote: I've used the the following to format the one cell to change to upper case: Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If Not Application.Intersect(Target, Range("J4")) Is Nothing Then Target(1).Value = UCase(Target(1).Value) End If Application.EnableEvents = True End Sub Now I need another cell, in the same Worksheet, to default to Proper Case. I tried copying and pasting the above, then changing: Target(1).Value = UCase(Target(1).Value) to Target(1).Value = StrConv(Target(1).Value, vbProperCase) But I get an error directed at the "Private Sub" line. Any suggestions? . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
UPPER and Proper Case
Frank's code is easily expanded to handled multiple ranges for the same
action (and therefore more robust). But, if in fact you only need to work with two cells this at least has a simpler construct. Private Sub Worksheet_Change(ByVal Target As Range) On Error goto CleanUp Application.EnableEvents = False Select Case Target.Address Case "$J$4" Target(1).Value = UCase(Target(1).Value) Case "$A$1" Target(1).Value = StrConv(Target(1).Value, vbProperCase) End Select CleanUp: Application.EnableEvents = True End Sub -- Regards, Tom Ogilvy "Frank Kabel" wrote in message ... Hi Tim only one procedure with the name worksheet_change is allowed per worksheet. So in your case try the following combined function (assumption A1 is your other cell): Private Sub Worksheet_Change(ByVal Target As Range) On Error goto CleanUp Application.EnableEvents = False If Not Application.Intersect(Target, Range("J4")) Is Nothing Then Target(1).Value = UCase(Target(1).Value) Else I Not Application.Intersect(Target, Range("A1) Is Nothing Then Target(1).Value = StrConv(Target(1).Value, vbProperCase) End If CleanUp: Application.EnableEvents = True End Sub HTH Frank Tim wrote: I've used the the following to format the one cell to change to upper case: Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If Not Application.Intersect(Target, Range("J4")) Is Nothing Then Target(1).Value = UCase(Target(1).Value) End If Application.EnableEvents = True End Sub Now I need another cell, in the same Worksheet, to default to Proper Case. I tried copying and pasting the above, then changing: Target(1).Value = UCase(Target(1).Value) to Target(1).Value = StrConv(Target(1).Value, vbProperCase) But I get an error directed at the "Private Sub" line. Any suggestions? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
UPPER and Proper Case
Change I Not to If Not
-- Regards, Tom Ogilvy wrote in message ... Frank, I tried that but it gives me a syntex error on " Else I Not Application.Intersect(Target, Range("AC4) Is Nothing Then" line. Any suggestions? Thanks Tim -----Original Message----- Hi Tim only one procedure with the name worksheet_change is allowed per worksheet. So in your case try the following combined function (assumption A1 is your other cell): Private Sub Worksheet_Change(ByVal Target As Range) On Error goto CleanUp Application.EnableEvents = False If Not Application.Intersect(Target, Range("J4")) Is Nothing Then Target(1).Value = UCase(Target(1).Value) Else I Not Application.Intersect(Target, Range("A1) Is Nothing Then Target(1).Value = StrConv(Target(1).Value, vbProperCase) End If CleanUp: Application.EnableEvents = True End Sub HTH Frank Tim wrote: I've used the the following to format the one cell to change to upper case: Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If Not Application.Intersect(Target, Range("J4")) Is Nothing Then Target(1).Value = UCase(Target(1).Value) End If Application.EnableEvents = True End Sub Now I need another cell, in the same Worksheet, to default to Proper Case. I tried copying and pasting the above, then changing: Target(1).Value = UCase(Target(1).Value) to Target(1).Value = StrConv(Target(1).Value, vbProperCase) But I get an error directed at the "Private Sub" line. Any suggestions? . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
UPPER and Proper Case
Sorry
forgot to paste the closing " change the line to Else If Not Application.Intersect(Target, Range("AC4") Is Nothing Then HTH Frank wrote: Frank, I tried that but it gives me a syntex error on " Else I Not Application.Intersect(Target, Range("AC4) Is Nothing Then" line. Any suggestions? Thanks Tim |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
UPPER and Proper Case
Tom, that worked. I was still getting an error with
Frank's code. Thanks to both of you for your assistance. either one of you know if it possible to have a drop-down list within a drop down list? I have formatted a drop down list but it is so long. I would like to divide it up in different categories. ie; first list would be Chev, Dodge, Ford, pick Chev then have the chose of Camaro, Sprint, etc. -----Original Message----- Frank's code is easily expanded to handled multiple ranges for the same action (and therefore more robust). But, if in fact you only need to work with two cells this at least has a simpler construct. Private Sub Worksheet_Change(ByVal Target As Range) On Error goto CleanUp Application.EnableEvents = False Select Case Target.Address Case "$J$4" Target(1).Value = UCase(Target(1).Value) Case "$A$1" Target(1).Value = StrConv(Target(1).Value, vbProperCase) End Select CleanUp: Application.EnableEvents = True End Sub -- Regards, Tom Ogilvy "Frank Kabel" wrote in message ... Hi Tim only one procedure with the name worksheet_change is allowed per worksheet. So in your case try the following combined function (assumption A1 is your other cell): Private Sub Worksheet_Change(ByVal Target As Range) On Error goto CleanUp Application.EnableEvents = False If Not Application.Intersect(Target, Range("J4")) Is Nothing Then Target(1).Value = UCase(Target(1).Value) Else I Not Application.Intersect(Target, Range("A1) Is Nothing Then Target(1).Value = StrConv(Target(1).Value, vbProperCase) End If CleanUp: Application.EnableEvents = True End Sub HTH Frank Tim wrote: I've used the the following to format the one cell to change to upper case: Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If Not Application.Intersect(Target, Range("J4")) Is Nothing Then Target(1).Value = UCase(Target(1).Value) End If Application.EnableEvents = True End Sub Now I need another cell, in the same Worksheet, to default to Proper Case. I tried copying and pasting the above, then changing: Target(1).Value = UCase(Target(1).Value) to Target(1).Value = StrConv(Target(1).Value, vbProperCase) But I get an error directed at the "Private Sub" line. Any suggestions? . |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
UPPER and Proper Case
Tim
See Debra Dalgleish's site for instructions on dependent lists. http://www.contextures.on.ca/xlDataVal02.html Gord Dibben Excel MVP On Fri, 30 Jan 2004 11:23:55 -0800, "Tim" wrote: either one of you know if it possible to have a drop-down list within a drop down list? I have formatted a drop down list but it is so long. I would like to divide it up in different categories. ie; first list would be Chev, Dodge, Ford, pick Chev then have the chose of Camaro, Sprint, etc. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
UPPER and Proper Case
Thanks Gord, that should help.
-----Original Message----- Tim See Debra Dalgleish's site for instructions on dependent lists. http://www.contextures.on.ca/xlDataVal02.html Gord Dibben Excel MVP On Fri, 30 Jan 2004 11:23:55 -0800, "Tim" wrote: either one of you know if it possible to have a drop- down list within a drop down list? I have formatted a drop down list but it is so long. I would like to divide it up in different categories. ie; first list would be Chev, Dodge, Ford, pick Chev then have the chose of Camaro, Sprint, etc. . |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
UPPER and Proper Case
Ever thought of just making a couple written macros and applying them to
your own toolbar, or menubar? The following code will change a cell to UPPER, lower or Proper depending on how many times you click the Icon you assign it too. Sub SwitchCase() Dim Cell As Range For Each Cell In Selection Select Case Cell Case LCase(Cell) 'It's lowercase Cell = UCase(Cell) Case UCase(Cell) 'It's uppercase Cell = Application.Proper(Cell) Case Else 'It's neither upper nor lower Cell = LCase(Cell) End Select Next End Sub Good Luck! --- Message posted from http://www.ExcelForum.com/ |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
UPPER and Proper Case
Be aware that running this code on any cell(s) with Formulas will turn those
formulas to values. The results of this change may not be too desirable. Gord Dibben Excel MVP On Sun, 1 Feb 2004 15:20:45 -0600, Padgett wrote: Ever thought of just making a couple written macros and applying them to your own toolbar, or menubar? The following code will change a cell to UPPER, lower or Proper depending on how many times you click the Icon you assign it too. Sub SwitchCase() Dim Cell As Range For Each Cell In Selection Select Case Cell Case LCase(Cell) 'It's lowercase Cell = UCase(Cell) Case UCase(Cell) 'It's uppercase Cell = Application.Proper(Cell) Case Else 'It's neither upper nor lower Cell = LCase(Cell) End Select Next End Sub Good Luck! --- Message posted from http://www.ExcelForum.com/ |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
UPPER and Proper Case
I don't understand why anyone would want to use a macro
that switches each entry to something else depending on what is in each cell. The usual purpose of such a macro to change case is to make the case for all of the cells consistent. I would suggest using separate macros depending on what case you want everyting in the selection to be. For instance use one macro for capitals wanted such as zipcode, and use another for proper case such as people's names. Proper, and other Text changes -- Use of SpecialCells http://www.mvps.org/dmcritchie/excel/proper.htm Example data in the following format will not be made consistent, by the subroutine that does a switcheroo based on current cell content. David McRitchie HARVEY QUINCY BoB Roberts Jim Roberts Arlene SANFORD tony weston Results of macro -- SwitchCasei david mcritchie Harvey Quincy bob roberts jim roberts arlene sanford TONY WESTON Results of use of Proper_Case on my webpage David McRitchie Harvey Quincy Bob Roberts Jim Roberts Arlene Sanford Tony Weston Rather than trying to fix names at proper case for exceptions there might be a reason to check if the original case is lower case, or upper case to change it to proper case; otherwise, leave it alone because you may have done special work like figuring out that Mackey and MacKey are two different names and are capitalized differently, and that even within some families member may even capitalize their lastname differently.. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Padgett " wrote ... Ever thought of just making a couple written macros and applying them to your own toolbar, or menubar? The following code will change a cell to UPPER, lower or Proper depending on how many times you click the Icon you assign it too. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I change from upper case to proper case in excel 2002 | Excel Discussion (Misc queries) | |||
Changing Entries to Proper and Upper Case Q | Excel Worksheet Functions | |||
excel'03 how to convert a column from upper case to proper case | Excel Discussion (Misc queries) | |||
Excel: How do I change all upper case ss to proper case? | Excel Worksheet Functions | |||
Changing Upper case to Proper Case | Excel Worksheet Functions |