Help with Arguments

[Guest]

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 tab>view code>insert this>modify to suit your range>SAVE
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

 

[Andrew Taylor]

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.

 

[Guest]

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]

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

 

[Guest]

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!