Mismatch error for macro when merged cell is cleared

[chongjoey]

Hi, I am currently writing a code in which a messagebox for tax exemption will be shown when D11:F11 is "FOOD AND BEVERAGE" or "INSURANCE" nature of business..However, mismatch error is shown when I clear the text in D11:F11. Please help. Thanks!

My code is as below:

Public Curval As String

Private Sub Worksheet_Activate()
Curval = Range("D11")
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Curval = Range("D11")
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 4 And Target.Row = 11 And Target <> Curval Then
If Target = "RELIGIOUS ORGANISATION" Or Target = "EDUCATION / TRAINING" Then
StartMsg = MsgBox("Is tax exempted for this client?", vbYesNo)

If StartMsg = vbNo Then
Range("D14").Value = "N"
Else
Range("D14").Value = "Y"
End If

End If
End If
End Sub

 

[DanteAmor]

Hi @chongjoey. Welcome to the MrExcel forum. Please accept my warmest greetings and sincere hope that all is well.

First, you don't want to use merged cells, especially if you're going to use them in VBA.

When using the Change event in a merged cell, in Target you have the set of modified cells, that is, in this case when you delete cell D11, you are modifying 3 cells at the same time, D11, E11 and F11.
So in target you have a range of cells. So the error is because you are asking for a value, but in Target you have an array with several values.

For this example it is necessary to check the value of the first cell D11, since for this exercise E11 and F11 do not matter.

Try:

Public Curval As String

Private Sub Worksheet_Activate()
  Curval = Range("D11").Value
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Curval = Range("D11").Value
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim StartMsg As VbMsgBoxResult
 
  If Target.Column = 4 And Target.Row = 11 And Target.Cells(1).Value <> Curval Then
 
    If Target.Cells(1).Value = "RELIGIOUS ORGANISATION" Or Target.Cells(1).Value = "EDUCATION / TRAINING" Then
      StartMsg = MsgBox("Is tax exempted for this client?", vbYesNo)
     
      If StartMsg = vbNo Then
        Range("D14").Value = "N"
      Else
        Range("D14").Value = "Y"
      End If
    End If
  End If
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​