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