VBA Run-time Error 13 Type Mismatch when multiple cells changed at once

[nuvque]

Hi all,

Complete newbie here, but coming across an issue with my VBA code and I'm not sure how to fix!

The only VBA code on this spreadsheet is below, which I am using for popping up message boxes when certain cell values are changed or selected from the data validation drop down.
It works fine when one cell is changed at a time, or if I ctrl + click to select multiple cells and then paste into the cells, but if I click and drag to highlight multiple cells in any of the columns in range, and interact with the cells (either pasting a value or deleting data), I get a run-time error code '13' - type mismatch. What I want is for the pop up box to appear once for the selection.

Any help would be greatly appreciated!

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("L2:L6000,K2:K6000,BM2:BM6000,AZ2:AZ6000")) Is Nothing Then
    Select Case Target
        Case "Redeployed": MsgBox ("1. text here" & vbNewLine & "2. text here" & vbNewLine & "3. text here" & vbNewLine & "4. text here" & vbNewLine & "5. text here" & vbNewLine & "6. text here" & vbNewLine & "7. text here"), vbInformation, "Checklist for Redeployment"
        Case "Formal Notice": MsgBox ("text here), vbInformation "Reminder"
        Case "At Risk": MsgBox ("1. text here" & vbNewLine & "2. text here"), vbInformation, "Reminder"
        Case "1000": MsgBox ("text here), vbInformation, "Reminder"
        Case "2000": MsgBox ("text here), vbInformation, "Reminder"
        Case "A": MsgBox ("text here), vbInformation, "Reminder"
        Case "B": MsgBox ("text here), vbInformation, "Reminder"
        Case "C": MsgBox ("text here), vbInformation, "Reminder"
        Case "D (full&full)": MsgBox ("text here), vbInformation, "Reminder"
    End Select
    End If
If Target.Value = "" Then Exit Sub
If Not Intersect(Target, Range("BA2:BA6000")) Is Nothing Then
    MsgBox ("1. text here" & vbNewLine & "2. text here" & vbNewLine & "3. text here" & vbNewLine & "4. text here"), vbInformation, "Reminder"
    End If
    End Sub

 

[steve the fish]

If you have lots of cells in your target range then you have lots of values of your target cells. You cant just test the target value to produce one message box if you have multiple cells in your target range.

 

[nuvque]

If you have lots of cells in your target range then you have lots of values of your target cells. You cant just test the target value to produce one message box if you have multiple cells in your target range.

Not quite sure I follow, sorry! First time using VBA and no experience of working with code - so you are saying there is no way to have one message box pop up if multiple cells are selected. Is there any way around this issue, e.g. separating out the target ranges or anything? Or, to have no message box pop up if multiple cells are selected, and only pop up for individual cells that are changed?

 

[steve the fish]

You can test each individual cell in the target range with your code and get lots of message boxes or you can suppress the message box should your target range be more than one cell. Either way is possible. Id plump for the latter i think. You could cause a right mess if your target range is large and you get endless message boxes.

 

[nuvque]

You can test each individual cell in the target range with your code and get lots of message boxes or you can suppress the message box should your target range be more than one cell. Either way is possible. Id plump for the latter i think. You could cause a right mess if your target range is large and you get endless message boxes.

Yes, definitely the latter sounds best. Although I have no idea how to do this!! Do you have any example code I could use for this?

 

[steve the fish]

The easiest way is to put this at the top of the procedure:

If Target.Cells.Count > 1 Then Exit Sub

 

[Scott T]

Not quite sure I follow, sorry! First time using VBA and no experience of working with code - so you are saying there is no way to have one message box pop up if multiple cells are selected. Is there any way around this issue, e.g. separating out the target ranges or anything? Or, to have no message box pop up if multiple cells are selected, and only pop up for individual cells that are changed?

Target is the range that changed. If you only change one cell then target = the value of that cell. So when you test if target="HI" VBA is able to do that. But when you change multiple cells target is no longer a single value. To test you would need to go though each cell value and test it, does Value of cell1="HI, does Value of cell2="HI...

 

[nuvque]

The easiest way is to put this at the top of the procedure:

If Target.Cells.Count > 1 Then Exit Sub

Thank you, this worked! I guess it will have to do to only have the message box pop up when it is one selection at a time.