Lección 22: Evento Change de Worksheet y el método Intersect en VBA.

Escrito por Administrador

4 de septiembre de 2021

En esta lección vas a aprender a usar el evento Change y el método Intersect en VBA. De esta forma, vas a poder lanzar el código de programación al cambiar el contenido de una determinada celda o un rango de ellas.

Hasta ahora habíamos asociado macros a botones, y las habíamos ejecutado al pulsar sobre ellos. Esto está bien para ejecutar programas en un momento determinado, pero hay diversas situaciones donde queremos que se ejecuten acciones de manera automática al cambiar el valor de una determinada celda. Esta posibilidad nos la proporciona el evento Change de Worksheet.

Por otro lado, el método Intersect no es más que la herramienta que va a determinar si la celda que hemos modificado pertenece al rango de “vigilancia” que hemos establecido para lanzar nuestro código.

En primer lugar, vemos su sintaxis:

Private Sub Worksheet_Change (ByVal Target As Range)

'Target: Contiene la celda a la cual se cambia

If Application.Intersect(Target, Range("E4")) Is Nothing Then

Exit Sub

Else

(Líneas de código a ejecutar)

End If

End Sub

La traducción comando a comando sería:

Ejecuta el código si hay algún cambio en la página.

Private Sub Worksheet_Change (ByVal Target As Range)

Si la celda que hemos modificado (Target) NO es la E4, salimos sin hacer nada.

If Application.Intersect(Target, Range("E4")) Is Nothing Then

Exit Sub

Else

(Líneas de código a ejecutar)

En caso contrario, ejecuta el código que programemos.

Seguidamente, veremos un ejemplo para comprender el abanico de posibilidades que se nos abren con el evento Change y el método Intersect de VBA.

Primero, crea un libro nuevo y abre el editor VBA. Hasta ahora habíamos insertado módulos, pero para vigilar si en una hoja se realizan cambios, hay que programar directamente sobre ella.

Después, haz click en el objeto Hoja1 (Hoja1) en la columna izquierda, tal y como se ve en la figura.

total-operaciones-matematicas-macros

A continuación, pega el siguiente código.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

'Macro que al rellenar la distancia, si es superior a 200km, rellena en rojo el nombre de la ciudad

'Si varía algún valor de la 2ª columna

If Target.Column = 2 Then

'Guarda en la variable ThisRow el valor de la fila

ThisRow = Target.Row

'Si además, la distancia es superior a 200km, rellena de rojo la celda de la ciudad

On Error GoTo final

If Target.Value > 200 Then

Range("A" & ThisRow).Interior.ColorIndex = 3

Else

Range("A" & ThisRow).Interior.ColorIndex = xlColorIndexNone

End If

End If

final:

End Sub

Ahora rellena la hoja de cálculo como se muestra en la figura.

total-operaciones-matematicas-macros

En el ejemplo, he rellenado las distancias que existen desde la ciudad de Lorca a las ciudades del listado. Así, conforme vamos rellenando distancias, si alguna supera los 200km, automáticamente se rellenará de rojo la celda que contiene el nombre de la ciudad.

total-operaciones-matematicas-macros

Sobre todo, recuerda que Target siempre hace referencia a la celda que está siendo modificada, de esta manera,

If Target.Column = 2 Then

Significa, que si la columna modificada es la segunda, entonces….

ThisRow = Target.Row

Guarda en la variable ThisRow el valor de la fila de la celda que está siendo modificada

If Target.Value > 200 Then

Significa, que si el valor de la celda modificada es superior a 200, entonces….

Con esto llegamos al final de la vigesimosegunda lección en la que has aprendido a utilizar el evento Change y el método Intersect en VBA.

Por último, quiero invitarte a compartir tus dudas en los comentarios o en el foro. Intentaré ayudarte en todo lo que pueda, y así aprenderemos todos.

Nos vemos en el próximo capítulo.

Si te ha servido y quieres donar

Descarga el archivo del ejemplo

8 Comentarios

  1. Avatar

    Buenas tardes,
    Antes de nada agradecer el contenido de su pagina.

    Quisiera saber si existe la siguiente posibilidad.
    Ahora mismo tengo configurado que si al realizar el cambio en una celda de un determinado rango, me ejecute un macro. El código es el siguiente:

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("M1:M65555")) Is Nothing Then
    Call AgregarComentario
    End If
    End Sub

    El problema que tengo es que si le doy doble clic con el ratón sin llegar a escribir nada, al cambiar de celda me ejecuta el macro.
    Existe alguna manera de limitar esta acción? Es decir, yo quiero que la llamada al otro macro se ejecute solo cuando escriba o elimine contenido, no al dar doble clic en una celda sin llegar a escribir nada.

    Muchas gracias.

    Un saludo.

    Responder
    • Avatar

      Hola!

      Solo tienes que añadir un condicional anidado a tu código,

      Private Sub Worksheet_Change(ByVal Target As Range)
      If Not Intersect(Target, Range("M1:M65555")) Is Nothing Then
      If Target.Value <> "" Then
      Call AgregarComentario
      End If
      End If
      End Sub

      Responder
    • Avatar

      Me parece que lo probé el otro día como tú indicas. Lo que ocurría es que al vaciar una celda no se ejecutaba el macro por el condicional.

      Muchas gracias

      Responder
      • Avatar

        Buenas,

        Entonces hay que hacer una chapucilla. Prueba este código:

        Private Sub Worksheet_Change(ByVal Target As Range)

        If Not Intersect(Target, Range("M1:M65555")) Is Nothing Then

        vNew = Target.Value
        Application.EnableEvents = False
        Application.Undo
        vOld = Target.Value
        Target.Value = vNew
        Application.EnableEvents = True

        If StrConv(vNew, vbLowerCase) StrConv(vOld, vbLowerCase) Then

        Call AgregarComentario

        End If
        End If
        End Sub

        Responder
  2. Avatar

    Buenos dias

    Como podria capturar el evento INSERTAR COLUMNA ó FILA con VB Excel ?

    Gracias

    Responder
    • Avatar

      Buenos días Jorge,

      Puedes fijar el valor de una celda que no se use en la hoja y comprobar con el método Change que no cambie su valor, porque en caso de hacerlo ha sido por la introducción o eliminación de una fila más.

      Saludos.

      Responder
  3. Avatar

    Buenos dias el asunto es el siguiente ,tengo un listbox
    Private Sub txt_codigo_Change()
    Dim c As Object
    Dim celda As Object
    Dim linea As Integer
    Dim codigo As Integer

    codigo = txt_codigo.Value
    Set c = Sheets("BD")
    Set celda = c.Range("B:B").Find(codigo, lookat:=xlWhole)
    linea = celda.Row

    Me.txt_cedula.Value = c.Cells(linea, 3).Value
    Me.txt_apellidos.Value = c.Cells(linea, 4).Value
    Me.txt_nombres.Value = c.Cells(linea, 5).Value
    Me.cmb_sexo.Value = c.Cells(linea, 6).Value
    Me.txt_edad.Value = c.Cells(linea, 7).Value

    cada vez que yo actualizo el valor de txt_codigo en cualquier otra macro como eliminar o incluir me da error ,por que activa el evento change como resulevo ese error

    Responder
    • Avatar

      Hola,

      ¿Podrías crear un hilo en el foro y subir el archivo para que podamos echarte una mano?

      Saludos.

      Responder

Enviar un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

Este sitio usa Akismet para reducir el spam. Aprende cómo se procesan los datos de tus comentarios.

« L21. Trabajar con distintas hojas y libros con macros.

L23. Enviar emails con macros (Lotus). »

Categorías

marzo 2024
LMXJVSD
 123
45678910
11121314151617
18192021222324
25262728293031

Debates en el foro

Avatar
Formularios con ADO

Contacto