¿Por qué MS Excel falla y se cierra durante el procedimiento Worksheet_Change Sub?

Recomiendo esto al usarWorksheet_Change

1. No necesita el nombre de la hoja. En un módulo de código de hoja, una referencia de rango no calificada se refiere a esa hoja. Dicho esto, es más claro utilizar el Mecalificativo. Si intenta utilizar otra hoja, califique la referencia de rango con esa hoja.

2. Siempre que trabaje con Worksheet_Changeeventos, cambie siempre Offlos eventos si está escribiendo datos en cualquier celda. Esto es necesario para que el código no vuelva a activar el evento Cambio y entre en un posible bucle sin fin.

3. Siempre que desactive eventos, utilice el manejo de errores para volver a activarlos; de lo contrario, si recibe un error, el código no se ejecutará la próxima vez.

Prueba esto

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo Whoa
    
    Application.EnableEvents = False
    
    Me.Range("A1:A8").Formula = "=B1+C1"
    
Letscontinue:
    Application.EnableEvents = True
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume Letscontinue
End Sub

Pocas cosas más que quizás quieras saber cuando trabajes con este evento.

Si desea asegurarse de que el código no se ejecute cuando se cambia más de una celda, agregue una pequeña marca

Private Sub Worksheet_Change(ByVal Target As Range)
    '~~> For Excel 2003
    If Target.Cells.Count > 1 Then Exit Sub
    
    '
    '~~> Rest of code
    '
End Sub

Se CountLargeintrodujo en Excel 2007 en adelante porque Target.Cells.Countdevuelve un Longvalor que puede generar errores en Excel 2007 debido al aumento del recuento total de celdas.

Private Sub Worksheet_Change(ByVal Target As Range)
    '~~> For Excel 2007
    If Target.Cells.CountLarge > 1 Then Exit Sub
    '
    '~~> Rest of code
    '
End Sub

Para trabajar con todas las celdas que fueron modificadas use este código

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim aCell As Range
    
    For Each aCell In Target.Cells
        With aCell
            '~~> Do Something
        End With
    Next
End Sub

Para detectar cambios en una celda en particular, use Intersect. Por ejemplo, si se produce un cambio en Cell A1, se activará el siguiente código

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Me.Range("A1")) Is Nothing Then
        MsgBox "Cell A1 was changed"
        '~~> Your code here
    End If
End Sub

Para detectar cambios en un conjunto particular de rango, utilícelo Intersectnuevamente. Por ejemplo, si se produce un cambio en el rango A1:A10, se activará el siguiente código

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Me.Range("A1:A10")) Is Nothing Then
        MsgBox "one or more Cells in A1:A10 range was changed"
        '~~> Your code here
    End If
End Sub

Nota : Si recibió un error anteriormente e hizo los cambios anteriores y su código aún no funciona, entonces es posible que los eventos no se hayan restablecido. En Immediate Window, escriba Application.EnableEvents = Truey presione la ENTERtecla. Esto lo restablecerá a True. Si no ve el archivo Immediate Window, presione la tecla de acceso directo Ctl+ Gpara iniciar el archivo Immediate Window.

Dec 13 '2012 14:12 Siddharth Rout