excel - visual - ¿Cómo puedo ejecutar un código VBA cada vez que una celda obtiene un valor cambiado por una fórmula?

excel vba ejecutar macro al cambiar valor de celda

Question

Me gustaría saber cómo puedo ejecutar un código VBA cada vez que una celda obtiene un valor cambiado por una fórmula? He logrado ejecutar un código cuando el usuario cambia el valor de una celda, pero no funciona w

Answer 1

Si tengo una fórmula en la celda A1 (por ejemplo, = B1 * C1) y deseo ejecutar algún código VBA cada vez que A1 cambie debido a las actualizaciones de la celda B1 o C1, entonces puedo usar lo siguiente:

Private Sub Worksheet_Calculate()
Dim target As Range
Set target = Range("A1")
If Not Intersect(target, Range("A1")) Is Nothing Then
//Run my VBA code
End If
End Sub

Actualizar

Hasta donde yo sé, el problema con la Worksheet_Calculate es que se activa para todas las celdas que contienen fórmulas en la hoja de cálculo y no se puede determinar qué celda se ha recalculado (es decir, Worksheet_Calculate cálculo_calcular no proporciona un objeto de Target )

Para evitar esto, si tiene un montón de fórmulas en la columna A y desea identificar cuál se ha actualizado y agregar un comentario a esa celda específica, entonces creo que el siguiente código lo logrará:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim updatedCell As Range
Set updatedCell = Range(Target.Dependents.Address)
If Not Intersect(updatedCell, Range("A:A")) Is Nothing Then
updatedCell.AddComment ("My Comments")
End If
End Sub

Para explicar, para una fórmula para actualizar, una de las celdas de entrada en esa fórmula debe cambiar; por ejemplo, si la fórmula en A1 es =B1 * C1 entonces B1 o C1 deben cambiar para actualizar A1.

Podemos usar el evento Worksheet_Change para detectar un cambio de celda en la hoja / s y luego usar la funcionalidad de auditoría de Excel para rastrear los dependientes, por ejemplo, la celda A1 depende tanto de B1 como de C1 y, en este caso, el código Target.Dependents.Address sería devuelve $A$1 por cualquier cambio a B1 o C1 .

Dado esto, todo lo que tenemos que hacer ahora es verificar si la dirección dependiente está en la columna A (usando Intersect ). Si está en la Columna A, podemos agregar comentarios a la celda apropiada.

Tenga en cuenta que esto solo funciona para agregar comentarios una sola vez en una celda. Si desea continuar sobrescribiendo comentarios en la misma celda, primero deberá modificar el código para verificar la existencia de comentarios y luego eliminarlos según sea necesario.

Answer 2

Aquí hay otra forma de usar clases. La clase puede almacenar el valor inicial de la celda y la dirección de la celda. En el evento calcular, comparará el valor actual de la dirección con el valor inicial almacenado. El siguiente ejemplo se hace para escuchar solo una celda ("A2"), pero puede iniciar la escucha de más celdas en el módulo o cambiar la clase para trabajar con rangos más amplios.

Módulo de clase llamado "Class1":

Public WithEvents MySheet As Worksheet
Public MyRange As Range
Public MyIniVal As Variant
Public Sub Initialize_MySheet(Sh As Worksheet, Ran As Range)
Set MySheet = Sh
Set MyRange = Ran
MyIniVal = Ran.Value
End Sub
Private Sub MySheet_Calculate()
If MyRange.Value <> MyIniVal Then
Debug.Print MyRange.Address & " was changed from " & MyIniVal & " to " & MyRange.Value
StartClass
End If
End Sub

Inicializa la clase en el módulo normall.

Dim MyClass As Class1
Sub StartClass()
Set MyClass = Nothing
Set MyClass = New Class1
MyClass.Initialize_MySheet ActiveSheet, Range("A2")
End Sub

Answer 3

Aquí está mi código:

Sé que se ve terrible, pero funciona! Por supuesto, hay soluciones que son mucho mejores.

Descripción del código:

Cuando se abre el Libro de trabajo, el valor de las celdas B15 hasta N15 se guarda en la variable PrevValb till PrevValn. Si se produce un evento Worksheet_Calculate (), los valores anteriores se comparan con los valores reales de las celdas. Si hay un cambio del valor, la celda se marca con color rojo. Este código podría escribirse con funciones, por lo que es mucho más corto y fácil de leer. Hay un botón de restablecimiento del color (Seenchanges), que restablece el color al color anterior.

Libro de trabajo:

Private Sub Workbook_Open()
PrevValb = Tabelle1.Range("B15").Value
PrevValc = Tabelle1.Range("C15").Value
PrevVald = Tabelle1.Range("D15").Value
PrevVale = Tabelle1.Range("E15").Value
PrevValf = Tabelle1.Range("F15").Value
PrevValg = Tabelle1.Range("G15").Value
PrevValh = Tabelle1.Range("H15").Value
PrevVali = Tabelle1.Range("I15").Value
PrevValj = Tabelle1.Range("J15").Value
PrevValk = Tabelle1.Range("K15").Value
PrevVall = Tabelle1.Range("L15").Value
PrevValm = Tabelle1.Range("M15").Value
PrevValn = Tabelle1.Range("N15").Value
End Sub

Modul:

Sub Seenchanges_Klicken()
Range("B15:N15").Interior.Color = RGB(252, 213, 180)
End Sub

Hoja1:

Private Sub Worksheet_Calculate()
If Range("B15").Value <> PrevValb Then
Range("B15").Interior.Color = RGB(255, 0, 0)
PrevValb = Range("B15").Value
End If
If Range("C15").Value <> PrevValc Then
Range("C15").Interior.Color = RGB(255, 0, 0)
PrevValc = Range("C15").Value
End If
If Range("D15").Value <> PrevVald Then
Range("D15").Interior.Color = RGB(255, 0, 0)
PrevVald = Range("D15").Value
End If
If Range("E15").Value <> PrevVale Then
Range("E15").Interior.Color = RGB(255, 0, 0)
PrevVale = Range("E15").Value
End If
If Range("F15").Value <> PrevValf Then
Range("F15").Interior.Color = RGB(255, 0, 0)
PrevValf = Range("F15").Value
End If
If Range("G15").Value <> PrevValg Then
Range("G15").Interior.Color = RGB(255, 0, 0)
PrevValg = Range("G15").Value
End If
If Range("H15").Value <> PrevValh Then
Range("H15").Interior.Color = RGB(255, 0, 0)
PrevValh = Range("H15").Value
End If
If Range("I15").Value <> PrevVali Then
Range("I15").Interior.Color = RGB(255, 0, 0)
PrevVali = Range("I15").Value
End If
If Range("J15").Value <> PrevValj Then
Range("J15").Interior.Color = RGB(255, 0, 0)
PrevValj = Range("J15").Value
End If
If Range("K15").Value <> PrevValk Then
Range("K15").Interior.Color = RGB(255, 0, 0)
PrevValk = Range("K15").Value
End If
If Range("L15").Value <> PrevVall Then
Range("L15").Interior.Color = RGB(255, 0, 0)
PrevVall = Range("L15").Value
End If
If Range("M15").Value <> PrevValm Then
Range("M15").Interior.Color = RGB(255, 0, 0)
PrevValm = Range("M15").Value
End If
If Range("N15").Value <> PrevValn Then
Range("N15").Interior.Color = RGB(255, 0, 0)
PrevValn = Range("N15").Value
End If
End Sub

Answer 4

El código que usaste no funciona porque el cambio de celda no es la celda con la fórmula, sino la venta ... cambiada :)

Esto es lo que debe agregar al módulo de la hoja de trabajo:

(Sin fecha: la línea "Establecer rDependents = Target.Dependents" provocará un error si no hay dependientes. Esta actualización se ocupa de esto).

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rDependents As Range
On Error Resume Next
Set rDependents = Target.Dependents
If Err.Number > 0 Then
Exit Sub
End If
'' If the cell with the formula is "F160", for example...
If Not Application.Intersect(rDependents, Range("F160")) Is Nothing Then
Call abc
End If
End Sub
Private Sub abc()
MsgBox """abc()"" is running now"
End Sub

Puede expandir esto si hay muchas celdas dependientes al ordenar una matriz de direcciones de celda en cuestión. Luego, debería probar cada dirección en la matriz (puede usar cualquier estructura de bucle para esto) y ejecutar una correlación de subrutina para la celda modificada (use SELECT CASE ...) para esto.