Type Mismatch 13 Error when deleting from a merged cell - VBA

user_6781190 0
Viewed 69

I having an error when I press delete button over a merged cell (Type Mismatch 13), however, this error only shows when the cell is merged, otherwise, no error is raised and I cannot understand why, please follow down the code:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target = Range("AG3") Then

    If Target.Value = 5 Then
        Sheets("Sheet6").CommandButton6.Visible = False

    Else
         Sheets("Sheet6").CommandButton6.Visible = True

End If
End If

On Error Resume Next
     Sheets("Sheet6").CommandButton6.Visible = False
On Error GoTo 0

End Sub

What is missing?

The cell AG3 has a number that varies from 0 to 5

vba excel
2 Answers

When you delete a value from a merged cell, Target gets the whole range, rather than just the first cell. This behavior is different from when you add a value to a merged cell (in which case only the first cell from the merge area gets passed in).

For example - if I merge D2:D8 on a worksheet with this event handler:

Private Sub Worksheet_Change(ByVal Target As Range)
    Debug.Print Target.Address, TypeName(Target.Value)
End Sub

Entering "Test" into the merged cell gives me:

$D$2          String

and deleting that value gives me:

$D$2:$D$8             Variant()

I only just learned that.

So consider looking at Target.Cells(1) instead of Target (depending on your exact use case)

user_4788840

A Worksheet Change: Show Command Button

  • Target can be any number of cells. Therefore it is best to get the intersection of it and the desired cell and if they were intersecting, continue with this intersection (actually the desired cell).
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim iCell As Range: Set iCell = Intersect(Range("AG3"), Target)
    If iCell Is Nothing Then Exit Sub
    
    If iCell.Value = 5 Then
        Sheets("Sheet6").CommandButton6.Visible = True
    Else
        Sheets("Sheet6").CommandButton6.Visible = False
    End If
 
End Sub
user_98140690
Related