jeudi 24 mai 2018

Excel VBA Exectution of Private Sub Worksheet_Change(ByVal Target As Range) even though if-criteria is not fullfilled

I'm using Private Sub Worksheet_Change(ByVal Target As Range) to react to a changes in Range("AV9:AV" & lastrow) in each of this cells is a dropdown list which is defined as follow:

Dim lastrow2 As Long
Dim lastcell As Long

lastrow2 = Tabelle3.Range("A" & Rows.Count).End(xlUp).Offset(8).Row
lastcell = Tabelle3.Range("AH1048576").End(xlUp).Row  

For Each Cell In Tabelle3.Range(Tabelle3.Cells(9, 48), Tabelle3.Cells(lastcell, 48))

    If Cell = "" Then

            Dim MyList(2) As String

                MyList(0) = "Relevant"
                MyList(1) = "For Discussion"
                MyList(2) = "Not Relevant"


            With Tabelle3.Range("AV9:AV" & lastrow2).Validation
                .Delete
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
                     Operator:=xlBetween, Formula1:=Join(MyList, Application.International(xlListSeparator))
            End With

    End If

Next

Those lines are incorporated into a macro which fills Tabelle3with data and all necessary functions, such as the dropdown field.

The Private Sub Worksheet_Change(ByVal Target As Range) is defined as follow:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim lastrow As Long

lastrow = Tabelle3.Range("A" & Rows.Count).End(xlUp).Offset(8).Row

    On Error Resume Next

    If Not Intersect(Target, Range("AV9:AV" & lastrow)) Is Nothing And Target.Value = "Relevant" Or Target.Value = "For Discussion" Then
        Application.CutCopyMode = False
        Cells(Target.Row, "A").Resize(, 57).Copy
        Tabelle14.Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
        Tabelle14.Range("A" & Rows.Count).End(xlUp).PasteSpecial xlPasteFormats
        Tabelle14.Range("A" & Rows.Count).End(xlUp).PasteSpecial xlPasteColumnWidths

        Application.CutCopyMode = False

    End If


    If Not Intersect(Target, Range("AV9:AV" & lastrow)) Is Nothing And Target.Value <> "" Then
        Cells(Target.Row, "A").Resize(, 2).Copy
        Tabelle10.Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
        Application.CutCopyMode = False

    End If

'//Delete all duplicate rows
Set Rng = Tabelle10.UsedRange
Rng.RemoveDuplicates Columns:=Array(1)


End Sub

As you can see the first part of the Private Sub Worksheet_Change(ByVal Target As Range) 'should' only be executed If in a dropdown field in Range("AV9:AV" & lastrow) the option 'Relevant' or 'For Discussion' is selected and the second part If anything is selceted , therefore I have used Target.Value <> "". This is principally working fine but one bug occurs.

If I insert the data to Tabelle3 through the already mentioned macro, it seems the Private Sub Worksheet_Change(ByVal Target As Range) is then automatically executed for row 9 in Tabelle3and I can find its data in Tabelle14 and Tabelle10 as defined.

Does someone know what's going on here?

Aucun commentaire:

Enregistrer un commentaire