VBA code quit working

[Brad_G]

I've been using this "forced upper case" code for the last 3 months and haven't had any problems out of it.

Now, it won't run.

I did set up the workbook to track changes, which shared the workbook. I went back and took off tracked changes and un-shared it, but I'm still getting nothing.

What am I missing?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim tmpRng As Range, cl As Range
Set tmpRng = Intersect(Me.Range("a5:z1000"), Target)
If tmpRng Is Nothing Then Exit Sub
With Application
    .ScreenUpdating = False
    .EnableEvents = False
End With
For Each cl In tmpRng
    With cl
        If VarType(.Value) = vbString Then _
            .Value = UCase$(.Value)
    End With
Next
With Application
    .ScreenUpdating = True
    .EnableEvents = True
End With
Set tmpRng = Nothing
End Sub

[realniceguy5000]

Hi,

Is it possible the events were turned to False Try this then save and reopen?

Sub okon()
Application.EnableEvents = True
End Sub

[Brad_G]

Mike, it worked perfectly. Thanks.

BSG

[Brad_G]

How to I mark the displayed subject as SOLVED?

[snb]

or

Private Sub Worksheet_Change(ByVal Target As Range)
  On Error GoTo xl90
  Application.ScreenUpdating = False
  Application.EnableEvents = False
  
  For Each cl In Intersect(Me.Range("a5:z1000").SpecialCells(2, 2), Target)
     cl = UCase(cl)
  Next
  
xl90:
  Application.EnableEvents = True
End Sub

[realniceguy5000]

Hi,

Glad to help...

Here is how to mark your post solved

Click the Edit button on your first post in the thread
Click Go Advanced
Select [SOLVED] from the dropdown
Then click Save Changes.