Automatizar una tarea cuando una celda cambia en Excel

Antes, un poquito de teoría

El código VBA es un conjunto de instrucciones que un equipo ejecuta en un orden específico. Visual Basic for Applications es un lenguaje orientado a objetos... es decir, el código se hace para interactuar con los elementos u "objetos".

En Excel, los objetos pueden ser "libros", hojas, celdas o rangos de celdas o archivos externos, por lo tanto, podemos interactuar con los objetos para conseguir que ocurra alguna o varias de estas cosas:

• Cambiar las PROPIEDADES o características de un objeto (por ejemplo, cambiar el nombre de una hoja).
• Hacer que un objeto realice una "acción" que es propia de él. En VBA no se llama "acción", sino MÉTODO. (un ejemplo de método es "Activarse", en el caso de una hoja).
• Que ocurra algo cuando sobre un objeto ocurre un EVENTO (un ejemplo de evento es el hecho de que cambia el contenido de una celda).
• Que ocurra algo por igual en una COLECCIÓN. Es decir, que ocurra algo a una serie de objetos del mismo tipo (por ejemplo, insertar un dato en la celda A1 de todas las hojas de un libro).

Ahora, un poquito de práctica

En este artículo, vamos a ver cómo podemos hacer que nuestra hoja de cálculo reaccione automáticamente a una acción que nosotros deseamos, cuando ocurre un EVENTO. Concretamente, veremos qué código debemos introducir y dónde, para conseguir que cuando una celda cambia de valor (EVENTO), se desencadene una acción elegida por nosotros... por ejemplo, que se muestre un mensaje al usuario.

Empezamos por decir dónde hay que escribir el código:

• Para ello, presionamos Alt + F11, o bien en la Ficha Programador, hacemos click en el botón "Visual Basic". Se abre el Editor de Visual Basic.
• En el explorador de proyectos, seleccionamos la hoja donde está la celda que cambiará de valor:

• Con el botón derecho, hacemos click en dicha hoja y seleccionamos "Ver código".

... se nos abre una ventana a la derecha y, en ella, debemos escribir el siguiente código:

Private Sub Worksheet_Change(ByVal Target As Range)
Celda = "B7"
If Not Application.Intersect(Target, Range(Celda)) Is Nothing Then
MsgBox ("Ha cambiado el valor de la celda")
End If
End Sub

Cerramos el editor de VBA y volvemos a la Hoja1 que es la que contiene la celda sobre la que hemos escrito el código (celda B7 .... como podemos leer dentro del código).

Si escribimos algo para cambiar el valor de B7, obtendremos un mensaje:

Basta con que sustituyamos la línea:

MsgBox ("Ha cambiado el valor de la celda") 

por el código que queramos, y obtendremos una reacción al cambio sufrido por la celda B7.

También podemos hacer que la reacción se obtenga al cambiar cualquier cosa dentro de un rango de celdas (no sólo una celda). En ese caso, debemos sustituir en el código la referencia a la celda B7, por la referencia al rango de celdas (por ejemplo:  B1:C4), quedando así:

Private Sub Worksheet_Change(ByVal Target As Range)
Rango = "B1:C4"
If Not Application.Intersect(Target, Range(Rango)) Is Nothing Then
MsgBox ("Ha cambiado el valor de la celda")
End If
End Sub

¡¡Cuidado!! (Actualizacion del artículo el 15/3/2016)

Si el código que necesitamos ejecutar genera algún cambio (en vez de ser un simple MsgBox) que provoca de nuevo que se "dispare" el evento, corremos el peligro de que la macro entre en un bucle interminable o que provoque un error . Un ejemplo de ello es la siguiente macro, que al actualizar una celda de la columna A, corta la fila de la celda cambiada y la pega en la Hoja2:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim ColumnaA As Range
Set ColumnaA = Range("A:A")
Dim NLin As Byte
NLin = Target.Row

If Not Intersect(Target, ColumnaA) Is Nothing Then
   If Target.Value = "Apto" Then
     Sheets("Hoja2").Rows("4:4").Insert
     Rows(NLin & ":" & NLin).Cut _
     Destination:=Sheets("Hoja2").Rows("4:4")
   End If
End If

End Sub


... podemos ver que el cambio que genera la propia macro al cortar una fila... provoca que la macro se vuelva a ejecutar y obtengamos un error. Para evitar esto, debemos insertar unas líneas de código al principio y al final que nos aseguran que durante la ejecución de la macro no se "disparará" ningún evento:

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo Error

Dim ColumnaA As Range
Set ColumnaA = Range("A:A")
Dim NLin As Byte
NLin = Target.Row

If Not Intersect(Target, ColumnaA) Is Nothing Then
   If Target.Value = "Apto" Then
     Sheets("Hoja2").Rows("4:4").Insert
     Rows(NLin & ":" & NLin).Cut _
     Destination:=Sheets("Hoja2").Rows("4:4")
   End If
End If

Error:
Application.EnableEvents = True

End Sub

En el siguiente archivo, podemos ver el código y el funcionamiento de algunas cosas de las expuestas anteriormente:

  José Manuel Pomares Medrano