Force Upper case Formula

[Shazz]

I have the below code which works fine in some worksheets and not others, can anyone advise what I am doing wrong at all?

Private Sub Worksheet_Change(ByVal Target As Range)

''''''''''''''''''''''''''''''''''''''''''''

'Forces text to UPPER case for the range M12:NV71

''''''''''''''''''''''''''''''''''''''''''''

If Target.Cells.Count > 1 Or Target.HasFormula Then Exit Sub



On Error Resume Next

If Not Intersect(Target, Range("M12:NV71")) Is Nothing Then

Application.EnableEvents = False

Target = UCase(Target)

Application.EnableEvents = True

End If

On Error GoTo 0



End Sub

 

[Deepak]

Sample?

 

[Luke M]

the code you posted would be installd in a sheet module, and thus would only work on tha specific sheet. If you want this applied to all worksheets, need to put this in the ThisWorkbook module.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
''''''''''''''''''''''''''''''''''''''''''''
'Forces text to UPPER case for the range M12:NV71
''''''''''''''''''''''''''''''''''''''''''''

If Target.Cells.Count > 1 Or Target.HasFormula Then Exit Sub

If Not Intersect(Target, Sh.Range("M12:NV71")) Is Nothing Then

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

End If


End Sub

Also, note that I took out the Resume Next command as there's nothing in the code that should be causing an error. If an error does occur, we want to know about it rather than just skipping over.