Upper Case on Input Q
 

I have the attached that I wish to Capitalise all entries within a
particular worksheet in Column D9:D44, my problem is that when I enter a
lower case value into anyone of the cells, it remains lowercase, what am I
doing wrong?

Thanks



Private Sub Worksheet_ChangeToUpper(ByVal Target As Excel.Range)
With Target
If .Count = 1 Then
If Not Intersect(.Cells, Range("D9:D44")) Is Nothing Then
Application.EnableEvents = False
.Value = UCase(.Value)
Application.EnableEvents = True
End If
End If
End With
End Sub

Upper Case on Input Q
 

John,

Worksheet event code has specific names, you can't change them to whatever
you fancy. There is no ChangeToUpper event, just a Change event

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count = 1 Then
If Not Intersect(.Cells, Range("D9:D44")) Is Nothing Then
Application.EnableEvents = False
.Value = UCase(.Value)
Application.EnableEvents = True
End If
End If
End With
End Sub



--

HTH

RP
(remove nothere from the email address if mailing direct)


"John" wrote in message
...
I have the attached that I wish to Capitalise all entries within a
particular worksheet in Column D9:D44, my problem is that when I enter a
lower case value into anyone of the cells, it remains lowercase, what am I
doing wrong?

Thanks



Private Sub Worksheet_ChangeToUpper(ByVal Target As Excel.Range)
With Target
If .Count = 1 Then
If Not Intersect(.Cells, Range("D9:D44")) Is Nothing Then
Application.EnableEvents = False
.Value = UCase(.Value)
Application.EnableEvents = True
End If
End If
End With
End Sub

Upper Case on Input Q
 

Aaaaaah, I had another Change event in the same worksheet Bob that changes a
different column to Proper so that why I renamed it when it wouldn't work
the first time. How would I combine the two?

Thanks again

"Bob Phillips" wrote in message
...
John,

Worksheet event code has specific names, you can't change them to whatever
you fancy. There is no ChangeToUpper event, just a Change event

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count = 1 Then
If Not Intersect(.Cells, Range("D9:D44")) Is Nothing Then
Application.EnableEvents = False
.Value = UCase(.Value)
Application.EnableEvents = True
End If
End If
End With
End Sub



--

HTH

RP
(remove nothere from the email address if mailing direct)


"John" wrote in message
...
I have the attached that I wish to Capitalise all entries within a
particular worksheet in Column D9:D44, my problem is that when I enter a
lower case value into anyone of the cells, it remains lowercase, what am
I
doing wrong?

Thanks



Private Sub Worksheet_ChangeToUpper(ByVal Target As Excel.Range)
With Target
If .Count = 1 Then
If Not Intersect(.Cells, Range("D9:D44")) Is Nothing Then
Application.EnableEvents = False
.Value = UCase(.Value)
Application.EnableEvents = True
End If
End If
End With
End Sub

Upper Case on Input Q
 

Got it!

Thanks

"John" wrote in message
...
Aaaaaah, I had another Change event in the same worksheet Bob that changes
a
different column to Proper so that why I renamed it when it wouldn't work
the first time. How would I combine the two?

Thanks again

"Bob Phillips" wrote in message
...
John,

Worksheet event code has specific names, you can't change them to
whatever
you fancy. There is no ChangeToUpper event, just a Change event

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count = 1 Then
If Not Intersect(.Cells, Range("D9:D44")) Is Nothing Then
Application.EnableEvents = False
.Value = UCase(.Value)
Application.EnableEvents = True
End If
End If
End With
End Sub



--

HTH

RP
(remove nothere from the email address if mailing direct)


"John" wrote in message
...
I have the attached that I wish to Capitalise all entries within a
particular worksheet in Column D9:D44, my problem is that when I enter
a
lower case value into anyone of the cells, it remains lowercase, what
am
I
doing wrong?

Thanks



Private Sub Worksheet_ChangeToUpper(ByVal Target As Excel.Range)
With Target
If .Count = 1 Then
If Not Intersect(.Cells, Range("D9:D44")) Is Nothing Then
Application.EnableEvents = False
.Value = UCase(.Value)
Application.EnableEvents = True
End If
End If
End With
End Sub

Upper Case on Input Q
 

Presumably, they work on separate ranges, so test individually,like


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count = 1 Then
If Not Intersect(.Cells, Range("D9:D44")) Is Nothing Then
'do one thing
ElseIf Not Intersect(.Cells, Range("A1:A100")) Is Nothing Then
'do another
End If
End If
End With
End Sub

If they both upshift, then try something liek

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count = 1 Then
If Not Intersect(.Cells, Range("A1:A100,D9:D44")) Is Nothing
Then
Application.EnableEvents = False
.Value = UCase(.Value)
Application.EnableEvents = True
End If
End If
End With
End Sub



--

HTH

RP
(remove nothere from the email address if mailing direct)


"John" wrote in message
...
Aaaaaah, I had another Change event in the same worksheet Bob that changes
a
different column to Proper so that why I renamed it when it wouldn't work
the first time. How would I combine the two?

Thanks again

"Bob Phillips" wrote in message
...
John,

Worksheet event code has specific names, you can't change them to
whatever
you fancy. There is no ChangeToUpper event, just a Change event

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count = 1 Then
If Not Intersect(.Cells, Range("D9:D44")) Is Nothing Then
Application.EnableEvents = False
.Value = UCase(.Value)
Application.EnableEvents = True
End If
End If
End With
End Sub



--

HTH

RP
(remove nothere from the email address if mailing direct)


"John" wrote in message
...
I have the attached that I wish to Capitalise all entries within a
particular worksheet in Column D9:D44, my problem is that when I enter
a
lower case value into anyone of the cells, it remains lowercase, what
am
I
doing wrong?

Thanks



Private Sub Worksheet_ChangeToUpper(ByVal Target As Excel.Range)
With Target
If .Count = 1 Then
If Not Intersect(.Cells, Range("D9:D44")) Is Nothing Then
Application.EnableEvents = False
.Value = UCase(.Value)
Application.EnableEvents = True
End If
End If
End With
End Sub