Run time error 13

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,935
Office Version
  1. 2007
Platform
  1. Windows
When im closing a worksheet i see RTE 13 Type mismatch.

I debug and this is shown in yellow.
Rich (BB code):
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.
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,637
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Nov 16, 2010
Messages
3,935
Office Version
  1. 2007
Platform
  1. Windows
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

Well-known Member
Joined
Dec 30, 2008
Messages
12,637
Office Version
  1. 365
Platform
  1. Windows
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.

VBA Code:
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

Well-known Member
Joined
Nov 16, 2010
Messages
3,935
Office Version
  1. 2007
Platform
  1. Windows
That worked with no errors.

Thanks
 

Watch MrExcel Video

Forum statistics

Threads
1,129,687
Messages
5,637,833
Members
416,985
Latest member
mrindira

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top