Run time error 13

[ipbr21054]

When im closing a worksheet i see RTE 13 Type mismatch.

I debug and this is shown in yellow.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not (Application.Intersect(Target, Range("P6:U6")) _
      Is Nothing) Then
        With Target
            If Not .HasFormula Then
                Application.EnableEvents = False
                .Value = UCase(.Value)
                Application.EnableEvents = True
            End If
        End With
    End If
End Sub

If we need to change the code thats fine,i was only using it to force the letters in P6:U6 to uppercase nowhere else.

 

[jasonb75]

Do you have code that is making changes to ranges of more than 1 cell when the workbook is closed?

The code above is will only run on a single cell so 2 or more cells changing at once will cause an error.

 

[ipbr21054]

I do so maybe this code is exagerated for what i need it to do ?
The cells in question are P6:U6 i type in each cell but sometimes lower case are entereed & i just need them to be upper case.
So maybe you know of a more basic code that would suffice.

 

[jasonb75]

I wanted to be sure that there wasn't something else I was missing before changing the code. This (untested) version should work with multiple cells being changed.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range, c As Range
Set rng = Intersect(Target, Range("P6:U6"))
If Not rng Is Nothing Then
    Application.EnableEvents = False
    For Each c In rng
        If Not c.HasFormula Then c.Value = UCase(c.Value)
    Next c
    Application.EnableEvents = True
End If
End Sub

 

[ipbr21054]

That worked with no errors.

Thanks