Run-time error '13': Type Mismatch

[mr.mackey]

Hi, looking for some help with this. I have the following code in my worksheet, which is supposed to pop-up a message when C19 has the value "BLUE"

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$19" And Target.Value = "BLUE" Then
MsgBox "Please ensure that you meant to select BLUE"
End If
End Sub

It works fine, however, it seems to be conflicting with a macro I have recorded that clears data in various cells. The macro code is as follows:


Sub ClearData()

Dim msgPrompt As String, msgTitle As String
Dim msgButtons As Integer, msgResult As Integer

msgPrompt = "Are you sure you want to clear all data?"
msgButtons = vbYesNo + vbQuestion + vbDefaultButton1
msgTitle = "Clear Data?"

msgResult = MsgBox(msgPrompt, msgButtons, msgTitle)

Select Case msgResult
Case vbYes

Sheets("Data Input").Select

With Application
.Calculation = xlManual
.MaxChange = 0.001
End With
ActiveWorkbook.PrecisionAsDisplayed = False

Range("C5:R8").Select
Selection.ClearContents

Range("C12:R34").Select
Selection.ClearContents

Range("C54:R55").Select
Selection.ClearContents

Range("C58:R58").Select
Selection.ClearContents

With Application
.Calculation = xlAutomatic
.MaxChange = 0.001
End With
ActiveWorkbook.PrecisionAsDisplayed = False

Case vbNo
Exit Sub
End Select
End Sub


The error I receive when I run this macro is "Run-time error '13': Type Mismatch".

Can someone help me solve this problem?

Thank you kindly!

 

[Andrew Poulsom]

Welcome to MrExcel.

In your ClearData procedure put this at the beginning:

Application.EnableEvents = False

and:

Application.EnableEvents = True

at the end. That will prevent your Worksheet_Change event procedure from firing.

 

[mr.mackey]

Thank you so much for your help!

 

[mr.mackey]

Hello again, while that solution fixed my problem. I'm now getting the same run-time error when copying and pasting in that worksheet.

Can you provide a solution for that?

Thanks,

 

[Andrew Poulsom]

On which line of code are you getting that error? Maybe try:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$C$19" Then
        If Target.Value = "BLUE"
            MsgBox "Please ensure that you meant to select BLUE"
        End If
    End If
End Sub

 

[RoryA]

Change the event code to:

Private Sub Worksheet_Change(ByVal Target As Range)
   If Not Intersect(Target, Range("C19")) Is Nothing Then
      If Range("C19").Value = "BLUE" Then
         MsgBox "Please ensure that you meant to select BLUE"
      End If
   End If
End Sub

 

[mr.mackey]

And how would I make this work for mutliple cells? Basically, I want the message box to appear if any cells from C19 to R19 are changed to BLUE.

Thanks

 

[RoryA]

Like so:

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim rngCell           As Excel.Range
   If Not Intersect(Target, Range("C19:R19")) Is Nothing Then
      For Each rngCell In Intersect(Target, Range("C19:R19"))
         If rngCell.Value = "BLUE" Then
            MsgBox "Please ensure that you meant to select BLUE"
         End If
      Next rngCell
   End If
End Sub

 

[mr.mackey]

Perfect. Thank you!