Triggering Worksheet_Change to delete the contents of the merged cell

I made a piece of code that when cell L3 changes, in turn changes the range of cells, depending on the content of L3.

L3 is a merged cell that spans from L3 to N4, and the code works great regardless, up to the point where the user is deleting content rather than entering something new.

The following code works fine for unlinked cells:

Sub Worksheet_Change(ByVal Target As Range)

        If Target.Address = Range("L3").Address Then

             If Range("L3") = Sheets("Formatering").Range("F27") Then
                 Tankpladser = "Vælg Tank"

             ElseIf Range("L3") = Sheets("Formatering").Range("F28") Then
                 Range("E11") = Sheets("Formatering").Range("L3")
                 Range("G11") = Sheets("Formatering").Range("L4")
                 Range("I11") = Sheets("Formatering").Range("L5")
                 Range("K11") = Sheets("Formatering").Range("L6")
            ElseIf Range("L3") = "" Then
                 Range("E11:E12,G11:G12,I11:I12,K11:K12").ClearContents
            End If
        End If
End Sub

      

The problem is that Range ("L3") is a merged cell and is actually Range ("L3: N4"). This causes a problem when checking if a cell is empty after deleting the cell contents.

Now I'm looking for a way to do something along these lines:

ElseIf Range("L3").MergeArea.Cells(1,1).Value = "" Then
    Range("E11:E12,G11:G12,I11:I12,K11:K12") = ""
End If

      

+3


source to share


1 answer


You can try something like this ...



Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("L3").MergeArea) Is Nothing Then
    Application.EnableEvents = False
    If Range("L3") = Sheets("Formatering").Range("F27") Then
        Tankpladser = "Vælg Tank"

    ElseIf Range("L3") = Sheets("Formatering").Range("F28") Then
        Range("E11") = Sheets("Formatering").Range("L3")
        Range("G11") = Sheets("Formatering").Range("L4")
        Range("I11") = Sheets("Formatering").Range("L5")
        Range("K11") = Sheets("Formatering").Range("L6")
    ElseIf Range("L3") = "" Then
        Range("E11:E12,G11:G12,I11:I12,K11:K12").ClearContents
    End If
    Application.EnableEvents = True
End If
End Sub

      

+4


source







All Articles