none
Excel - Delete selection when value changes RRS feed

  • Question

  • Hi, I have this macro in one of my worksheets.

    It selects an area and deletes it when there's a value change in the area "L7:L23".

    It works well when I'm on the worksheet itself, but not when I'm my worksheet "3".

    I click a button and make the values change but it doesn't seem to trigger the macro.

    Private Sub Macro5(ByVal Target As Range)
    If Not Intersect(Target, Range("L7:L23")) Is Nothing Then
    Call Sheets("Feuille de données cachées").Select
        Range("G26:G200").Select
        Selection.ClearContents
        Sheets("3").Select
    End If
    End Sub

    What did I do wrong?

    Can you help?


    Saturday, June 23, 2018 4:32 PM

All replies

  • If you have the code in the worksheet's module and use code to change the ActiveSheet (Selected Sheet) then Excel becomes confused as to which sheet is selected.

    With VBA code, it is almost never necessary to select worksheets or ranges so if you simply reference the worksheet and range and perform the required action on the reference and range as follows then Excel will then know exactly what is required. The ActiveSheet does not change so it is also not necessary to re-select Sheets("3").

    I have assumed that the following line is meant to reference to the ActiveSheet which is Sheets("3"). If this is not the case then need more explanation, including how the code is called because it does not resemble a normal event code.

    If Not Intersect(Target, Range("L7:L23")) Is Nothing Then

    Private Sub Macro5(ByVal Target As Range)
        If Not Intersect(Target, Range("L7:L23")) Is Nothing Then
            Sheets("Feuille de données cachées").ClearContents
        End If

    End Sub

    You might find the following link to the Community is a better place to ask your questions.

    https://answers.microsoft.com/en-us/


    Regards, OssieMac

    Sunday, June 24, 2018 1:37 AM
  • Hello,

    Marco5 is not an event and it should not be triggered automatically. How did it work well for you when you are on the worksheet itself?  I would suggest you share a simple workbook with your code so we could try to reproduce your issue. And for avoid misunderstanding, would you mind detail us your reproduce steps?

    For sharing the document, you could share it via cloud storage, such as One Drive, and then put the link address here.

    Thanks for understanding,

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, June 25, 2018 6:23 AM