locked
VBA code not working RRS feed

  • 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

    Monday, January 13, 2014 6:03 AM

Answers

  • 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)

    Monday, January 13, 2014 6:41 AM

All replies

  • 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)

    Monday, January 13, 2014 6:41 AM
  • 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

    Monday, January 13, 2014 7:07 AM
  • Actually, the code only works when my cursor is set into the subroutine "Effacer". Can you tell me what's wrong?

    Thanks


    fx

    Monday, January 13, 2014 8:03 AM
  • OK I solved the issue, even I don't know how.

    Many thanks


    fx

    Monday, January 13, 2014 8:48 AM