UPPER and Proper Case

[Tim]

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?

 

[Frank Kabel]

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

 

[Guest]

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

 

[Tom Ogilvy]

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

 

[Tom Ogilvy]

Change I Not to If Not

--
Regards,
Tom Ogilvy

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

 

[Frank Kabel]

Sorry
forgot to paste the closing "
change the line to
Else If Not Application.Intersect(Target, Range("AC4") Is Nothing Then

HTH
Frank

 

[Tim]

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.

 

[Gord Dibben]

Tim

See Debra Dalgleish's site for instructions on dependent lists.

http://www.contextures.on.ca/xlDataVal02.html

Gord Dibben Excel MVP

 

[Tim]

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



.

 

[Padgett]

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!

 

[Gord Dibben]

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

 

[David McRitchie]

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..