One VBA code upsetting another

[Holbeche]

I have an excel sheet where i have written code so that certin range of cells are always CAPS. I have also added a button to clear ranges of cells once the form has been saved so that it is ready to fill it out again without having to open up a new template.
The range that has the uppercase code launches the debugger and i have to open up a new template for it to work the second time around.

Any ideas how to fix? The line highlighted in red is the one that needs debugging.



Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Target, Range("B3:E8")) Is Nothing Then Exit Sub

Application.EnableEvents = False
Target = UCase(Target)
Application.EnableEvents = True

End Sub

 

[tonyyy]

Holbeche,

Welcome to the Board.

You might try the following...

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo errHandler
If Intersect(Target, Range("B3:E8")) Is Nothing Or Target.Count > 1 Then Exit Sub
Application.EnableEvents = False
Target = UCase(Target)
errHandler:
Application.EnableEvents = True
End Sub

Cheers,

tonyyy

 

[Holbeche]

Cheers tonyyy
Works perfectly.
Many thanks for your help!!

Holbeche,

Welcome to the Board.

You might try the following...

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo errHandler
If Intersect(Target, Range("B3:E8")) Is Nothing Or Target.Count > 1 Then Exit Sub
Application.EnableEvents = False
Target = UCase(Target)
errHandler:
Application.EnableEvents = True
End Sub

Cheers,

tonyyy

 

[tonyyy]

You're very welcome...