martes, 1 de marzo de 2016

VBA: Lanzar macro cuando cambia el valor de una fórmula.

En un comentario de este post un lector preguntaba:
...Tengo 4 hojas, las tres primeras hojas en su celda A1 toman los valores de la cuarta hoja, de A1, A2 y A3, respectivamente, con la siguiente expresión:
=SI(Hoja4!A1="","",Hoja4!A1)
=SI(Hoja4!A2="","",Hoja4!A2)
=SI(Hoja4!A3="","",Hoja4!A3)

El inconveniente que tengo es que no cambia el color de la etiqueta a menos que lo haga de forma manual, sin estar formulado...


La idea es conseguir ejecutar una acción, mediante macros, cuando cambia el valor de una celda que contiene una fórmula.
La clave para resolver este asunto es emplear una variable general donde cargar el valor de la celda, para luego controlarla.
La carga la podemos realizar al entrar o activar la hoja de trabajo en cuestión (por ejemplo)... una vez cargada, la seguiremos controlando dentro del evento _Change (o _Calculate) de los ejemplos siguientes


Insertamos el siguiente código dentro de la ventana de código de la Hoja:

Option Explicit
Dim Monitor
 
Private Sub Worksheet_Activate()
'Cargamos la variable para controlar el cambio de valor en la celda A1
Monitor = Range("A1").Value
End Sub
 
Private Sub Worksheet_Change(ByVal Target As Range)

'Prevenimos un posible bucle debido a cambios en la hoja
Application.EnableEvents = False
'Comparamos la celda A1 controlada con el valor anterior...
If Range("A1").Value <> Monitor Then
    'Lanzamos la acción deseada...
    MsgBox "La fórmula en A1 ha cambiado.."
    
    'cargamos de nuevo la variable de control de cambio
    Monitor = Range("A1").Value
End If
 
'Reseteamos los eventos
Application.EnableEvents = True
End Sub



Otra opción sería emplear el evento _Calculate (muy similar al anterior...):

Public ValorPrevio As Variant

Private Sub Worksheet_Activate()
'Cargamos la variable para controlar el cambio de valor en la celda A1
ValorPrevio = Hoja2.Range("A1").Value
End Sub
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Sub Worksheet_Calculate()
Set etiqueta = ThisWorkbook.Sheets(2).Tab
    
'Prevenimos un posible bucle debido a cambios en la hoja
Application.EnableEvents = False

'verificamos si el valor de A1 ha cambiado respecto al valor anterior
If Hoja2.Range("A1").Value <> ValorPrevio Then
    'lanzamos mensaje
    MsgBox "Valor celda A1 en Hoja2 ha cambiado el valor de su fórmula"
    'ejecutamos la acción deseada..
    etiqueta.ColorIndex = 3
    'cargamos de nuevo la variable de control de cambio
    ValorPrevio = Hoja2.Range("A1").Value
End If

'Reseteamos los eventos
Application.EnableEvents = True
End Sub

3 comentarios:

  1. Excelente ejecución de una macro

    ResponderEliminar
  2. si quisiera que fuera en un rango X de celdas que paso debo dar?

    ResponderEliminar
    Respuestas
    1. podrías hacer un recorrido guardando en una matriz (Array) los diferentes valores previo para luego ir comparando con el nuevo valor actualizado
      Slds

      Eliminar