Eventos de Excel VBA: una guía fácil (y completa)

Cuando crea o graba una macro en Excel, debe ejecutar la macro para ejecutar los pasos en el código.

Algunas formas de ejecutar una macro incluyen usar el cuadro de diálogo de macro, asignar la macro a un botón, usar un atajo, etc.

Además de estas ejecuciones de macros iniciadas por el usuario, también puede utilizar eventos de VBA para ejecutar la macro.

Eventos de Excel VBA - Introducción

Permítanme explicar primero qué es un evento en VBA.

Un evento es una acción que puede desencadenar la ejecución de la macro especificada.

Por ejemplo, cuando abre un nuevo libro de trabajo, es un evento. Cuando inserta una nueva hoja de trabajo, es un evento. Cuando haces doble clic en una celda, es un evento.

Hay muchos eventos de este tipo en VBA y puede crear códigos para estos eventos. Esto significa que tan pronto como ocurra un evento, y si ha especificado un código para ese evento, ese código se ejecutará instantáneamente.

Excel hace esto automáticamente tan pronto como se da cuenta de que se ha producido un evento. Por lo tanto, solo necesita escribir el código y colocarlo en la subrutina de eventos correcta (esto se explica más adelante en este artículo).

Por ejemplo, si inserta una nueva hoja de trabajo y desea que tenga un prefijo de año, puede escribir el código.

Ahora, cada vez que alguien inserta una nueva hoja de trabajo, este código se ejecutará automáticamente y agregará el prefijo del año al nombre de la hoja de trabajo.

Otro ejemplo podría ser que desea cambiar el color de la celda cuando alguien hace doble clic en ella. Puede utilizar el evento de doble clic para esto.

Del mismo modo, puede crear códigos VBA para muchos de estos eventos (como veremos más adelante en este artículo).

A continuación se muestra una breve imagen que muestra el evento de doble clic en acción. Tan pronto como haga doble clic en la celda A1. Excel abre instantáneamente un cuadro de mensaje que muestra la dirección de la celda.

Hacer doble clic es un evento, y mostrar el cuadro de mensaje es lo que he especificado en el código cada vez que tiene lugar el evento de doble clic.

Si bien el ejemplo anterior es un evento inútil, espero que le ayude a comprender qué son realmente los eventos.

Diferentes tipos de eventos de Excel VBA

Hay diferentes objetos en Excel, como el propio Excel (al que a menudo nos referimos como la aplicación), libros de trabajo, hojas de trabajo, gráficos, etc.

Cada uno de estos objetos puede tener varios eventos asociados. Por ejemplo:

• Si crea un libro de trabajo nuevo, es un evento de nivel de aplicación.
• Si agrega una nueva hoja de trabajo, es un evento de nivel de libro de trabajo.
• Si cambia el valor en una celda de una hoja, es un evento de nivel de hoja de trabajo.

A continuación se muestran los diferentes tipos de eventos que existen en Excel:

1. Eventos de nivel de hoja de trabajo: Estos son los tipos de eventos que se activarían en función de las acciones realizadas en la hoja de trabajo. Ejemplos de estos eventos incluyen cambiar una celda en la hoja de trabajo, cambiar la selección, hacer doble clic en una celda, hacer clic con el botón derecho en una celda, etc.
2. Eventos de nivel de libro de trabajo: Estos eventos se activarían en función de las acciones en el nivel del libro de trabajo. Los ejemplos de estos eventos incluyen agregar una nueva hoja de trabajo, guardar el libro de trabajo, abrir el libro de trabajo, imprimir una parte o el libro completo, etc.
3. Eventos de nivel de aplicación: Estos son los eventos que ocurren en la aplicación Excel. Un ejemplo de estos incluiría cerrar cualquiera de los libros abiertos o abrir un nuevo libro.
4. Eventos de nivel de formulario de usuario: Estos eventos se activarían en función de las acciones en el "UserForm". Ejemplos de estos incluyen inicializar un UserForm o hacer clic en un botón del UserForm.
5. Eventos de gráficos: Estos son eventos relacionados con la hoja del gráfico. Una hoja de gráfico es diferente a una hoja de trabajo (que es donde la mayoría de nosotros estamos acostumbrados a trabajar en Excel). El propósito de las hojas de un gráfico es sostener un gráfico. Ejemplos de tales eventos incluirían cambiar la serie del gráfico o cambiar el tamaño del gráfico.
6. Eventos OnTime y OnKey: Estos son dos eventos que no encajan en ninguna de las categorías anteriores. Así que los he enumerado por separado. El evento "OnTime" le permite ejecutar un código en un momento específico o después de que haya transcurrido un tiempo específico. El evento "OnKey" le permite ejecutar un código cuando se usa una tecla específica (o una combinación de teclas).

Dónde colocar el código relacionado con el evento

En la sección anterior, cubrí los diferentes tipos de eventos.

Según el tipo de evento, debe colocar el código en el objeto relevante.

Por ejemplo, si se trata de un evento relacionado con la hoja de trabajo, debería ir en la ventana de código del objeto de la hoja de trabajo. Si está relacionado con el libro de trabajo, debería ir en la ventana de código para un objeto de libro de trabajo.

En VBA, diferentes objetos, como hojas de trabajo, libros de trabajo, hojas de gráficos, formularios de usuario, etc., tienen sus propias ventanas de código. Debe colocar el código del evento en la ventana de código del objeto correspondiente. Por ejemplo, si se trata de un evento de nivel de libro de trabajo, debe tener el código de evento en la ventana de código del libro de trabajo.

Las siguientes secciones cubren los lugares donde puede colocar el código del evento:

En la ventana de código de la hoja de trabajo

Cuando abre VB Editor (usando el método abreviado de teclado ALT + F11), notará el objeto de hojas de trabajo en el Explorador de proyectos. Para cada hoja de trabajo del libro, verá un objeto.

Cuando hace doble clic en el objeto de la hoja de trabajo en el que desea colocar el código, se abrirá la ventana de código para esa hoja de trabajo.

Si bien puede comenzar a escribir el código desde cero, es mucho mejor seleccionar el evento de una lista de opciones y dejar que VBA inserte automáticamente el código relevante para el evento seleccionado.

Para hacer esto, primero debe seleccionar la hoja de trabajo del menú desplegable en la parte superior izquierda de la ventana del código.

Después de seleccionar la hoja de trabajo del menú desplegable, obtendrá una lista de todos los eventos relacionados con la hoja de trabajo. Puede seleccionar el que desea usar en el menú desplegable en la parte superior derecha de la ventana del código.

Tan pronto como seleccione el evento, ingresará automáticamente la primera y la última línea del código para el evento seleccionado. Ahora puede agregar su código entre las dos líneas.

Nota: Tan pronto como seleccione Hoja de trabajo en el menú desplegable, notará que aparecen dos líneas de código en la ventana de código. Una vez que hayas seleccionado el evento para el que quieres el código, puedes borrar las líneas que aparecían por defecto.

Tenga en cuenta que cada hoja de trabajo tiene una ventana de código propia. Cuando pones el código para Sheet1, solo funcionará si el evento ocurre en Sheet1.

En la ventana de código de ThisWorkbook

Al igual que con las hojas de trabajo, si tiene un código de evento de nivel de libro de trabajo, puede colocarlo en la ventana de código de ThisWorkbook.

Al hacer doble clic en ThisWorkbook, se abrirá la ventana de código.

Debe seleccionar Libro de trabajo en el menú desplegable en la parte superior izquierda de la ventana del código.

Después de seleccionar Libro de trabajo en el menú desplegable, obtendrá una lista de todos los eventos relacionados con el Libro de trabajo. Puede seleccionar el que desea usar en el menú desplegable en la parte superior derecha de la ventana del código.

Tan pronto como seleccione el evento, ingresará automáticamente la primera y la última línea del código para el evento seleccionado. Ahora puede agregar su código entre las dos líneas.

Nota: Tan pronto como seleccione Libro de trabajo en el menú desplegable, notará que aparecen dos líneas de código en la ventana de código. Una vez que hayas seleccionado el evento para el que quieres el código, puedes borrar las líneas que aparecían por defecto.

En la ventana de código de formulario de usuario

Cuando crea UserForms en Excel, también puede usar eventos UserForm para ejecutar códigos basados ​​en acciones específicas. Por ejemplo, puede especificar un código que se ejecuta cuando se hace clic en el botón.

Si bien los objetos Hoja y los objetos ThisWorkbook ya están disponibles cuando abre VB Editor, UserForm es algo que debe crear primero.

Para crear un UserForm, haga clic con el botón derecho en cualquiera de los objetos, vaya a Insertar y haga clic en UserForm.

Esto insertaría un objeto UserForm en el libro de trabajo.

Cuando hace doble clic en el UserForm (o en cualquiera de los objetos que agrega al UserForm), se abre la ventana de código del UserForm.

Ahora, al igual que las hojas de trabajo o ThisWorkbook, puede seleccionar el evento e insertará la primera y la última línea para ese evento. Y luego puede agregar el código en el medio.

En la ventana de código de gráfico

En Excel, también puede insertar hojas de gráficos (que son diferentes a las hojas de trabajo). Una hoja de gráficos está destinada a contener gráficos únicamente.

Cuando haya insertado una hoja de gráfico, podrá ver el objeto Hoja de gráfico en el Editor de VB.

Puede agregar el código de evento a la ventana de código de la hoja del gráfico tal como lo hicimos en la hoja de trabajo.

Haga doble clic en el objeto Hoja de gráfico en el Explorador de proyectos. Esto abrirá la ventana de código para la hoja del gráfico.

Ahora, debe seleccionar Gráfico del menú desplegable en la parte superior izquierda de la ventana del código.

Después de seleccionar Gráfico en el menú desplegable, obtendrá una lista de todos los eventos relacionados con la hoja Gráfico. Puede seleccionar el que desea usar en el menú desplegable en la parte superior derecha de la ventana del código.

Nota: Tan pronto como seleccione Gráfico en el menú desplegable, notará que aparecen dos líneas de código en la ventana de código. Una vez que hayas seleccionado el evento para el que quieres el código, puedes borrar las líneas que aparecían por defecto.

En el módulo de clase

Los módulos de clase deben insertarse como UserForms.

Un módulo de clase puede contener código relacionado con la aplicación, que sería el propio Excel y los gráficos incrustados.

Cubriré el módulo de la clase como un tutorial separado en las próximas semanas.

Tenga en cuenta que, aparte de los eventos OnTime y OnKey, ninguno de los eventos anteriores se puede almacenar en el módulo VBA normal.

Comprensión de la secuencia de eventos

Cuando desencadena un evento, no ocurre de forma aislada. También puede conducir a una secuencia de múltiples desencadenantes.

Por ejemplo, cuando inserta una nueva hoja de trabajo, suceden las siguientes cosas:

1. Se agrega una nueva hoja de trabajo
2. La hoja de trabajo anterior se desactiva
3. Se activa la nueva hoja de trabajo

Si bien en la mayoría de los casos, es posible que no deba preocuparse por la secuencia, si está creando códigos complejos que se basan en eventos, es mejor conocer la secuencia para evitar resultados inesperados.

Comprensión del papel de los argumentos en los eventos de VBA

Antes de pasar a los ejemplos de eventos y las cosas increíbles que puede hacer con él, hay un concepto importante que debo cubrir.

En los eventos de VBA, habría dos tipos de códigos:

• Sin argumentos
• Con argumentos

Y en esta sección, quiero cubrir rápidamente el papel de los argumentos.

A continuación se muestra un código que no tiene ningún argumento (los paréntesis están vacíos):

Private Sub Workbook_Open () MsgBox "Recuerde completar la hoja de horas" End Sub

Con el código anterior, cuando abre un libro de trabajo, simplemente muestra un cuadro de mensaje con el mensaje "Recuerde llenar la hoja de tiempo".

Ahora echemos un vistazo a un código que tiene un argumento.

Private Sub Workbook_NewSheet (ByVal Sh As Object) Sh.Range ("A1") = Sh.Name End Sub

El código anterior usa el argumento Sh que se define como un tipo de objeto. El argumento Sh podría ser una hoja de trabajo o una hoja de gráfico, ya que el evento anterior se activa cuando se agrega una nueva hoja.

Al asignar la nueva hoja que se agrega al libro de trabajo a la variable de objeto Sh, VBA nos ha permitido usarla en el código. Entonces, para hacer referencia al nuevo nombre de la hoja, puedo usar Sh.Name.

El concepto de argumentos será útil cuando revise los ejemplos de eventos de VBA en las siguientes secciones.

Eventos de nivel de libro de trabajo (explicados con ejemplos)

A continuación, se muestran los eventos más utilizados en un libro de trabajo.

NOMBRE DEL EVENTO	QUÉ DESACTIVA EL EVENTO
Activar	Cuando se activa un libro
AfterSave	Cuando se instala un libro como complemento
AntesGuardar	Cuando se guarda un libro
AntesCerrar	Cuando se cierra un libro
AntesImprimir	Cuando se imprime un libro
Desactivar	Cuando un libro de trabajo está desactivado
Hoja nueva	Cuando se agrega una nueva hoja
Abierto	Cuando se abre un libro
HojaActivate	Cuando se activa cualquier hoja del libro
SheetBeforeDelete	Cuando se elimina cualquier hoja
HojaAntesDoubleClick	Cuando se hace doble clic en cualquier hoja
SheetBeforeRightClick	Cuando se hace clic con el botón derecho en cualquier hoja
HojaCalcular	Cuando se calcula o recalcula cualquier hoja
SheetDeactivate	Cuando un libro de trabajo está desactivado
SheetPivotTableUpdate	Cuando se actualiza un libro
HojaSelecciónCambio	Cuando se cambia un libro
WindowActivate	Cuando se activa un libro
Ventana Desactivar	Cuando un libro de trabajo está desactivado

Tenga en cuenta que esta no es una lista completa. Puedes encontrar la lista completa aquí.

Recuerde que el código para el evento Workbook se almacena en la ventana de código de objetos ThisWorkbook.

Ahora echemos un vistazo a algunos eventos útiles del libro de trabajo y veamos cómo se pueden utilizar en su trabajo diario.

Evento abierto del libro de trabajo

Supongamos que desea mostrarle al usuario un recordatorio amigable para que complete sus hojas de horas trabajadas cada vez que abran un libro de trabajo específico.

Puede usar el siguiente código para hacer esto:

Private Sub Workbook_Open () MsgBox "Recuerde completar la hoja de horas" End Sub

Ahora, tan pronto como abra el libro de trabajo que tiene este código, le mostrará un cuadro de mensaje con el mensaje especificado.

Hay algunas cosas que debe saber al trabajar con este código (o los códigos de eventos del libro de trabajo en general):

• Si un libro tiene una macro y desea guardarla, debe guardarla en formato .XLSM. De lo contrario, se perdería el código macro.
• En el ejemplo anterior, el código de evento se ejecutará solo cuando las macros estén habilitadas. Es posible que vea una barra amarilla pidiendo permiso para habilitar macros. Hasta que eso esté habilitado, el código de evento no se ejecuta.
• El código del evento Workbook se coloca en la ventana de código del objeto ThisWorkbook.

Puede refinar aún más este código y mostrar el mensaje solo del viernes.

El siguiente código haría esto:

Private Sub Workbook_Open () wkday = Weekday (Date) If wkday = 6 Then MsgBox "Recuerde llenar la hoja de horas" End Sub

Tenga en cuenta que en la función Día de la semana, al domingo se le asigna el valor 1, el lunes es 2 y así sucesivamente.

Por lo tanto, para el viernes, he usado 6.

El evento Workbook Open puede ser útil en muchas situaciones, como:

• Cuando desea mostrar un mensaje de bienvenida a la persona cuando se abre un libro.
• Cuando desee mostrar un recordatorio cuando se abra el libro.
• Cuando desee activar siempre una hoja de trabajo específica en el libro de trabajo cuando se abre.
• Cuando desee abrir archivos relacionados junto con el libro de trabajo.
• Cuando desee capturar la marca de fecha y hora cada vez que se abre el libro.

Evento de hoja nueva del libro de trabajo

El evento NewSheet se activa cuando inserta una nueva hoja en el libro.

Supongamos que desea ingresar el valor de fecha y hora en la celda A1 de la hoja recién insertada. Puede utilizar el siguiente código para hacer esto:

Private Sub Workbook_NewSheet (ByVal Sh As Object) En caso de error Reanudar siguiente Sh.Range ("A1") = Formato (ahora, "dd-mmm-aaaa hh: mm: ss") End Sub

El código anterior usa "En caso de error, reanudar a continuación" para manejar los casos en los que alguien inserta una hoja de gráfico y no una hoja de trabajo. Dado que la hoja del gráfico no tiene la celda A1, mostraría un error si no se usa "En caso de error, reanudar siguiente".

Otro ejemplo podría ser cuando desee aplicar alguna configuración o formato básico a una nueva hoja tan pronto como se agregue. Por ejemplo, si desea agregar una nueva hoja y desea que obtenga automáticamente un número de serie (hasta 100), puede usar el código a continuación.

Private Sub Workbook_NewSheet (ByVal Sh As Object) En caso de error Continuar con Sh.Range ("A1") .Value = "S. No." .Interior.Color = vbBlue .Font.Color = vbWhite End With For i = 1 To 100 Sh.Range ("A1"). Offset (i, 0) .Value = i Next i Sh.Range ("A1", Range ("A1"). Fin (xlDown)). Borders.LineStyle = xlContinuous End Sub

El código anterior también tiene un poco de formateo. Le da a la celda de encabezado un color azul y hace que la fuente sea blanca. También aplica un borde a todas las celdas llenas.

El código anterior es un ejemplo de cómo un código VBA corto puede ayudarlo a robar unos segundos cada vez que inserta una nueva hoja de trabajo (en caso de que esto sea algo que debe hacer cada vez).

Libro de trabajo BeforeSave Event

El evento Before Save se activa cuando guarda un libro. Tenga en cuenta que el evento se activa primero y luego se guarda el libro.

Al guardar un libro de Excel, podría haber dos escenarios posibles:

1. Lo está guardando por primera vez y mostrará el cuadro de diálogo Guardar como.
2. Ya lo guardó anteriormente y simplemente guardará y sobrescribirá los cambios en la versión ya guardada.

Ahora echemos un vistazo a algunos ejemplos en los que puede utilizar el evento BeforeSave.

Suponga que tiene un nuevo libro de trabajo que está guardando por primera vez y desea recordarle al usuario que lo guarde en la unidad K, entonces puede usar el siguiente código:

Private Sub Workbook_BeforeSave (ByVal SaveAsUI como booleano, cancelar como booleano) Si SaveAsUI, entonces MsgBox "Guardar este archivo en la unidad K" End Sub

En el código anterior, si el archivo nunca se ha guardado, SaveAsUI es True y muestra el cuadro de diálogo Guardar como. El código anterior mostraría el mensaje antes de que aparezca el cuadro de diálogo Guardar como.

Otro ejemplo podría ser actualizar la fecha y la hora en que el archivo se guarda en una celda específica.

El siguiente código insertaría la marca de fecha y hora en la celda A1 de Sheet1 siempre que se guarde el archivo.

Private Sub Workbook_BeforeSave (ByVal SaveAsUI As Boolean, Cancel As Boolean) Worksheets ("Sheet1"). Range ("A1") = Format (Now, "dd-mmm-aaaa hh: mm: ss") End Sub

Tenga en cuenta que este código se ejecuta tan pronto como el usuario guarda el libro de trabajo. Si el libro de trabajo se guarda por primera vez, mostrará un cuadro de diálogo Guardar como. Pero el código ya está ejecutado cuando ve el cuadro de diálogo Guardar como. En este punto, si decide cancelar y no guardar el libro de trabajo, la fecha y la hora ya estarán ingresadas en la celda.

Libro de trabajo antes del evento de cierre

El evento Before Close ocurre justo antes de que se cierre el libro.

El siguiente código protege todas las hojas de trabajo antes de que se cierre el libro.

Private Sub Workbook_BeforeClose (Cancelar como booleano) Dim sh como hoja de trabajo para cada sh en este workbook.Worksheets sh.Protect Next sh End Sub

Recuerde que el código de evento se activa tan pronto como cierra el libro de trabajo.

Una cosa importante que debe saber sobre este evento es que no le importa si el libro de trabajo está realmente cerrado o no.

En caso de que el libro de trabajo no se haya guardado y se le muestre el mensaje que le pregunta si desea guardar el libro de trabajo o no, y hace clic en Cancelar, no guardará su libro de trabajo.Sin embargo, el código del evento ya se habría ejecutado para entonces.

Libro de trabajo antes del evento de impresión

Cuando da el comando de impresión (o el comando Vista previa de impresión), se activa el evento Antes de imprimir.

El siguiente código volvería a calcular todas las hojas de trabajo antes de que se imprima su libro de trabajo.

Private Sub Workbook_BeforePrint (Cancelar como booleano) para cada ws en las hojas de trabajo ws.Calcular Next ws End Sub

Cuando el usuario está imprimiendo el libro de trabajo, el evento se dispararía si él / ella está imprimiendo todo el libro de trabajo o solo una parte de él.

Otro ejemplo a continuación es del código que agregaría la fecha y la hora al pie de página cuando se imprima el libro.

Private Sub Workbook_BeforePrint (Cancelar como booleano) Dim ws As Worksheet For each ws In ThisWorkbook.Worksheets ws.PageSetup.LeftFooter = "Printed On -" & Format (Now, "dd-mmm-aaaa hh: mm") Next ws End Sub

Eventos de nivel de hoja de trabajo (explicados con ejemplos)

Los eventos de la hoja de trabajo tienen lugar en función de los desencadenantes de la hoja de trabajo.

A continuación, se muestran los eventos más utilizados en una hoja de trabajo.

Nombre del evento	¿Qué desencadena el evento?
Activar	Cuando la hoja de trabajo está activada
AntesBorrar	Antes de que se elimine la hoja de trabajo
AntesDoubleClick	Antes de hacer doble clic en la hoja de trabajo
BeforeRightClick	Antes de hacer clic con el botón derecho en la hoja de trabajo
Calcular	Antes de que se calcule o recalcule la hoja de trabajo
Cambiar	Cuando se cambian las celdas de la hoja de trabajo
Desactivar	Cuando la hoja de trabajo está desactivada
PivotTableUpdate	Cuando se actualiza la tabla dinámica en la hoja de trabajo
SelectionChange	Cuando se cambia la selección en la hoja de trabajo

Tenga en cuenta que esta no es una lista completa. Puedes encontrar la lista completa aquí.

Recuerde que el código para el evento de la hoja de trabajo se almacena en la ventana de código de objeto de la hoja de trabajo (en la que desea que se active el evento). Puede haber varias hojas de trabajo en un libro de trabajo, y su código se activará solo cuando el evento tenga lugar en la hoja de trabajo en la que se encuentra.

Ahora echemos un vistazo a algunos eventos útiles de la hoja de trabajo y veamos cómo se pueden utilizar en su trabajo diario.

Evento de activación de hoja de trabajo

Este evento se activa cuando activa una hoja de trabajo.

El siguiente código desprotege una hoja tan pronto como se activa.

Private Sub Worksheet_Activate () ActiveSheet.Unprotect End Sub

También puede usar este evento para asegurarse de que se seleccione una celda específica o un rango de celdas (o un rango con nombre) tan pronto como active la hoja de trabajo. El siguiente código seleccionaría la celda D1 tan pronto como active la hoja.

Private Sub Worksheet_Activate () ActiveSheet.Range ("D1"). Seleccione End Sub

Evento de cambio de hoja de trabajo

Se activa un evento de cambio cada vez que realiza un cambio en la hoja de trabajo.

Bueno … no siempre.

Hay algunos cambios que activan el evento y otros que no. A continuación, se muestra una lista de algunos cambios que no activarán el evento:

• Cuando cambia el formato de la celda (tamaño de fuente, color, borde, etc.).
• Cuando fusionas celdas. Esto es sorprendente ya que, a veces, la combinación de celdas también elimina el contenido de todas las celdas excepto la de la parte superior izquierda.
• Cuando agrega, elimina o edita un comentario de celda.
• Cuando ordena un rango de celdas.
• Cuando usas Goal Seek.

Los siguientes cambios activarían el evento (aunque crea que no debería):

• Copiar y pegar el formato desencadenaría el evento.
• Borrar el formato desencadenaría el evento.
• Ejecutar un corrector ortográfico desencadenaría el evento.

A continuación se muestra un código que muestra un cuadro de mensaje con la dirección de la celda que se ha cambiado.

Private Sub Worksheet_Change (ByVal Target As Range) MsgBox "Acabas de cambiar" & Target.Address End Sub

Si bien esta es una macro inútil, le muestra cómo usar el argumento Target para averiguar qué celdas se han cambiado.

Ahora veamos un par de ejemplos más útiles.

Suponga que tiene un rango de celdas (digamos A1: D10) y desea mostrar un mensaje y preguntarle al usuario si realmente desea cambiar una celda en este rango o no, puede usar el siguiente código.

Muestra un mensaje con dos botones: Sí y No. Si el usuario selecciona "Sí", el cambio se realiza, de lo contrario, se invierte.

Private Sub Worksheet_Change (ByVal Target As Range) Si Target.Row <= 10 y Target.Column <= 4 Then Ans = MsgBox ("Está realizando un cambio en las celdas en A1: D10. ¿Está seguro de que lo desea?", vbYesNo) End If If Ans = vbNo Then Application.EnableEvents = False Application.Undo Application.EnableEvents = True End If End Sub

En el código anterior, verificamos si la celda de destino está en las primeras 4 columnas y las primeras 10 filas. Si ese es el caso, se muestra el cuadro de mensaje. Además, si el usuario seleccionó No en el cuadro de mensaje, el cambio se revierte (mediante el comando Application.Undo).

Tenga en cuenta que he usado Application.EnableEvents = False antes de la línea Application.Undo. Y luego lo invirtí usando Application.EnableEvent = True en la siguiente línea.

Esto es necesario, ya que cuando ocurre la acción de deshacer, también desencadena el evento de cambio. Si no configuro EnableEvent en False, seguirá activando el evento de cambio.

También puede monitorear los cambios en un rango con nombre usando el evento de cambio. Por ejemplo, si tiene un rango con nombre llamado "DataRange" y desea mostrar un mensaje en caso de que el usuario realice un cambio en este rango con nombre, puede usar el siguiente código:

Private Sub Worksheet_Change (ByVal Target As Range) Dim DRange As Range Establecer DRange = Range ("DataRange") Si no se interseca (Target, DRange) no es nada, entonces MsgBox "Acaba de realizar un cambio en el rango de datos" End If End Sub

El código anterior verifica si la celda / rango donde ha realizado los cambios tiene celdas comunes al Rango de datos. Si es así, muestra el cuadro de mensaje.

Evento de cambio de selección de libro

El evento de cambio de selección se activa siempre que haya un cambio de selección en la hoja de trabajo.

El siguiente código volvería a calcular la hoja tan pronto como cambie la selección.

Private Sub Worksheet_SelectionChange (ByVal Target As Range) Aplicación. Calcular End Sub

Otro ejemplo de este evento es cuando desea resaltar la fila y columna activas de la celda seleccionada.

Algo como se muestra a continuación:

El siguiente código puede hacer esto:

Private Sub Worksheet_SelectionChange (ByVal Target As Range) Cells.Interior.ColorIndex = xlNone With ActiveCell .EntireRow.Interior.Color = RGB (248, 203, 173) .EntireColumn.Interior.Color = RGB (180, 198, 231) Finalizar con End Sub

El código primero elimina el color de fondo de todas las celdas y luego aplica el mencionado en el código a la fila y columna activas.

Y ese es el problema con este código. Que quita el color de todas las células.

Si desea resaltar la fila / columna activa mientras mantiene intacto el color en otras celdas, use la técnica que se muestra en este tutorial.

Evento de libro de trabajo de DoubleClick

Este es uno de mis eventos de hoja de trabajo favoritos y verá muchos tutoriales en los que lo he usado (como este o este).

Este evento se activa cuando hace doble clic en una celda.

Déjame mostrarte lo maravilloso que es esto.

Con el siguiente código, puede hacer doble clic en una celda y se aplicará un color de fondo, cambiará el color de la fuente y hará que el texto de la celda esté en negrita;

Private Sub Worksheet_BeforeDoubleClick (ByVal Target As Range, Cancel As Boolean) Cancel = True With Target .Interior.Color = vbBlue .Font.Color = vbWhite .Font.Bold = True End With End Sub

Esto puede ser útil cuando está revisando una lista de celdas y desea resaltar algunas seleccionadas. Si bien puede usar la tecla F4 para repetir el último paso, solo podría aplicar un tipo de formato. Con este evento de doble clic, puede aplicar los tres con solo un doble clic.

Tenga en cuenta que en el código anterior, hice el valor de Cancelar = Verdadero.

Esto se hace para que la acción predeterminada de doble clic esté deshabilitada, que es ingresar al modo de edición. Con Cancelar = Verdadero, Excel no lo pondrá en modo de edición cuando haga doble clic en la celda.

He aquí otro ejemplo.

Si tiene una lista de tareas pendientes en Excel, puede usar el evento de doble clic para aplicar el formato tachado y marcar la tarea como completada.

Algo como se muestra a continuación:

Aquí está el código que hará esto:

Private Sub Worksheet_BeforeDoubleClick (ByVal Target As Range, Cancel As Boolean) Cancel = True CurrFormat = Target.Font.Strikethrough If CurrFormat Then Target.Font.Strikethrough = False Else Target.Font.Strikethrough = True End If End Sub

Tenga en cuenta que en este código, hice doble clic como un evento de alternancia. Cuando hace doble clic en una celda, comprueba si ya se ha aplicado el formato tachado. Si ha sido así, al hacer doble clic se elimina el formato tachado y, si no ha sido así, se aplica el formato tachado.

Evento de Excel VBA OnTime

Los eventos que hemos visto hasta ahora en este artículo se asociaron con uno de los objetos de Excel, ya sea el libro de trabajo, la hoja de trabajo, la hoja de gráficos o los formularios de usuario, etc.

El evento OnTime es diferente a otros eventos, ya que se puede almacenar en el módulo VBA normal (mientras que los otros debían colocarse en la ventana de código de objetos como ThisWorkbook o Worksheets o UserForms).

Dentro del módulo VBA normal, se utiliza como método del objeto de aplicación.

La razón por la que esto se considera un evento es que se puede activar en función de la hora que especifique. Por ejemplo, si quiero que la hoja se recalcule cada 5 minutos, puedo usar el evento OnTime para ello.

O, si quiero mostrar un mensaje / recordatorio en un momento específico del día, puedo usar el evento OnTime.

A continuación se muestra un código que mostrará un mensaje a las 2 pm todos los días.

Sub MessageTime () Application.OnTime TimeValue ("14:00:00"), "ShowMessage" End Sub Sub ShowMessage () MsgBox "It's Lunch Time" End Sub

Recuerde que debe colocar este código en el módulo VBA normal,

Además, aunque el evento OnTime se activará a la hora especificada, debe ejecutar la macro manualmente en cualquier momento. Una vez que ejecute la macro, esperará hasta las 2 p.m. y luego llamará a la macro "ShowMessage".

La macro ShowMessage luego mostraría el mensaje.

El evento OnTime toma cuatro argumentos:

Application.OnTime (Hora mas temprana, Procedimiento, LatestTime, Calendario)

• Hora mas temprana: El momento en que desea ejecutar el procedimiento.
• Procedimiento: El nombre del procedimiento que se debe ejecutar.
• LatestTime (Opcional): En caso de que se esté ejecutando otro código y el código especificado no se pueda ejecutar a la hora especificada, puede especificar el LatestTime al que debe esperar. Por ejemplo, podría ser EarliestTime + 45 (lo que significa que esperará 45 segundos para que se complete el otro procedimiento). Si incluso después de 45 segundos el procedimiento no se puede ejecutar, se abandona. Si no especifica esto, Excel esperará hasta que se pueda ejecutar el código y luego lo ejecutará.
• Horario (opcional): Si se establece en Verdadero, programa un nuevo procedimiento de tiempo. Si es False, cancela el procedimiento establecido previamente. De forma predeterminada, esto es Verdadero.

En el ejemplo anterior, solo usamos los dos primeros argumentos.

Veamos otro ejemplo.

El siguiente código actualizaría la hoja de trabajo cada 5 minutos.

Atenuar NextRefresh as Date Sub RefreshSheet () ThisWorkbook.Worksheets ("Sheet1"). Calcular NextRefresh = Now + TimeValue ("00:05:00") Application.OnTime NextRefresh, "RefreshSheet" End Sub Sub StopRefresh () En caso de error Reanudar Siguiente Application.OnTime NextRefresh, "RefreshSheet",, False End Sub

El código anterior actualizaría la hoja de trabajo cada 5 minutos.

Utiliza la función Now para determinar la hora actual y luego agrega 5 minutos a la hora actual.

El evento OnTime continuará ejecutándose hasta que lo detenga. Si cierra el libro de trabajo y la aplicación de Excel aún se está ejecutando (hay otros libros abiertos), el libro de trabajo que tiene el evento OnTime ejecutándose en él se volverá a abrir.

Esto se maneja mejor deteniendo específicamente el evento OnTime.

En el código anterior, tengo el código StopRefresh, pero debe ejecutarlo para detener el evento OnTime. Puede hacer esto manualmente, asignarlo a un botón y hacerlo presionando el botón o llamarlo desde el evento Cierre del libro de trabajo.

Private Sub Workbook_BeforeClose (Cancelar como booleano) Llamar StopRefresh End Sub

El código de evento "BeforeClose" anterior va en la ventana de código de ThisWorkbook.

Evento de Excel VBA OnKey

Cuando trabaja con Excel, sigue monitoreando las pulsaciones de teclas que usa. Esto nos permite utilizar pulsaciones de teclas como disparador de un evento.

Con el evento OnKey, puede especificar una pulsación de tecla (o una combinación de pulsaciones de tecla) y el código que debe ejecutarse cuando se utiliza esa pulsación de tecla. Cuando se presionan estas teclas, ejecutará el código correspondiente.

Al igual que el evento OnTime, debe tener una forma de cancelar el evento OnKey. Además, cuando configura el evento OnKey para una pulsación de tecla específica, está disponible en todos los libros abiertos.

Antes de mostrarle un ejemplo del uso del evento OnKey, primero permítame compartir los códigos clave que están disponibles para usted en VBA.

CLAVE	CÓDIGO
Retroceso	{RETROCESO} o {BS}
Romper	{PAUSA}
Bloq Mayús	{BLOQ MAYÚS}
Borrar	{BORRAR} o {BORRAR}
Flecha hacia abajo	{ABAJO}
Fin	{FIN}
Ingresar	~
Enter (en el teclado numérico)	{INGRESAR}
Escapar	{ESCAPE} o {ESC}
Casa	{CASA}
En s	{INSERTAR}
Flecha izquierda	{IZQUIERDA}
Num lock	{NUM LOCK}
Página abajo	{PGDN}
Página arriba	{PGUP}
Flecha correcta	{DERECHO}
Bloqueo de desplazamiento	{DESPLAZAMIENTO}
Pestaña	{PESTAÑA}
Flecha arriba	{ARRIBA}
F1 a F15	{F1} a {F15}

Cuando necesite usar cualquier evento onkey, debe usar el código para ello.

La tabla anterior tiene los códigos para pulsaciones de teclas únicas.

También puede combinarlos con los siguientes códigos:

• Cambio: + (Signo de más)
• Control: ^ (Signo de intercalación)
• Alt: % (Porcentaje)

Por ejemplo, para Alt F4, debe usar el código: "% {F4}”- donde% es para la tecla ALT y {F4} es para la tecla F4.

Ahora echemos un vistazo a un ejemplo (recuerde que el código para los eventos OnKey se coloca en el módulo VBA normal).

Cuando presionas la tecla PageUp o PageDown, salta 29 filas por encima o por debajo de la celda activa (al menos eso es lo que está haciendo en mi computadora portátil).

Si desea que salte solo 5 filas a la vez, puede usar el siguiente código:

Sub PageUpDOwnKeys () Application.OnKey "{PgUp}", "PageUpMod" Application.OnKey "{PgDn}", "PageDownMod" End Sub Sub PageUpMod () En caso de error Reanudar Siguiente ActiveCell.Offset (-5, 0) .Activar Fin Sub Sub PageDownMod () En caso de error Reanudar Siguiente ActiveCell.Offset (5, 0) .Activar End Sub

Cuando ejecuta la primera parte del código, ejecutará los eventos OnKey. Una vez que esto se ejecuta, usar la tecla PageUp y PageDown solo haría que el cursor salte 5 filas a la vez.

Tenga en cuenta que hemos utilizado "En caso de error, reanudar a continuación" para asegurarnos de que se ignoren los errores. Estos errores pueden ocurrir cuando presiona la tecla PageUp incluso cuando se encuentra en la parte superior de la hoja de trabajo. Dado que no hay más filas para saltar, el código mostraría un error. Pero como hemos utilizado "En caso de error, reanudar a continuación", se ignorará.

Para asegurarse de que estos eventos de OnKey estén disponibles, debe ejecutar la primera parte del código. En caso de que desee que esté disponible tan pronto como abra el libro de trabajo, puede colocarlo en la ventana de código de ThisWorkbook.

Private Sub Workbook_Open () Application.OnKey "{PgUp}", "PageUpMod" Application.OnKey "{PgDn}", "PageDownMod" End Sub

El siguiente código devolverá las claves a su funcionalidad normal.

Sub Cancel_PageUpDownKeysMod () Application.OnKey "{PgUp}" Application.OnKey "{PgDn}" End Sub

Cuando no especifica el segundo argumento en el método OnKey, devolverá la pulsación de tecla a su funcionalidad normal.

En caso de que desee cancelar la funcionalidad de una pulsación de tecla, para que Excel no haga nada cuando se utiliza esa pulsación de tecla, debe utilizar una cadena en blanco como segundo argumento.

En el siguiente código, Excel no haría nada cuando usamos las teclas PageUp o PageDown.

Sub Ignore_PageUpDownKeys () Application.OnKey "{PgUp}", "" Application.OnKey "{PgDn}", "" End Sub

Deshabilitar eventos en VBA

A veces, es posible que deba deshabilitar eventos para que su código funcione correctamente.

Por ejemplo, supongamos que tengo un rango (A1: D10) y quiero mostrar un mensaje cada vez que se cambia una celda en este rango. Entonces muestro un cuadro de mensaje y le pregunto al usuario si está seguro de que quiere hacer el cambio. Si la respuesta es Sí, se realiza el cambio, y si la respuesta es No, VBA lo desharía.

Puede utilizar el siguiente código:

Private Sub Worksheet_Change (ByVal Target As Range) Si Target.Row <= 10 y Target.Column <= 4 Then Ans = MsgBox ("Está realizando un cambio en las celdas en A1: D10. ¿Está seguro de que lo desea?", vbYesNo) End If If Ans = vbNo Then Application.Undo End If End Sub

El problema con este código es que cuando el usuario selecciona No en el cuadro de mensaje, la acción se invierte (ya que he usado Application.Undo).

Cuando ocurre la operación de deshacer y el valor se cambia de nuevo al original, el evento de cambio de VBA se activa nuevamente y al usuario se le muestra nuevamente el mismo cuadro de mensaje.

Esto significa que puede continuar haciendo clic en NO en el cuadro de mensaje y seguirá apareciendo. Esto sucede porque, en este caso, se ha quedado atascado en el bucle infinito.

Para evitar tales casos, debe deshabilitar los eventos para que el evento de cambio (o cualquier otro evento) no se active.

El siguiente código funcionaría bien en este caso:

Private Sub Worksheet_Change (ByVal Target As Range) Si Target.Row <= 10 y Target.Column <= 4 Then Ans = MsgBox ("Está realizando un cambio en las celdas en A1: D10. ¿Está seguro de que lo desea?", vbYesNo) End If If Ans = vbNo Then Application.EnableEvents = False Application.Undo Application.EnableEvents = True End If End Sub

En el código anterior, justo encima de la línea Application.Undo, hemos usado - Application.EnableEvents = False.

Establecer EnableEvents en False no desencadenaría ningún evento (en el libro actual o en cualquier libro abierto).

Una vez que hayamos completado la operación de deshacer, podemos volver a cambiar la propiedad EnableEvents a True.

Tenga en cuenta que la desactivación de eventos afecta a todos los libros de trabajo que están abiertos actualmente (o abiertos mientras EnableEvents está establecido en False). Por ejemplo, como parte del código, si abre un nuevo libro de trabajo, el evento Workbook Open no funcionará.

Impacto de la pila de deshacer eventos

Primero déjame decirte qué es una pila de deshacer.

Cuando trabaja en Excel, sigue monitoreando sus acciones. Cuando comete un error, siempre puede usar Control + Z para volver al paso anterior (es decir, deshacer su acción actual).

Si presiona Control + Z dos veces, retrocederá dos pasos. Estos pasos que ha realizado se almacenan como parte de la pila Deshacer.

Cualquier evento que cambie la hoja de trabajo destruye esta pila Deshacer.Esto significa que si he hecho 5 cosas antes de activar un evento, no podré usar Control + Z para volver a los pasos anteriores. Activar el evento ha destruido esa pila para mí.

En el siguiente código, uso VBA para ingresar la marca de tiempo en la celda A1 cada vez que hay un cambio en la hoja de trabajo.

Private Sub Worksheet_Change (ByVal Target As Range) Application.EnableEvents = False Range ("A1"). Value = Format (Now, "dd-mmm-aaaa hh: mm: ss") Application.EnableEvents = True End Sub

Como estoy haciendo un cambio en la hoja de trabajo, esto destruirá la pila de deshacer.

Además, tenga en cuenta que esto no se limita solo a eventos.

Si tiene un código que está almacenado en un módulo VBA normal y realiza un cambio en la hoja de trabajo, también destruiría la pila de deshacer en Excel.

Por ejemplo, el siguiente código simplemente ingresa el texto "Hola" en la celda A1, pero incluso ejecutar esto destruiría la pila de deshacer.

Sub TypeHello () Range ("A1"). Value = "Hello" End Sub

También le pueden gustar los siguientes tutoriales de Excel VBA:

• Trabajar con celdas y rangos en Excel VBA.
• Trabajar con hojas de trabajo en Excel VBA.
• Trabajar con libros de trabajo en Excel VBA.
• Bucles de Excel VBA: la guía definitiva.
• Usando IF Then Else Statment en Excel VBA.
• Para el siguiente bucle en Excel.
• Creación de funciones definidas por el usuario en Excel VBA.
• Cómo crear y usar complementos en Excel.
• Cree y reutilice macros guardándolas en el Libro de macros personal.