Eventos Page 1 of 4

EVENTOS
Página inicial

En esta página irán apareciendo ejemplos del uso de eventos en Excel, aunque lo normal es que los eventos se usen en situaciones muy específicas,
lo que hace difícil que el código de un evento que sirve a alguien pueda interesar a otras personas. Sin embargo, sí puede servir como ejemplo para
situaciones similares y para ver las posibilidades que ofrecen los eventos.

EVENTOS DE LIBRO

Uso del evento Workbook_BeforePrint para seleccionar el número de copias a imprimir de una hoja determinada y poner el número de copia en
una celda

Uso del evento Workbook_Open para guardar en un fichero el nombre del usuario que abrió el libro y la hora de acceso

EVENTOS DE HOJA

Uso del evento Worksheet_Change para dejar constancia de la hora en que se produce un cambio en un rango

Uso del evento Worksheet_Change para copiar datos de un libro cerrado a la hoja activa

Uso del evento Worksheet_Change para actualizar una tabla dinámica cuando se modifica una celda

Uso del evento Worksheet_Change para impedir que se pueda modificar una celda si afecta al resultado de otra

EVENTOS DEL OBJETO QUERYTABLE

Ejemplo de uso de los eventos BeforeRefresh y AfterRefresh para actualizar una consulta web que se encuentra en una hoja protegida.

EVENTOS DE GRÁFICOS

Ejemplo de uso del evento Calculate en un gráfico incrustado y en un gráfico situado en su propia hoja de gráficos.

EVENTOS DEL OBJETO CALENDAR

Ejemplo de uso de los eventos Click y GotFocus de un objeto Calendar incrustado en una hoja

Uso del evento Workbook_BeforePrint para seleccionar el número de copias a imprimir de una hoja determinada y poner el
número de copia en una celda
El siguiente código, situado en el módulo del libro (ThisWorkbook) se encargaría de preguntar el número de copias a imprimir de Hoja1 y pondría en la
celda D1, empezando a numerar a partir del valor de dicha celda.

Private Sub Workbook_BeforePrint(Cancel As Boolean)

If [Link] <> "Hoja1" Then Exit Sub

Dim intNúmCopias As Integer, n As Integer

intNúmCopias = [Link](prompt:="Número de copias a imprimir:", Type:=1)

For n = 1 To intNúmCopias
[Link] copies:=1
[Link]("D1").Value = [Link]("D1").Value + 1
Next n
End Sub

Uso del evento Workbook_Open para guardar en un fichero .log el nombre del usuario que abrió el libro y la hora de acceso
Normalmente esto se usa en libros situados en una unidad de red, si se necesita conocer qué usuario accedió al libro y a qué hora se produjo el
acceso.
En el módulo del libro iría este código:

Private Sub Workbook_Open()

ActualizarRegistro
End Sub

Y en un módulo vacío iría lo siguiente:

Private Declare Function GetUserName Lib "[Link]" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

Sub ActualizarRegistro()
Dim strUserName As String, Cadena As String * 46
Dim intNúmArchivo As Integer, lngNúmRegistro As Long

strUserName = String(100, Chr$(0))

GetUserName strUserName, 100
strUserName = Left$(strUserName, InStr(strUserName, Chr$(0)) - 1)

intNúmArchivo = FreeFile(1)

[Link] 12/01/2012
Eventos Page 2 of 4

Cadena = Left(Left(strUserName, 25) & String(25, " "), 25) & Now() & Chr(13) & Chr(10)

Open "P:\[Link]" For Random Shared As intNúmArchivo Len = 46

lngNúmRegistro = (LOF(intNúmArchivo) / 46) + 1
Put intNúmArchivo, lngNúmRegistro, Cadena

Close intNúmArchivo
End Sub

(en este código, la unidad de red es P:, y el fichero [Link] se situaría en su directorio raíz)

El problema con este tipo de controles es que se pueden burlar si el usuario tiene establecida la seguridad de Excel en media o alta y decide no
autorizar la ejecución de macros, aunque por otra parte lo habitual es que estos libros tengan funcionalidades que sólo se pueden obtener ejecutando
código, por lo que no tendría demasiado sentido abrirlos sin autorizar su ejecución.
€

Uso del evento Worksheet_Change para dejar constancia de la hora en que se produce un cambio en un rango
El siguiente código, situado en el módulo de una hoja se encargaría de poner en el rango B2:B10 la hora en que se ha editado por última vez la celda
de la misma fila en la columna A:

Private Sub Worksheet_Change(ByVal Target As Range)

If [Link](Target, [Link]("A2:A10")) Is Nothing Then Exit Sub

[Link] = False
[Link](, 1) = Now
[Link] = True
End Sub
€

Uso del evento Worksheet_Change para copiar datos de un libro cerrado a la hoja activa
En este ejemplo se necesita que al escribir un nombre de libro en la columna A de una hoja se actualizen las columnas B,C,D y E con el contenido de
las mismas columnas de la Hoja1 del libro cuyo nombre se ha escrito.

Private Sub Worksheet_Change(ByVal Target As Range)

If [Link] <> 1 Then Exit Sub

Dim n As Byte

If Dir("C:\prueba\" & [Link] & ".xls") = "" Then Exit Sub

[Link] = False
For n = 2 To 5 'Número de columnas que se pondrán del libro seleccionado
[Link]([Link], n).FormulaR1C1 = "='C:\prueba\[" & [Link] & ".xls" & "]" & "Hoja1'!R" & [Link] & "C" & n
Next n
[Link] = True
End Sub

En este caso, los ficheros están situados en C:\Pruebas

Si el libro cuyo nombre se ha tecleado no existe, el código no hace nada.

Uso del evento Worksheet_Change para actualizar una tabla dinámica cuando se modifica una celda
Suponiendo que en Hoja2 hubiera una tabla dinámica cuyo origen de datos estuvieran en Hoja1, si se necesita que al modificar cualquier celda de dicho
origen de datos se actualice automáticamente la tabla dinámica se podría usar el siguiente código:

Private Sub Worksheet_Change(ByVal Target As Range)

€€€ With Worksheets("Hoja2").PivotTables(1).PivotCache 'Hoja donde se encuentre la tabla dinámica
€€€€€€€ If Not Intersect(Target, Range([Link](Replace(.SourceData, "F", "R"), xlR1C1, xlA1))) Is Nothing
Then .Refresh
€€€ End With
End Sub

Ejemplo de uso de los eventos BeforeRefresh y AfterRefresh para actualizar una consulta web que se encuentra en una hoja
protegida.
Para poder hacer uso de los eventos de los objetos QueryTable es necesario crear un objeto con eventos usando un módulo de clase.

En el ejemplo disponible aquí se puede ver cómo usar estos eventos, en el caso de una consulta web, para actualizarla en una hoja protegida.

El ejemplo no funcionará si se abre desde Internet Explorer. Para que funcione, hay que guardarlo en el disco duro (pero el código sí puede examinarse
abriendo el libro desde IE).

Ejemplo de uso del evento Calculate en un gráfico incrustado y en un gráfico situado en su propia hoja de gráficos.
Los eventos para los gráficos situados en hojas de gráfico están habilitados por defecto en Excel, pero los de los gráficos incrustados en una hoja de
cálculo no lo están, por lo que para poder usarlos es necesario crear un objeto con eventos usando un módulo de clase.

[Link] 12/01/2012
Eventos Page 3 of 4

En el ejemplo disponible aquí hay dos gráficos iguales, con la única diferencia de que uno se encuentra incrustado en Hoja1 y el otro en su propia hoja
de gráfico.

El ejercicio consiste en usar el evento Calculate de ambos gráficos para que cada una de sus barras cambie de color dependiendo de su valor, en
función de cuatro tramos que están definidos en el código VBA.

El ejemplo no funcionará bien si se abre desde Internet Explorer. Para que funcione, hay que guardarlo en el disco duro (pero el código sí puede
examinarse abriendo el libro desde IE).

Ejemplo de uso de los eventos Click y GotFocus de un objeto Calendar incrustado en una hoja

Si se necesita introducir y editar fechas, a veces puede convenir usar el objeto Calendar, el cual puede incrustarse en una hoja de cálculo desde
Insertar->Objeto.

Vamos a suponer que interesa que se muestre el calendario siempre que se edite una celda situada en la columna A de una hoja. El código siguiente
(que habría que situar en el módulo de la hoja haciendo click derecho sobre su etiqueta->Ver código), se encargaría del trabajo:

Private Sub Calendar1_Click()

€€€ ActiveCell = Calendar1
End Sub
€
Private Sub Calendar1_GotFocus()
€€€ If IsDate(ActiveCell) Then Calendar1 = ActiveCell
End Sub
€
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
€€€ With Calendar1
€€€€€€€ .Visible = [Link] = 1
€€€€€€€ If [Link] = 1 And IsDate(Format(ActiveCell, "yyyy-mm-dd")) Then Calendar1 = ActiveCell Else .Value = Int(Now())
€€€ End With
End Sub

Con el código anterior, el calendario aparecería siempre en la posición en que se hubiera situado manualmente. Si se necesitara que, por ejemplo, el
calendario apareciera justo a la derecha de la celda que se está editando, se podría usar:

Private Sub Calendar1_Click()

€€€ ActiveCell = Calendar1
End Sub

Private Sub Calendar1_GotFocus()

€€€ If IsDate(ActiveCell) Then Calendar1 = ActiveCell
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

€€€ If [Link] = 1 Then
€€€€€€€ With Calendar1
€€€€€€€€€€€ .Top = [Link]
€€€€€€€€€€€ .Left = [Link] + [Link](0, 1).Left
€€€€€€€€€€€ .Visible = True
€€€€€€€€€€€ If IsDate(Format(ActiveCell, "yyyy-mm-dd")) Then .Value = ActiveCell Else .Value = Int(Now())
€€€€€€€ End With
€€€ Else
€€€€€€€ [Link] = False
€€€ End If
End Sub
€

Si el control Calendar no estuviera disponible, en el siguiente enlace hay información (en inglés, me temo):

[Link]

Uso del evento Worksheet_Change para impedir que se pueda modificar una fórmula si afecta al resultado de otra
Si no se desea proteger una hoja pero sí evitar que se pueda modificar cualquier celda que afecte al resultado de una dada (en el ejemplo, A6), se
podría usar el siguiente código, que tendría que situarse en el módulo de la hoja:

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo Captura
If Not [Link]([$A$6], [Link]) Is Nothing Then
With Application
.EnableEvents = False
.Undo
.EnableEvents = True
MsgBox prompt:="La celda " & [Link] & " no se puede modificar porque afecta al resultado de la celda $A$6.", Buttons:=vbOKOnly + vbI
End With
End If
Exit Sub
Captura:
If [Link] = 1004 Then Exit Sub Else MsgBox [Link] & " - " & [Link]
End Sub

Si sólo se deseara impedir que se pudiesen modificar las celdas con precedencia directa sobre A6, habría que cambiar

[Link]

por

[Link]

[Link] 12/01/2012
Eventos Page 4 of 4

Página inicial
€

[Link] 12/01/2012