Search code examples
excelvbatype-mismatch

Type mismatch, conflicting macros

The first VBA script I have is working fine,

Private Sub Worksheet_Change(ByVal Target As Range)
If Not (Application.Intersect(Target, Range("C9:G9,C15:G15,C21:G21,C27,G27")) _
  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

This makes all text entered in a cell capitalize after entered by user

Then I have a macro button to clear certain cells

Sub inputcaps()
Range("C9", "G9").Value = ""
End Sub

After pressing this macro it works fine, but I do get a "Run-time error '13': Type mismatch" error and the first script stops working and I have to restart the excel sheet.

How can I fix this??

The debug takes me to .Value = UCase(.Value) from the first script

thank you

Solution

  • You need to do following this code:-

    Not More Issue in code simple First EnableEvents = false and before finished march write EnableEvents = true

    Like this Code :

     Sub inputcaps()
   Application.EnableEvents = False

    Range("C9", "G9").Value = ""

   Application.EnableEvents = True

End Sub