VBA code not working

Question

Hi,

I use a VBA code in Excel but it does not trigger. Does anybody know what 's wrong? Here is the code:

Dim i As Integer
Dim temp As Variant

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCells As Range

    ' The variable KeyCells contains the cells that will
    ' cause an alert when they are changed.
    Set KeyCells = Range("A1:C10")
   
    If Not Application.Intersect(KeyCells, Range(Target.Address)) _
           Is Nothing Then

        ' Display a message when one of the designated cells has been
        ' changed.
        ' Place your code here.
        i = Effacer()
      
    End If
End Sub

 

Private Function Effacer() As Integer
    If MsgBox("Supprimerligne6?", vbYesNo, "Test") = vbYes Then

    Sheets.Select ("Sheet1")
    Rows.Delete ("6")
    Sheets.Select ("Sheet1(2)")
    Rows.Delete ("6")
   
   
   
    End
    Effacer() = 1
End Function

Thanks in advance

---

fx

Answer 1

Sheets.Select ("Sheet1") and Rows.Delete("6") aren't valid Excel VBA.

Instead of End on a line by itself (which ends code execution completely), you should have End If.

Try this version (I don't know what Temp is for, or why you declare the variable i at the module level, but I didn't change that):

Dim i As Integer
Dim temp As Variant

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCells As Range

    On Error GoTo ErrHandler

   ' The variable KeyCells contains the cells that will
    ' cause an alert when they are changed.
    Set KeyCells = Range("A1:C10")

    If Not Application.Intersect(KeyCells, Target) Is Nothing Then
        Application.EnableEvents = False
        ' Display a message when one of the designated cells has been changed.
        i = Effacer
        Application.EnableEvents = True
    End If

Exithandler:
    Application.EnableEvents = True
    Exit Sub

ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume Exithandler
End Sub

Private Function Effacer() As Integer
    If MsgBox("Supprimerligne6?", vbYesNo, "Test") = vbYes Then
        Sheets("Sheet1").Rows(6).Delete
        Sheets("Sheet1(2)").Rows(6).Delete
    End If
    Effacer = 1
End Function

---

Regards, Hans Vogelaar (http://www.eileenslounge.com)

Answer 2

A lot of thanks Hans.

Still there persists another mistake: I successfully ran the code but now it doesn't work anymore. I guess it is due to a bad macro definition. When I try to run it again, it asks me to give a name to the macro. Then I save the macro Under "Erase1" and it generates a module with Erase1 as title but the module is empty, while the code persists in the VBA objects of the workbook. I think there is a conflict. Could you confirm?

---

fx

Answer 3

Actually, the code only works when my cursor is set into the subroutine "Effacer". Can you tell me what's wrong?

Thanks

---

fx

Answer 4

OK I solved the issue, even I don't know how.

Many thanks

---

fx