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?

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?

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?


.

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?


.

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

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?

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?




.

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.

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.

.