I am relatively new to VB coding-

Someone suggested this as a way to change lower case letter to upper case
letter when entered incorrectly by the user. I receive a Compile Error -
Argument not Optional. Any idea what I am doing wrong?

Right click sheet tabview codeinsert thismodify to suit your rangeSAVE
workbook.
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target.Range("H141:AK24")) Is Nothing Then
Target = UCase(Target.Range)
End If
End Sub

It should be:

If Not Intersect(Target, Range("H141:AK24")) Is Nothing Then
Target = UCase(Target)
End If

The error message was telling you that the Intersect function
takes two arguments, and you only gave it one.



On Jul 25, 3:44 pm, betany70
wrote:
I am relatively new to VB coding-

Someone suggested this as a way to change lower case letter to upper case
letter when entered incorrectly by the user. I receive a Compile Error -
Argument not Optional. Any idea what I am doing wrong?

Right click sheet tabview codeinsert thismodify to suit your rangeSAVE
workbook.
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target.Range("H141:AK24")) Is Nothing Then
Target = UCase(Target.Range)
End If
End Sub

Thanks - got rid of the error but it still doesn't work - someone else
suggested the cap lock key - I may try that :(

"Andrew Taylor" wrote:


It should be:

If Not Intersect(Target, Range("H141:AK24")) Is Nothing Then
Target = UCase(Target)
End If

The error message was telling you that the Intersect function
takes two arguments, and you only gave it one.



On Jul 25, 3:44 pm, betany70
wrote:
I am relatively new to VB coding-

Someone suggested this as a way to change lower case letter to upper case
letter when entered incorrectly by the user. I receive a Compile Error -
Argument not Optional. Any idea what I am doing wrong?

Right click sheet tabview codeinsert thismodify to suit your rangeSAVE
workbook.
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target.Range("H141:AK24")) Is Nothing Then
Target = UCase(Target.Range)
End If
End Sub

It works for me - as long as the target cell is in the range
H141:AK24.
Is that the correct range? The specification is valid but unusual:
H24:AK141 is equivalent and probably clearer.

BTW it occurred to me after I sent my first message that you
should use
If Target < UCase(Target) Then Target = UCase(Target)
to stop the event being triggered repeatedly.

Andrew


On Jul 25, 4:10 pm, betany70
wrote:
Thanks - got rid of the error but it still doesn't work - someone else
suggested the cap lock key - I may try that :(



"Andrew Taylor" wrote:

It should be:

If Not Intersect(Target, Range("H141:AK24")) Is Nothing Then
Target = UCase(Target)
End If

The error message was telling you that the Intersect function
takes two arguments, and you only gave it one.

On Jul 25, 3:44 pm, betany70
wrote:
I am relatively new to VB coding-

Someone suggested this as a way to change lower case letter to upper case
letter when entered incorrectly by the user. I receive a Compile Error -
Argument not Optional. Any idea what I am doing wrong?

Right click sheet tabview codeinsert thismodify to suit your rangeSAVE
workbook.
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target.Range("H141:AK24")) Is Nothing Then
Target = UCase(Target.Range)
End If
End Sub- Hide quoted text -

- Show quoted text -

Nothing better than another set of eyes - I missed a 1 when I was updating -
range should have been H14:AK24 - I made the change to the stop event as well
- works like a charm!!!! Thanks so much!

"Andrew Taylor" wrote:

It works for me - as long as the target cell is in the range
H141:AK24.
Is that the correct range? The specification is valid but unusual:
H24:AK141 is equivalent and probably clearer.

BTW it occurred to me after I sent my first message that you
should use
If Target < UCase(Target) Then Target = UCase(Target)
to stop the event being triggered repeatedly.

Andrew


On Jul 25, 4:10 pm, betany70
wrote:
Thanks - got rid of the error but it still doesn't work - someone else
suggested the cap lock key - I may try that :(



"Andrew Taylor" wrote:

It should be:

If Not Intersect(Target, Range("H141:AK24")) Is Nothing Then
Target = UCase(Target)
End If

The error message was telling you that the Intersect function
takes two arguments, and you only gave it one.

On Jul 25, 3:44 pm, betany70
wrote:
I am relatively new to VB coding-

Someone suggested this as a way to change lower case letter to upper case
letter when entered incorrectly by the user. I receive a Compile Error -
Argument not Optional. Any idea what I am doing wrong?

Right click sheet tabview codeinsert thismodify to suit your rangeSAVE
workbook.
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target.Range("H141:AK24")) Is Nothing Then
Target = UCase(Target.Range)
End If
End Sub- Hide quoted text -

- Show quoted text -