jueves, 24 de noviembre de 2016

VBA: Detectar si una Celda ha cambiado de valor

Una lectoar preguntaba por una manera de detectar si una celda ha cambiado de valor:
[...] me gustaría guardar el valor actual de la celda para luego compararlo con la misma celda, y resaltar si el valor cambió, son valores numéricos y me gustaría identificar si alguno cambió [...]

La idea según explica nuestra lectora es detectar cuándo se ha modificado o cambiado de valor una celda dentro de un rango definido (en nuestro ejemplo A1:B10).

Para ello emplearemos el evento de hoja Worksheet_Change que hará posible verificar la variación.
Por otro lado haremos uso del tipo de variable Static, que permite fijar el valor antes de los cambios.


El procedimiento es en sí simple (al menos en la idea).
Si guardo en memoria los valores antes del cambio, y tras un posible cambio compruebo celda a celda si el nuevo valor comparado con el anterior, sabremos si efectivamente se ha producido o no dicho cambio.


Si tenemos esta situación inicial en nuestro rango A1:B10:

VBA: Detectar si una Celda ha cambiado de valor



En la ventana de código de nuestra hoja de trabajo incluimos el siguiente evento:

'Al definir las variables antes del procedimiento,
'trabajamos con ellas como si fueran 'estáticas' (Static)
'i.e., mantienen el valor en memoria para comprobar si se ha producido un cambio...
Dim inicio As Long
Dim ValorAnterior() As Variant

Private Sub Worksheet_Change(ByVal Target As Range)
Dim fila As Long, col As Long
Dim contador As Long
'definimo el rango sobre el que aplicar el control
Dim RangoTrabajo As Range
Set RangoTrabajo = Range("A1:B10")

If inicio = 0 Then
    ValorAnterior = RangoTrabajo
    inicio = 1
    Exit Sub
End If

'si no trabajamos sobre el rango descrito salimos del procedimiento
If Intersect(Target, RangoTrabajo) Is Nothing Then Exit Sub

'iniciamos contador
contador = 1
'recorremos el rango de trabajo
'comprobando si los valores anteriores difieren de los actuales
'después del cambio
'recorremos las filas del rango A1:B10
For fila = LBound(ValorAnterior, 1) To UBound(ValorAnterior, 1)
    'recorremos las columnas del rango A1:B10
    For col = LBound(ValorAnterior, 2) To UBound(ValorAnterior, 2)
        valor1 = RangoTrabajo(contador)         'acual valor de la celda(después cambio)
        valor2 = ValorAnterior(fila, col)       'anterior valor de la celda (antes cambio)
        'si difieren entonces lanzamos mensaje
        If valor1 <> valor2 Then
            MsgBox "La Celda " & RangoTrabajo(contador).Address & " ha cambiado del valor " & valor2 & " al nuevo valor " & valor1
        End If
        contador = contador + 1
    Next col
Next fila

'cargamos la matriz con un nuevo valor
ValorAnterior = RangoTrabajo
End Sub



Si tras insertar nuestro código y realizar algún cambio comprobamos el efecto.
Por ejemplo cambiamos la celda B3, de un valor 100 a introducir el valor 1313. El evento Worksheet_Change actuará y lanzará el siguiente mensaje:

VBA: Detectar si una Celda ha cambiado de valor



Cada vez que entramos en el rango A1:B10 se produce una carga de datos en memoria, para poder así comparar con la siguiente vez que accedamos a dicho rango...

21 comentarios:

  1. pregunta puede detectarse cambios de datos en una celda de una hoja diferente a la activa ... explico tengo una hoja que mi interes es que al llegar a 5 la celda arroje un aviso pero la carga de estos datos los trae de otra hoja

    G5 = HOJA2 H9 ... SI G5 LLEGA A 5 ENTONCES LA ALERTA NO SE SI ME EXPLICO

    ResponderEliminar
    Respuestas
    1. Hola,
      si sería posible indicando el rango completo (nombre hoja y rango celdas)...

      Saludos

      Eliminar
  2. una consulta y si solo quiero saber si ha cambiado UNA celda??
    gracias

    ResponderEliminar
    Respuestas
    1. Hola Gorki Llerena,
      cómo estás?, un placer saludarte igualmente.

      El truco es emplear el método intersect (ver línea 21) sobre qué celda quieres trabajar o controlar.

      Un cordial saludo

      Eliminar
  3. Gracias. Como haría en el caso detectar el cambio en un rango nombrado anteriormente?

    ResponderEliminar
    Respuestas
    1. Hola David,
      en la fila 12 del código
      Set RangoTrabajo = Range("A1:B10")


      emplearías el nombre definido:
      Set RangoTrabajo = Range("NombreDefinido")

      Espero haberte entendido

      Saludos

      Eliminar
  4. Si en una celda había un dato y alguien lo modifico hay forma de saber fecha y hora de modificacion y que información tenía anteriormente la celda

    ResponderEliminar
    Respuestas
    1. Hola jluiscarrillo,
      qué tal? Un placer saludarte igualmente.
      Existe una manera si conviertes tu libro en Libro compartido, desde donde podrás gestionar el Control de cambios ...
      No es tan clara como pueda ser en Word pero quizá te sirva.

      Ojo con la versión de Excel en qué trabajes!!, en Excel 2016 actúa de manera diferente

      Slds

      Eliminar
  5. Hola,
    todo lo que veo sirve, si el usuario introduce un vcambio manualmente.
    Pero cómo se haría, si lo que hay en la celda es una funcion, que varia segun otros datos. Gracias.

    ResponderEliminar
  6. Buenas Ismael

    estoy trabajando en un generador de tarifas en mi.trabajo y me he encallsdo en lo siguiente: Hay 7 puertos con una característica comun: Puertos Base. si en una celda pongo Puertos Base quiero que en las 7 celdas que le corresponde a cada puerto se pongan automaticamente por su nombre y si seesceibes otra cosa, no realizar nningu cambio. No sé si me he explicado pero lo q quiero es evitar poner manualmente algo q se suele cotizar conjuntamente y asi ahorrar tiempo. Gracias por tu tiempo

    ResponderEliminar
    Respuestas
    1. Hola, habría que ver el fichero...
      y entender la distribución de celdas, pero para lanzar un relleno de 7 celdas, una sola vez, tendrás que incluir una condición que controle que la celda cambiada está ya completada (como se explica en el post más arriba).
      Esto te supondrá tener activo siempre el evento change, lo que puede repercutir en tu uso normal del fichero...

      Quizá asociado a un botón para que complete esas celda a discreción

      espero haberte entendido
      Saludos

      Eliminar
  7. ¿Cómo se autoajustaría el ancho de cualquier columna que sufra cambios en los valores de sus celdas que hayan sido pegados? Gracias y saludos.

    ResponderEliminar
    Respuestas
    1. Hola
      pues igual que el código descrito, pero en lugar del MsgBox de la línea 36, incluir:
      RangoTrabajo(contador).EntireColumn.AutoFit

      Saludos

      Eliminar
  8. Gracias, pero tengo dificultades. Cuando pego valores la primera vez en una columna cualquiera, la macro no funciona; si pego valores en otra columna, por segunda vez, entonces funciona. Y si continúo pegando en más columnas, la macro sigue funcionando. ¿Por qué no funciona desde la primera vez que pego valores?

    Otra pregunta más: ¿cómo definir un rango para que la macro funcione en cualquier libro activo?

    Gracias y saludos cordiales.

    ResponderEliminar
    Respuestas
    1. Hola,
      la cuestión es que primero debe grabar, reconocer el dato que hubiera... y para eso necesita ejecutarse.... de ahí que no detecte la primera vez que escribes (o pegas) y sí las sucesivas.

      Esta macro está sujeta a un evento de hoja, y a lo que ocurre por tanto en esa hoja de un libro en concreto... es lo malo de los eventos, están sujetos a una hoja particular (o en su caso a un libro concreto)

      Saludos

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

    ResponderEliminar
  10. Gracias por tu ayuda, saludos.

    ResponderEliminar
  11. Otra pregunta: ¿cómo dar click al botón derecho del mouse con macro?
    Gracias.

    ResponderEliminar
    Respuestas
    1. Hola,
      si presionas las teclas mayusc + F10 sacas el menú contextual... asi pues podrías con el método .SendKeys replicar el click derecho
      Saludos

      Eliminar

Nota: solo los miembros de este blog pueden publicar comentarios.