VBA Worksheet_Change - Type mismatch runtime error

[btc3111]

Hello all! @rollis13 was kind enough to combine 3 VBA Worksheet_Change's for me. In particular, it had to do incorporating only certain characters in certain ranges and also make said ranges only possible to be Capital letters. Everything works but when selecting multiple ranges, I receive a Microsoft Visual Basic popup stating "Run-time error '13': Type mismatch". With the Debug function highlighting this portion of the code .Value = UCase(.Value).
If anyone can fix this, it would be greatly appreciated!

Option Explicit
Private Const FCheckRgAddress As String = "M12:CI36"
Private Const FCheckRgAddress_2 As String = "L45:T60,W45:AE60,AH45:AP60"
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim xChanged As Range
    Dim xRg    As Range
    Dim xString As String
    Dim sErrors As String
    Dim xRegExp As Variant
    Dim xHasErr As Boolean
    Set xRegExp = CreateObject("VBScript.RegExp")
    xRegExp.Global = True
    xRegExp.IgnoreCase = True
    Set xChanged = Application.Intersect(Range(FCheckRgAddress), Target)
    If xChanged Is Nothing Then GoTo second
    xRegExp.Pattern = "[^A,B,C,F,I,N,O,P,S,T,X]"
    For Each xRg In xChanged
        If xRegExp.TEST(xRg.Value) Then
            xHasErr = True
            Application.EnableEvents = False
            xRg.ClearContents
            Application.EnableEvents = True
        End If
    Next
    If xHasErr Then MsgBox "These cells had invalid entries and have been cleared:"
second:
    Set xChanged = Application.Intersect(Range(FCheckRgAddress_2), Target)
    If xChanged Is Nothing Then GoTo third
    xRegExp.Pattern = "[^A,B,C,I,N,O,T,X]"
    For Each xRg In xChanged
        If xRegExp.TEST(xRg.Value) Then
            xHasErr = True
            Application.EnableEvents = False
            xRg.ClearContents
            Application.EnableEvents = True
        End If
    Next
    If xHasErr Then MsgBox "These cells had invalid entries and have been cleared:"
third:
    If Not (Application.Intersect(Target, Range("M12:CI36,L45:T60,W45:AE60,AH45:AP60")) 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

 

[Joe4]

When exactly is in the cell that is trying to be updated?
Do you have any errors in the ranges you are checking?

 

[btc3111]

Hello all! @rollis13 was kind enough to combine 3 VBA Worksheet_Change's for me. In particular, it had to do incorporating only certain characters in certain ranges and also make said ranges only possible to be Capital letters. Everything works but when selecting multiple ranges, I receive a Microsoft Visual Basic popup stating "Run-time error '13': Type mismatch". With the Debug function highlighting this portion of the code .Value = UCase(.Value).
If anyone can fix this, it would be greatly appreciated!

Sorry, I don't know how to edit my post. To elaborate, my error occurs when selecting multiple ranges and then deleting said range.

 

[btc3111]

When exactly is in the cell that is trying to be updated?
Do you have any errors in the ranges you are checking?

Sorry, should've elaborated, i'm having issues with any of the allowed characters from the code left in the cells when multi deleting them

 

[Joe4]

If you are deleting ranges in that scenario, then you DON'T want the code to run in those scenarios, right (there would be no range to run it on)!

People often prevent this sort of error from happening by adding a line like this to the top of their "Worksheet_Change" code:

If Target.CountLarge > 1 Then Exit Sub

What this does it limit the code to only running when a SINGLE cell is changed at a time.
(When you delete whole rows, whole columns, or multiple cells, multiple cells are updated at once).

If you have need for the code to run on multiple cells at once (i.e. if you were to copy one cell over to ten, and wanted the code to run on all ten), then we would need to get more creative in the code.

 

[btc3111]

If you are deleting ranges in that scenario, then you DON'T want the code to run in those scenarios, right (there would be no range to run it on)!

People often prevent this sort of error from happening by adding a line like this to the top of their "Worksheet_Change" code:

If Target.CountLarge > 1 Then Exit Sub

What this does it limit the code to only running when a SINGLE cell is changed at a time.
(When you delete whole rows, whole columns, or multiple cells, multiple cells are updated at once).

If you have need for the code to run on multiple cells at once (i.e. if you were to copy one cell over to ten, and wanted the code to run on all ten), then we would need to get more creative in the code.

You're on the money. I do need to be able to copy multiple cells. Its meant to be a used as a editable layout tool that will calculate only particular characters. Where can i go from here?

 

[Joe4]

There are a few options I can think of off the top of my head.

1. Disable the ability to delete ranges on the sheet altogether, and then write your own macro for deleting range (then you can tell it to disable events before running, so it will not trigger the "Worksheet_Change" event procedure code in that case).

2. Try to incorporate something like the "marker" logic mentioned in this thread here: Determine whether user is adding or deleting rows

 

[rollis13]

Since you will be entering letters one at a time all you have to do to avoid the issue when deleting multi-cells is to do what @Joe4 suggested and explained in his post #5.