viernes, 30 de marzo de 2012

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:

http://1.bp.blogspot.com/-8QBCzP74CwQ/T1OlYc6YzuI/AAAAAAAAA88/hiQEEajQk1A/s1600/Descargar-icono.PNG


  José Manuel Pomares Medrano

72 comentarios:

  1. Quisiera saber como podría hace para que el archivo meda del codigo siguiente sea una variable para insertar fotos en excel:

    Sub InsertFoto()
    '
    ' InsertFoto Macro
    ' Insetar una foto en un rango de celdas
    '

    '
    Range("B3:I9").Select
    ActiveSheet.Pictures.Insert( _
    "D:\Documents and Settings\FAVIO\Mis documentos\Mis imágenes\meda.jpg").Select
    End Sub

    ResponderEliminar
  2. Tengo un problema usando el codigo del primer ejemplo : Si la celda B7 tiene la formula "=C7" . Cuando hago cambios en la celda C7 , tambien cambia el valor de la celda B7 pero no aparece el Mensaje

    ResponderEliminar
    Respuestas
    1. La diferencia es que Worksheet_Change no detecta que una celda se haya "calculado", sino que sólo detecta cuando hay un cambio escrito y aceptado con Intro.

      Para lo que tu quieres, prueba con el evento "Calculate". Es decir... con un código similar a esto:

      Private Sub Worksheet_Calculate()
      MsgBox ("Hola")
      End Sub

      Saludos

      Eliminar
  3. Excelente artículo. Justo lo que buscaba.

    ResponderEliminar
  4. Gracias por el aporte, agradecería tu ayuda pues tengo el siguiente problema, necesito que cuando cambia la celda B7 a un dato diferente recien ejecute la macro, pero si se ingresa la misma información que tenía que no haga nada, y con con estos comandos se ejecute si o si cuando se edita el campo, independientemente de que se agrega, me podrías ayudar por favor?
    gracias

    ResponderEliminar
    Respuestas
    1. No te entiendo.
      Si por un lado quieres que se ejecute la macro "SI o SI" cuando se edita el campo, pero por otro lado quieres que si se "ingresa" la misma información no se ejecute la macro... eos no es compatible.

      Es decir, si se ingresa la misma información...se está editando el campo. ¿No te refieres a eso?

      Eliminar
  5. Buen día, amigo necesito de su ayuda. Me han ordenado, tengo que hacer una pequeña base de datos con 40 personas, a los cuales tengo que incluir todos sus datos Obviamente y su fotografía. Para realizar consultas futuras en un formato determinado e imprimirlas cuando sea el caso. Agradecería su ayuda muchas gracias

    ResponderEliminar
  6. Hola por favor me puedes explicar que significa Not Application.Intersect(Target, Range(Celda)) Is Nothing

    espero tu respuesta muchas gracias

    ResponderEliminar
    Respuestas
    1. En primer lugar:
      El método Intersect nos da como resultado un rango que es la intersección de dos o más rangos. En el ejemplo, los dos rangos evaluados son "Target" (la celda que cambia) y "Celda". Por lo tanto, si la celda que cambia coincide que es Celda, entonces obtenemos ALGO... que es un rango.
      En segundo lugar, evaluamos:
      Si hemos obtenido ALGO, o lo que es lo mismo, SI lo que obtenemos NO ES "NADA" (If Not ............is nothing).... then (entonces)... que ocurra lo que nosotros pongamos a continuación mediante código.

      Espero haberte ayudado

      Eliminar
  7. cuando se utiliza "supr" para borrar el contenido de la celda marca error

    ResponderEliminar
  8. el error que sale es:
    Se ha producido el error '13' en tiempo de ejecucion:
    No coinciden los tipos

    ResponderEliminar
    Respuestas
    1. He copiado el código expuesto en el artículo y funciona perfectamente. Repasa todo el proceso.
      Saludos

      Eliminar
  9. Desde Costa Rica en Centroamérica, MUCHAS GRACIAS!!!

    ResponderEliminar
  10. Funciono de maravilla.. muchas gracias :D

    ResponderEliminar
  11. Saludos!!!, desearía que ocurra el evento pero no solo al escribir en la celda y dar intro, sino también cuando borre el dato de esta celda. Será eso posible?

    ResponderEliminar
    Respuestas
    1. Borrar elcontenido de la celda también está considerado un cambio, por lo tanto también funcionará este código.
      Saludos.

      Eliminar
  12. Tengo la siguiente duda:

    He utilizado el sigueinte codigo para que cada vez que modifique una celda me limpie o borre otra celda

    Private Sub Worksheet_Change(ByVal Target As Range)

    If Target = Range("A2") Then
    Range("B2").Value = ""
    End If

    End Sub


    Pero solo funciona con una celda, ¿Hay forma de que cada que modifique cualquier celda de la Columna A se modifique la celda en la misma linea de la columna B?

    Por ejemplo: si modifico la Celda A3 se modifique la Celda C3, si modifico la Celda A15 se modifique la Celda B15 y así sucesivamente.

    Por tu atención muchas gracias!!!

    ResponderEliminar
    Respuestas
    1. Un código que realizaría esa acción podría ser este:

      Private Sub Worksheet_Change(ByVal Target As Range)
      If Not Intersect(Target, Range("A:A")) Is Nothing Then
      MiFila = Intersect(Target, Range("A:A")).Row
      Range("B" & MiFila) = ""
      End If
      End Sub

      Eliminar
    2. La siguiente macro limpia el contenido de la celda de la derecha, cuando en una celda de la columa A hay una modificación:

      Private Sub Worksheet_Change(ByVal Target As Range)
      Columna = "A:A"
      If Not Application.Intersect(Target, Range(Columna)) Is Nothing Then
      Range("B" & Target.Row).ClearContents
      End If
      End Sub

      Eliminar
    3. Grande!! Esto es justo lo que necesitaba, mil gracias!!

      Eliminar
  13. Hola, me resulto la macro, pero al igual que una persona que comenta anteriormente, me gustaria que la macro se ejecte solo cuando cambia a un valor definido, por ejemplo de aprobado a rechazado, no cada vez que cambia sin importar el contenido,. Eso es posible??

    Atte Paula

    ResponderEliminar
    Respuestas
    1. Deberías hacer dos macros:

      ..........Una de ellas, tal y como explica el artículo, dentro de la hoja:

      Private Sub Worksheet_Change(ByVal Target As Range)
      Celda = "B7"
      If Not Application.Intersect(Target, Range(Celda)) Is Nothing Then
      Call Ejecutar
      End If
      End Sub

      ..........Otra macro dentro de un módulo estandar:

      Sub Ejecutar()
      Celda = Range("B7")
      If Celda = "Rechazado" Then MsgBox "Hola"
      End Sub

      --------------------------------
      La segunda macro que se llama "Ejecutar" lo que hace es comprobar de forma externa (no desde dentro del código de la hoja) el contenido de la celda (ya sea "Aprobado" o "Rechazado"...etc..).

      Saludos.

      Eliminar
    2. Muchas gracias....

      Me resulta siempre y cuando le de ejecutar en forma manual, no cuando se produce el cambio de la celda. Como soluciono esto??

      Y otra cosa, necesito que esta macro se ejecute en todas las celdas de una columna y que el MsgBox tome el valor de una celda en particular, segun quien esta rechazado.

      En resumen lo que necesito es una planilla de calculo de notas, ya tengo la columna que me avisa si esta aprobado o rechazado, pero necesito que se levante un mensaje cada vez que aparezca un rechazado y con el nombre de quien esta rechazado. (No se si se entiende)

      Si me pudieran ayudar por fa.
      Muchas gracias nuevamente.

      Eliminar
    3. Este comentario ha sido eliminado por el autor.

      Eliminar
    4. Prueba con la siguiente macro, donde en la columna B están todos los "Rechazados" o "No rechazados" y en la columna C todos los nombres de las personas.

      En este caso el código se introduce en la hoja y ya está (no es necesario otra macro)

      Private Sub Worksheet_Change(ByVal Target As Range)
      Columna = "B:B"
      If Not Application.Intersect(Target, Range(Columna)) Is Nothing Then
      If Range("B" & Target.Row) = "Rechazado" Then MsgBox Range("C" & Target.Row)
      End If
      End Sub

      Eliminar
  14. Estimados:

    Como puedo hacer mas fácil esta macro ya que debo llegar a mas celdas:

    Sub Ejecutar()
    If Range("d2") = "No pasa" Then MsgBox Range("e2")
    If Range("d3") = "No pasa" Then MsgBox Range("e3")
    If Range("d4") = "No pasa" Then MsgBox Range("e4")
    If Range("d5") = "No pasa" Then MsgBox Range("e5")
    If Range("d6") = "No pasa" Then MsgBox Range("e6")
    End Sub

    ResponderEliminar
    Respuestas
    1. Sub Ejecutar()
      Dim i As Integer
      For i = 2 To 30
      If Range("d" & i) = "No pasa" Then MsgBox Range("e" & i)
      Next
      End Sub


      El número 30 utilizado en la tercera línea es un ejemplo del límite hasta dónde se debe hacer el reconocimiento de valores en celdas

      Eliminar
  15. Muchas gracias por el articulo, ha sido de mucha ayuda.

    Tengo un problema. Quiero que al cambiar el valor en una celda, si ahora esta vacia no se realice la tarea. Normalmente los valores de las celdas son x o celda vacia, dependiendo si se realizo o no una tarea.

    Pongo mi codigo para mayor referencia.

    Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Count > 1 Then Exit Sub

    Dim i As Integer
    Dim Val As String

    Val = 0
    i = 0
    Com = 0
    l = 0
    k = 0
    Val = Target.Value 'Valor normalmente de x o celda vacia

    If Not Intersect(Target, Range("B7:CS50")) Is Nothing Then

    i = Target.Row

    If i Mod 2 = 0 Then
    k = Target.Column
    l = Target.Row - 1

    Com = Cells(l, k).Value

    If Com = Val Then
    MsgBox "Impossible de remplir cellule Orange et Bleu. Remplir cellule Orange si intervention physique ou reparation"
    Cells(i, k).Value = ""

    End If

    End If

    If i Mod 2 <> 0 Then

    k = Target.Column
    l = Target.Row + 1
    Com = Cells(l, k).Value


    If Com = Val Then

    MsgBox "Impossible de remplir cellule Orange et Bleu. Remplir cellule Bleu si contrôle fait et non anormalité"
    Cells(i, k).Value = ""

    End If

    End If

    End If

    ResponderEliminar
    Respuestas
    1. No se muy bien la relación de tu pregunta con el código que pones.
      Si lo que necesitas es lo que comentas al principio, el código podría ser:

      Private Sub Worksheet_Change(ByVal Target As Range)
      If Not Application.Intersect(Target, Range("A1")) Is Nothing Then
      If Target <> "" Then Call MiMacro
      End If
      End Sub



      Donde MiMacro es la macro que lanzaría la terea en el caso de que la celda que ha cambiado no esté vacía.
      Donde Range("A1") es el rango donde se quiere aplicar la macro.

      Eliminar
  16. Buenas noches.

    Con relación al tema de que se ejecute una Macro cuando se da enter en la celda seleccionada, tengo una inquietud; si quiero que sea no en una celda si no en varias celdas diferentes pero dentro de la misma hoja, cómo debo escribir el código? Porque solo me permite en esa hoja un solo Private Sub Worksheet_Change(ByVal Target As Range), entonces como diferencio éste de los otros? Me explico con un ejemplo.

    En la Hoja 1, celda D4 debe ejecutarse la Macro 1 cuando presione enter.
    Estando en la misma hoja 1, celda I4 debe ejecutarse la Macro 2 cuando se presione Enter.
    Así sucesivamente varias veces en distintas Celdas.

    Todo lo anterior radica en que debo ingresar información en diferentes celdas que no están unas al lado de las otras, sino que debo pasear por toda la hoja. La idea es que cuando presione Enter, inmediatamente me traslade a la celda de previamente le haya programado.

    Espero haber sido un poco claro.

    Muchas gracias.

    ResponderEliminar
    Respuestas
    1. La solución es esta:

      Private Sub Worksheet_SelectionChange(ByVal Target As Range)

      Set rgUnion1 = Union(Range("A2"), Range("A6"))
      Set rgUnion2 = Union(Range("C2"), Range("C6"))

      If Not Intersect(Target, rgUnion1) Is Nothing Then
      Call MiMacro1
      End If

      If Not Intersect(Target, rgUnion2) Is Nothing Then
      Call MiMacro2
      End If

      End Sub

      ....Es conveniente poner también en un módulo estándar la siguiente declaración de variables:

      Public rgUnion1 As Range
      Public rgUnion2 As Range


      ....Todo este código, lo que hace es que:

      1) Si la selección cambia HACIA la celda A2 o HACIA la celda A6, entonces se ejecuta la macro MiMacro1

      2) Si la selección cambia HACIA la celda C2 o HACIA la celda C6, entonces se ejecuta la macro MiMacro2

      Como ves, te he puesto un ejemplo para el caso en el que quieras que con VARIAS posibilidades de selección se ejecute una determinada macro. En el caso de que no fuese neecsario, no utilizaríamos el método UNION.

      Saludos

      Eliminar
    2. Muchísimas gracias por la ayuda, me sirvió muchísimo. Altamente agradecido.

      Eliminar
  17. Buena noche.
    Me encuentro en la situación en que quisiera hacer una macro que cuando me encuentre en la celda J6 y le de un enter automáticamente me inserte una columna y ya después de haberla insertado se vaya a la celda I6.

    Espero me puedas apoyar.
    De antemano gracias.

    ResponderEliminar
  18. Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Dim Micelda As Range
    Set Micelda = Range("J7")

    If Not Intersect(Target, Micelda) Is Nothing Then
    Micelda.EntireColumn.Insert shift:=xlLeft
    End If

    End Sub

    ...Puedes cambiar xlLeft o xlRight según donde quieras que inserte la columna (a la izquierda de la selección o a la derecha)
    Saludos.

    Como ves, también he supuesto que al presionar ENTER la selección cambia a J7

    ResponderEliminar
  19. Hola,
    He visto la operación de todo lo explicado y la verdad es genial que todas funcionan OK.
    Ahora, me gustaría preguntar ¿como hacer que se ejecute la macro si la celda que cambiará de valor esta en otra hoja y el valor se transfiere de hoja1 a hoja2 con un simple =Hoja1!A1.
    Saludos

    ResponderEliminar
    Respuestas
    1. El evento Worksheet_Change se ejecuta cuando cambia una celda MEDIANTE TECLADO. Por lo tanto, con lo que propones, vamos a ver 2 posibilidades:

      1- Alguien cambiará mediante teclado la celda de la hoja1, ¿no? Si es así... (que ya sé que no tiene por qué) puedes poner la macro en un evento de la hoja 1... o en definitiva de la hoja donde se produce el cambio original mediante teclado.

      2- Otra posibilidad es utilizar otro evento, sustituyendo TODA la línea "Private Sub Worksheet_Change(ByVal Target As Range)" por "Private Sub Worksheet_Calculate()", puesto que esta última línea de inicio de la macro provoca la ejecución cuando una hoja ha sufrido un recálculo.

      Espero haberte ayudado. Saludos.

      Eliminar
    2. Gracias, probé ambas y la que me mejor me convino utilizar fue la opción 1.

      Eliminar
  20. Hola muchas gracias, hay alguna manera de que se envíen por correo los cambios realizados?

    ResponderEliminar
  21. Buenos días. Me gustaría saber como puedo realizar el proceso sobre este caso. Imaginemos que en la columna A, introduzco los valores : "apto" o "no apto". Mi intención es que cuándo introduzco directamente apto o bien cambio de No Apto a apto, me coja el valor entero de esa fila, lo elimine de esa hoja y me lo introduzca en la Hoja 2, indicándome a mayores la fecha exacta (y que se fija) del momento del cambio...mil gracias

    ResponderEliminar
  22. Hay algunas cosas que no entiendo:
    1) "me coja el valor entero de esa fila" Esto no lo entiendo porque una fila no tiene un valor. Supongo que se refiere a todos los valores que hay en las celdas de esa fila... es decir, la fila entera.
    2)"...me lo introduzca en la hoja 2" ... en la hoja 2 ¿dónde?
    3)¿Qué significa indicar a mayores en la frase "...indicándome a mayores"

    Si me solucionas estas dudas, intentaremos resolverlo.
    Gracias y un saludo.

    ResponderEliminar
  23. Muchisimas gracias por su pronta respuesta.
    Trato de aclararles.
    1) si me referia a lo que indican. Cortar la fila en la que se ha modificado la celda correspondiente al valor apto, que desaparezca de hoja 1 y pegarla como datos en la hoja 2 en la fila por ejemplo 4 (siempre en esta fila. Seria siempre insertar para mantener los datos que ya hibiese en esa hoja) que es donde se inicia la BDD de alumnos aptos. Asi tendria BDD de no aptos en hoja 1 o por ejemplo tareas pendientes y en hoja 2 BDD de aptos o tareas finalizadas. Me gustaria que se añadiese tambien en automatico la fecha de realizacion del cambio de no apto a apto en la hoja 2 y en la fila que acabamos de incorporar.

    Creo que se soluciona en este punto las tres dudas que les genere en mi anterior comentario.

    ResponderEliminar
    Respuestas
    1. Esta sería una posibilidad:

      Private Sub Worksheet_Change(ByVal Target As Range)

      Application.EnableEvents = False

      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

      Application.EnableEvents = True

      End Sub

      Eliminar
  24. Muchas Gracias. Me funciona correctamente pero hay dos puntos que no se si se podrían hacer (me explique yo mal en el comentario anterior. Mil disculpas). 1) En lugar de seleccionar toda la fila de la Hoja 1 que contenga en la columna A el valor Apto, se podría seleccionar por ejemplo un rango concreto de la A:6 a la G6 y pegar este rango como han hecho siempre en la fila 4 de la hoja 2? (de esta forma podría tener en la Hoja 2 unas fórmulas de cálculo? 2)la idea es incluir una fórmula en esa columna de la hoja 2 que me dejase como fija la fecha en la que he pasado a alguien de No Apto a Apto. 3) podría desaparecer de la hoja 1 la fila correspondiente al rango que se ha cortado para seguir teniendo en automático la BDD de forma seguida sin huecos?? Un millón de gracias por su ayuda!!!!!!!!!!

    ResponderEliminar
  25. Es complicado diseñar una macro así de compleja comunicándonos mediante comentarios de un blog, puesto que las explicaciones no están del todo claras, exceden de lo que es el tema central del artículo y se acercan a lo que es consultoría.
    No puedo atender de forma ilimitada a todas las preguntas que se puedan hacer mediante este foro y además, algunas preguntas requieren un desarrollo propio de una consultoría o aplicación con un desarrollo complejo.
    Espero que me discupe y lo entienda.
    Saludos.

    ResponderEliminar
  26. Totalmente comprensible. Muchas gracias por la ayuda prestada!!
    Un saludo

    ResponderEliminar
  27. Buenos días. Una única pregunta sobre la programación que me enviaron. Esto me funciona para una BDD ya creada, pero si introduzco una fila más con datos en hoja1 me da error... me podrían ayudar con este tema concreto?. Mil gracias

    ResponderEliminar
    Respuestas
    1. Sustituye el código anterior por el siguiente (en el que se introduce una administración de errores):

      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

      Eliminar
  28. funciona fantásticamente bien!!! los otros temas que les había preguntado he sido capaz de solucionarlos yo mediante la grabación de macros automáticas y viendo luego los códigos. De nuevo mi más sincera felicitación por su blog y mil gracias por su gran ayuda.!!!!!!!!!

    ResponderEliminar
  29. Buenas noches,
    Tengo el siguiente código que ejecuta una macro al cambiar resultado de una celda con fórmula (worsheet_calculate), funciona correctamente a excepción del primer dato que introduzco al abrir el libro y no se que solución tiene, espero me puedan ayudar.
    Saludos y gracias

    Private Sub worksheet_calculate()
    Application.ScreenUpdating = False
    Static semana1, semana2, semana3, semana4

    If Range("b8").Value <> semana1 Then
    semana1 = Range("b8")
    Range("b8").Copy
    Range("e8").PasteSpecial Paste:=xlPasteValues
    Worksheets("hoja2").Range("d7").PasteSpecial Paste:=xlPasteValues
    End If

    If Range("b17").Value <> semana2 Then
    semana2 = Range("b17")
    Range("b17").Copy
    Range("e8").PasteSpecial Paste:=xlPasteValues
    Worksheets("hoja2").Range("d7").PasteSpecial Paste:=xlPasteValues
    End If

    If Range("b26").Value <> semana3 Then
    semana3 = Range("b26")
    Range("b26").Copy
    Range("e8").PasteSpecial Paste:=xlPasteValues
    Worksheets("hoja2").Range("d7").PasteSpecial Paste:=xlPasteValues
    End If

    If Range("b35").Value <> semana4 Then
    semana4 = Range("b35")
    Range("b35").Copy
    Range("e8").PasteSpecial Paste:=xlPasteValues
    Worksheets("hoja2").Range("d7").PasteSpecial Paste:=xlPasteValues
    End If
    Application.CutCopyMode = False
    End Sub

    ResponderEliminar
    Respuestas
    1. Me falta saber, al menos, dos cosas:

      1) El objetivo general de la macro
      2) El motivo de utilizar las variables del tipo static semana1, semana2, semana3, semana4

      Quizá la seguna cuestión quede respondida con la primera.

      Eliminar
  30. Hola,
    Tengo en Hoja1 la columna "B" con tablas del tipo "B2:B8", "B11:B17", "B20:B26", así varias tablas, siendo la cabecera "Semana1", "Semana2", "Semana3" y la última celda (B8,B17,B26), es el resultado de la suma de cada tabla. Necesito copiar ese resultado cuando cambia algún dato de cada tabla a la "Hoja2" y siempre tiene que ser el último que cambie.
    La idea de utilizar la variable static es para que al utilizar el IF THEM detecte el cambio con el anterior resultado y solo ejecute esa linea de código.
    Posiblemente haya una opción más sencilla, que no se como hacer.
    Saludos

    ResponderEliminar
    Respuestas
    1. Esta sería una de las posibles soluciones. Para ella, es necesario habilitar unas celdas que almacenen el último valor calculado en B8, B17, etc. Yo he puesto como ejemplo las mismas celdas, pero de la columna A (A8, A17, etc.).

      Está comprobado y funciona.

      Tambien he supuesto que puede haber más de un recálculo a la vez y que estos pueden venir de otras hojas y no debidos a introducción de datos en Hoja1 (aunque intuyo que esto no hubiera hecho falta).

      Si los recálculos de las celdas B8, B17, etc. hubiesen venid SIEMPRE debido a cambios introducidos por teclado desde las celdas de cada tabla se podría haber hecho todo mediante otro evento y quizá más fácil. En cualquier caso, la solución que aporto es polivalente.

      IMPORTANTE: ANTES DE UTILIZAR LA MACRO POR PRIMERA VEZ, HAY QUE ESCRIBIR EN A8, A17, ETC. LOS MISMOS VALORES QUE HAYA EN B8, B7, ETC. (ESTO SOLO SE HACE UNA ÚNICA VEZ).


      Private Sub Worksheet_Calculate()

      Application.ScreenUpdating = False

      Dim Semana1, Semana2, Semana3, Semana4
      Dim ResultadoACopiar As Double

      Semana1 = Range("A8")
      Semana2 = Range("A17")
      Semana3 = Range("A26")
      Semana4 = Range("A35")

      If Range("B35") <> Semana4 Then ResultadoACopiar = Range("B35")
      If Range("B26") <> Semana3 Then ResultadoACopiar = Range("B26")
      If Range("B17") <> Semana2 Then ResultadoACopiar = Range("B17")
      If Range("B8") <> Semana1 Then ResultadoACopiar = Range("B8")

      Range("E8") = ResultadoACopiar
      Hoja2.Range("D7") = ResultadoACopiar

      Range("A8") = Range("B8")
      Range("A17") = Range("B17")
      Range("A26") = Range("B26")
      Range("A35") = Range("B35")
      End Sub

      Algunos apuntes más:

      -Las variables Static se deben utilizar en módulos privados de Hoja.

      -No es necesario utilizar "Pegar valores" con macros casi nunca. En su lugar, basta con igualar el valor de una celda a otra.

      -Es más óptimo poner en una variable el ResultadoACopiar y luego ordenar el establecimiento del valor en las celdas correspondientes una única vez fuera de los IF...THEN

      -Es mejor utilizar el código de nombre de la hoja que el nombre de la hoja (Hoja1 en vez de WorkSheets.Range("Hoja1"))

      SALUDOS.

      Eliminar
  31. BUENOS DIAS AMIGO, SALUDOS.
    DISCULPA LA MOLESTIA, MEPARECIERON MUY INTERESANTES TUS APORTES Y QUERIA SABER SI ME PODIAS AYUDAR UN POCO EN UN PROBLEMITA YA QUE CONOZCO POCO EL LENGUAJE.
    BASICAMENTE EN MI TRABAJO USAN UNAS PLANILLAS EN EXCEL DONDE CALCULAN LA ENTRADA Y SALIDA DE LOS PRODUCTOS (EXISTEN VARIOS TIPOS) ELLA MUESTRA EL TOTAL POR CADA PRODUCTO Y EL TOTAL DETODA LA MERCANCIA. ESA HOJA DE CALCULO SE LLEVA ACABO POR MES Y EN EL MESPOR DIAS. EJEMPLO LA HOJA DE ABRIL, DENTRO DE ELLA ESTAN DIA 1, DIA 2, DIA 3, (Y TODOS LOS DIAS DEL MES EN DIFERENTES PESTAÑAS).
    DE VEZ EN CUANDO CAMBIAN ALGUNAS COSAS EN LOS PRODUCTOS POR LO TANTO ES NECESARIO CAMBIAR FORMULAS EN LAS CASILLAS, EJEMPLOL: ='dia 01'!B25+'Dia 02'!E8-'Dia 02'!L8 EN ESA CASILLA ME TRABAJA CON DATOS DEL DIA 1 ( QUE ES EL DIA ANTERIOR) Y DEL DIA 2 (DIA ACTUAL) ENTONCES EL DIA 3 DEBE SER BASICAMENTE ESTO : ='dia 02'!B25+'Dia 03'!E8-'Dia 03'!L8.. MI INTENCION ES GRABAR UNA MACRO Y EDITARLA DE MANERA QUE SE CORRAN LOS DIAS EN TODAS LAS CASILLAS QUE SE EDITEN. NO SE SI HAY UNA MANERA TE LO AGRADECERIA.

    ResponderEliminar
    Respuestas
    1. Lo que comentas se debe hacer mediante tablas dinamicas y de esa forma no es necesario cambiar fórmulas ni macros.
      Puedes acudir a los artículos de este blog relativos a las tablas dinámicas o bien al curso de tablas dinámicas que puedes encontrar en el enlace azul de cursos de témpora (arriba, en la parte derecha del blog).
      Saludos.

      Eliminar
  32. cordial saludo
    tengo una macro la cual necesito ejecutar a diario pero en diferente fila ; es decir al ejecutar la macro que esta en el dia 11 se alistan los datos para el día 12 . En este momento solo consigo que se ejecute la macro en los celdas que seleccione. y necesito que cada ves que yo ejecute mi macro se corra al día siguiente después de que llene mis datos correspondientes así sucesivamente .
    las celdas que no tienen datos permanecen ocultas .

    que codigo o que función puedo utilizar para que mi macro se ejecute y avance al dia siguiente .

    espero ser claro para que por favor me ayuden con esto de antemano muchas gracias .

    ResponderEliminar
  33. Buenas tardes...

    Tengo el siguiente código que me funciona correctamente:

    Private Sub Worksheet_Change(ByVal Target As Range)

    If Target = Range("A2") Then
    Range("B2").Value = ""
    End If

    End Sub

    El problema es que intento modificarlo para que haga lo mismo en otras celdas (en distintas filas) me funciona, pero se ralla y al final dándole a ESC me salta un error: "la ejecución del código se ha detenido".

    Alguien sabría como hacerlo??

    Muchas gracias.

    Un saludo.

    ResponderEliminar
  34. Perdón..... no había visto un comentario que era como el mio. Ya lo tengo solucionado, muchas gracias.

    ResponderEliminar
  35. Buenas tardes.
    Estoy realizan una planilla simple y necesito que los cuando la celda A4 esté vacía también se vacíe la celda E4; la A5 vacíe la E5.......así hasta las celda A12-E12. gracias.

    ResponderEliminar
    Respuestas
    1. Este sería el código:

      Private Sub Worksheet_Change(ByVal Target As Range)
      If Not Application.Intersect(Target, Range("A4:A12")) Is Nothing Then
      If Target = "" Then Target.Offset(0, 4).ClearContents
      End If
      End Sub

      Eliminar
    2. Muchas gracias. Apliqué lo indicado y quedó genial. Muy amable.

      Eliminar
  36. Sus explicaciones son las mejores que he leído en la red!, de verdad muchas gracias por tomarse el tiempo de hacer este tipo de blogs y de responder a las preguntas de los demás, no saben cuánto me han ayudado!.

    ResponderEliminar
  37. Buenas tardes,
    tengo un formulario con un botón que al darle click el ingresa los datos del formulario en las celdas de una misma fila. ahora lo que no sabría hacer es que luego de esa operación, la próxima ingrese los datos en la fila siguiente, como si fuera un enter. gracias

    ResponderEliminar
  38. Hola tengo una pregunta con respecto a este tema

    Necesito que al modificar cualquier celda en el rango B8 al T14, automaticamente me borre las siguientes: "B8:E10,F8:H10,I8:K8,L8:N8,O8:Q8,R8:T8,I10:T10,D12:F12,B14:E14,F14,G14:T14,B16:F16,G16:M16,N16:T16", gracias

    ResponderEliminar
  39. Hola, tu ejemplo me ha servido muy bien siempre que hago el cambio por teclado, pero tengo la necesidad de que se ejecute una subrutina siempre que una deternminada Celda2 con referencia a una Celda1 cambie (o se "recalcule"), intento probar con el evento "Private Sub Worksheet_Change()" pero no me detecta nunca el cambio, además de que necesito que detecte el cambio de UNA sola celda por separado y no de toda la hoja (puesto necesito hacer diferentes subturinas dependiendo de la celda que cambie. Mi pregunta es ¿Es posible?

    Ya le mencionaste a alguien que usara el evento "Private Sub Worksheet_Change(ByVal Target As Range)"o bien "Worksheet_Change(ByVal Target As Range)" en la hoja y celda principal (Celda1) donde alguien tendría que introducir el dato manual, pero en mi caso mi valor de Celda1 está referenciado al resultado que me lanza un programa (conclusión nadie teclea el valor en ningun momento).

    Agradezco tu tiempo.

    ResponderEliminar
    Respuestas
    1. El evento Change solo entra en funcionamiento cuando el valor es actualizado mediante teclado,mediante un link externo a otro archivo o mediante VBA, pero NO cuando se actualiza con un recálculo de celda.

      Por lo tanto, debes utilizar el evento "Calculate". Es decir, en este caso, Excel considera que la celda "se calcula", pero no considera que "cambia"...

      Cosas de Excel...

      Eliminar
    2. hola Suu Aloine, lograste solucionar lo que quieres hacer? yo quiero hacer algo como lo tuyo pero no me funciona tampoco.

      Eliminar
    3. Hola, AYUDAAAAAAAAA!!!
      Tengo un caso parecido a Suu Aloine. Necesito actualizar una macro, pero dependo del valor de una celda que viene de un PLC y los valores que me trae es 0 y 1. El problema es que la celda se me llena o cambia automaticamente, en ningun momento le doy enter.
      Necesito cuando la celda este en 1 me ejecute la macro.

      Necesito su ayuda, ya la cabeza no me da y no se como hacerlo porque los enventos Worksheet_Change, Worksheet_SelectionChange y el mismo Calculate no me sirve xq funciona solo si doy enter. o no se si lo estoy haciendo mal.

      Eliminar
  40. Hola. Si tienes muchas celdas con formulas y utilizas el Worksheet_Calculate()..... ¿cómo puedes determinar la celda que ha cambiado de valor?
    Muchas gracias

    ResponderEliminar
    Respuestas
    1. Si se utiliza WorkSheet_Calculate, este detecta el recálculo, no una celda que cambia.
      Para detectar celdas que cambian, este artículo tiene la solución:

      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

      Eliminar
  41. Hola, tengo el siguiente código que funciona bien si escribo un valor negativo, pero lo necesito cuando cambia por fórmula.
    He probado con WorkSheet_Calculate y un montón de cosas mas, pero no hay manera.

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("H3:H35,N3:N35,T3:T35")) Is Nothing Then
    Exit Sub
    End If
    If Target < 0 Then
    Target.Interior.Color = vbRed
    MsgBox "Verificar valores negativos." & vbCrLf & "Corregir fecha en la hoja -Parciales-", , "Atención, números negativos"
    End If
    End Sub

    Muchas gracias

    ResponderEliminar
    Respuestas
    1. Como ya he contestado en otras ocasiones, el evento change no responde a cambios mediante recálculo.
      Si el cambio no se produce por teclado... se debe producir porque ocurrirá algo, no? En algún lugar de Excel ocurrirá algo para que se produzca el cambio en el resultado de la fórmula.
      Hay que buscar "eso que ocurre" que motiva el cambio en la fórmula y poner una macro para que se dispare cuando ocurre "eso" (ya sea un cambio por teclado en otra hoja...o una macro que genera el cambio, etc.)
      Saludos.

      Eliminar