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 to share