Help with Arguments
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 |
Help with Arguments
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 |
Help with Arguments
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 |
Help with Arguments
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 - |
Help with Arguments
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 - |
All times are GMT +1. The time now is 07:48 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com