• Author / Uploaded
• Ruben Purca

Citation preview

EXCEL AVANZADO CON MACROS Laboratorio – 04 TEMA:

Nro. DD-106 Página 1/19

FORMULARIOS

OBJETIVOS     

Identificar las principales características de los formularios en VBA Implementar formularios en VBA Identificar y utilizar los principales controles de formularios Personalizar las propiedades de los controles de un formulario Identificar código VBA en los eventos en un formulario

REQUERIMIENTOS  

PC con 2 GB memoria RAM Microsoft Excel instalado

INTRODUCCIÓN Los formularios en VBA permiten implementar interfaces de usuario mucho más amigables. Los formularios se implementan en dos etapas: 1. Diseño del formulario:  En esta etapa se implementa “Visualmente” el formulario arrastrando controles desde una barra de herramientas hacia la superficie de dicho formulario.  Una vez que se han ubicado los controles sobre el formulario se procede a personalizar el aspecto visual de cada control, para esto se hace uso de la ventana de “Propiedades” donde encontraremos un listado completo de cada una de las propiedades que posee el control que en ese momento se encuentre seleccionado 2. Programación de código:  Una vez finalizada la etapa de diseño anterior se procede a ingresar el código VBA que deberá ejecutarse como respuesta a diversas acciones que realice el usuario del formulario durante su interacción. Por ejemplo se debe incluir el código que debe ejecutar el formulario cuando un usuario presione con el mouse los botones que hayamos situado sobre él, o tal vez código que debe ejecutarse ante la presión de alguna determinada tecla o combinación de teclas que efectúe el usuario.  Este código a ingresar se define en bloques de código en forma de Subrutinas denominados “Eventos”.

PROCEDIMIENTO 1. Diseño de formularios (PARTE I) 1.1. Crear un documento nuevo     

Encienda la PC utilice la cuenta de usuario: tecsup clave: Virtu@l Inicie el software MS Excel Crear un nuevo libro con soporte para macros Guardar el nuevo libro con el nombre Lab_04 .xlsm en la carpeta creada en la unidad E:\ Renombrar la hoja 1 con el nuevo nombre “datos”

1

EXCEL AVANZADO CON MACROS Laboratorio – 04



Nro. DD-106 Página 2/19

Ingrese los siguientes textos y aplique el formato indicado en la hoja “datos”

1.2. Creación de un formulario simple 1



Inserte un botón ActiveX cerca de la celda F1. Ficha: Desarrollador Categoría: Controles  Herramienta: Insertar  Controles ActiveX



Para cambiar el nombre del nuevo botón y el texto que muestra dicho botón aplicaremos el siguiente procedimiento: o Click derecho sobre el botón  Propiedades o En la ventana Propiedades. Modifique los siguientes valores: Nuevo Valor Propiedad

2

3

(Name)

botonIngresar

Caption

Ingreso de datos

Font

Negrita, Tamaño 12



Verifique el nuevo aspecto que tiene el botón ActiveX



Doble click al botón para modificar el código asociado al evento click de dicho botón. Ingrese el siguiente comando:



Antes de verificar la acción del botón anterior. Desactive el modo de diseño a través de la siguiente herramienta de la ficha Desarrollador: 2

EXCEL AVANZADO CON MACROS Laboratorio – 04



Nro. DD-106 Página 3/19

Presione el nuevo botón y realice el ingreso de algunos registros a través del formulario que aparece. (Cierre cualquier cuadro de diálogo que pueda mostrarse)

Guardar los cambios y cerrar formulario

 

Cierre el formulario anterior. Verifique que los registros ingresados se hayan adicionado a la hoja “datos” como se muestra a continuación:



Mencione algunas ventajas y desventajas del uso del comando ”ShowDataForm” para la creación de un formulario de ingreso y consulta de datos Desventajas Ventajas Te permite agregar valores con un formulario ya Borra los datos colocados anteriormente si no se definido por excel coloca en la celda correcta

1.3. Creación de un formulario de ingreso de datos personalizado 

Ingrese a la herramienta Visual Basic desde la ficha Desarrollador

3

EXCEL AVANZADO CON MACROS Laboratorio – 04

Nro. DD-106 Página 4/19



En la herramienta visual Basic. Agregue un nuevo Formulario empleando la opción del menú: Insertar  UserForm



A continuación se muestra el aspecto del nuevo Formulario totalmente vacío



Empleando la ventana de propiedades modificaremos algunas propiedades del nuevo formulario Nota: En caso no estuviera visible la ventana de propiedades puede activarla a través de la opción del menú: Ver  Ventana Propiedades o también presionando la tecla F4 Nuevo Valor Propiedad



(Name)

formIngresoNotas

BackColor

&H00C0FFFF&

Caption

Ingreso de notas finales

Picture

Seleccione el archivo “logo.gif” o algún otro

PictureAlignment

4 – fmPictureAlignmentBottomRight

A continuación se muestra el aspecto del nuevo formulario

4

EXCEL AVANZADO CON MACROS Laboratorio – 04

Nro. DD-106 Página 5/19

1.4. Trabajar con controles de formularios 

Haremos uso de la ventana de Cuadro de herramientas, la cual contiene los controles más comunes para el diseño de formularios Nota: En caso no estuviera visible la ventana de “Cuadro de herramientas” puede activarla a través de la opción del menú: Ver  Cuadro de herramientas Checkbox: Casillas de verificación

Label: Etiquetas o títulos TextBox: Cajas para ingreso de datos

RadioButton: Elementos de selección única

Button: Botón de comandos



Desde la ventana “Cuadro de herramientas” arrastre los siguientes controles al formulario:

Cuadros de Texto: (TextBox 1 - 4)



Desde la ventana de Propiedades, modifique las siguientes propiedades de cada uno de los controles Label: 5

EXCEL AVANZADO CON MACROS Laboratorio – 04 Control

Nro. DD-106 Página 6/19

Nuevo Valor

Propiedad

Label1

Caption

Nro

Label2

Caption

Curso

Label3

Caption

Nota final

Label4

Caption

Estado



Verifique el efecto que produjo la modificación de propiedades anterior



Desde la ventana de Propiedades, modifique las siguientes propiedades de cada uno de los controles TextBox: Control Nuevo Valor Propiedad



TextBox1

(Name)

txtNro

TextBox2

(Name)

txtCurso

TextBox3

(Name)

txtNota

TextBox4

(Name)

txtEstado

Desde la ventana de Propiedades, modifique las siguientes propiedades de cada uno de los controles CommandButton: Control Nuevo Valor Propiedad CommandButton1 (Name)

btnAceptar

Caption

Aceptar

CommandButton2 (Name)

btnSalir

Caption

Salir

6

EXCEL AVANZADO CON MACROS Laboratorio – 04

Nro. DD-106 Página 7/19

1.5. Trabajar con eventos 

  



Actualmente el formulario es una simple interfaz visual pero no tiene incorporada ningún tipo de funcionalidad. Para poder asignarle funcionalidad a los diferentes componentes de nuestro formulario se necesita especificar código VBA. Este código generalmente es ingresado como eventos asociados a los controles involucrados. Se desea que al presionar el botón Salir se cierre todo el formulario. Para ello. Asignaremos el código necesario al evento click del botón “Salir” de nuestro formulario. Presione “doble click” sobre el botón Salir. Se mostrará una ventana de código con el esqueleto de la subrutina asociada al evento click de dicho botón

Adicionaremos al evento anterior la única instrucción VBA requerida para cerrar el formulario

1.6. Verificar la acción de los eventos 

Para verificar el funcionamiento de nuestro formulario y de la única acción implementada “Salir”. Debemos adicionar un botón a nuestra hoja Excel que permita cargar el formulario creado.  Regrese al libro creado en Excel.  Inserte un botón ActiveX a la altura de la celda H1. Empleando Ficha: Desarrollador Categoría: Controles  Herramienta: Insertar  Controles ActiveX  Para cambiar el nombre del nuevo botón y el texto que muestra dicho botón aplicaremos el siguiente procedimiento: o Click derecho sobre el botón  Propiedades o En la ventana Propiedades. Modifique los siguientes valores: Nuevo Valor Propiedad (Name)

botonAbrirformulario

Caption

Abrir Formulario

Font

Negrita, Tamaño 12 7

EXCEL AVANZADO CON MACROS Laboratorio – 04

Nro. DD-106 Página 8/19



Verifique el nuevo aspecto que tiene el botón ActiveX



Doble click al botón para modificar el código asociado al evento click de dicho botón. Ingrese el siguiente comando:



Antes de verificar la acción del botón anterior. Desactive el modo de diseño a través de la siguiente herramienta de la ficha Desarrollador:



Presione el nuevo botón y verifique si se muestra el nuevo formulario que acaba de crear y si funciona el botón Salir



Anote a continuación los principales detalles asociados al desarrollo del ejercicio realizado hasta el momento Se utiliza las diferentes herramientas de Controles X, en el UserForm, también se interactua con los eventos al poner la programación el código que llama a los nombres de los controlesX

1.7. Implementar el código del evento click del botón Aceptar  

Regrese al editor de Visual Basic Indicaremos en palabras la secuencia de acciones que debe realizar el botón Aceptar: 1. Declarar variables para referenciar la fila donde se incluirán los datos del nuevo registro 2. Buscar en la hoja “Datos” de nuestro libro Excel la ubicación para la nueva fila de datos 8

EXCEL AVANZADO CON MACROS Laboratorio – 04

Nro. DD-106 Página 9/19

 

3. Copiar el valor ingresado en cada uno de los controles TextBox de nuestro formulario en cada una de las celdas de la nueva fila que se acaba de ubicar. Esta acción físicamente agregará la información del nuevo registro en nuestra hoja “Datos” 4. Enviar un aviso al usuario que la inserción del nuevo registro se realizó correctamente 5. Colocar en blanco cada uno de los controles TextBox del formulario para facilitar el ingreso de un nuevo registro. Active el formulario creado en las secciones anteriores Doble click sobre el botón “Aceptar” para acceder al esqueleto de su evento Click



Adicione las siguientes instrucciones al evento anterior 'Declaración de variables requeridas Dim nuevaFila As Long Dim totalFilas As Long Dim hojaDatos As Worksheet 'Asignación de datos a las variables anteriores Set hojaDatos = Worksheets("datos") totalFilas = hojaDatos.Rows.Count

'Ubicar la posición de la nueva fila de datos a utilizar nuevaFila = hojaDatos.Cells(totalFilas, "A").End(xlUp).Offset(1, 0).Row 'Copiar el contenido de cada control TextBox a cada celda. hojaDatos.Cells(nuevaFila, 1).Value = txtNro.Text hojaDatos.Cells(nuevaFila, 2).Value = txtCurso.Text hojaDatos.Cells(nuevaFila, 3).Value = txtNota.Text hojaDatos.Cells(nuevaFila, 4).Value = txtEstado.Text 'Muestra el mensaje sobre el resultado del nuevo registro agregado MsgBox "Registro agregado correctamente…" 'Colocar los controladores de textbox en blanco. txtNro.Text = "" txtCurso.Text = "" txtNota.Text = "" txtEstado.Text = ""

1.8. Verificar la acción del botón Aceptar  

Regrese al libro creado en Excel. Presione el botón que abre nuestro formulario y que ya fue creado previamente

 

Verifique que se muestre nuevamente el formulario Ingrese un nuevo registro y luego presione el botón Aceptar para verificar si adiciona un nuevo registro a la hoja “datos” del libro excel

9

EXCEL AVANZADO CON MACROS Laboratorio – 04

Nro. DD-106 Página 10/19

Estado actual de la hoja de cálculo “Datos” antes del ingreso del nuevo registro

Ingreso de datos para un nuevo registro empleando el formulario creado. Al finalizar el ingreso presione el botón “Aceptar”

Cuadro de diálogo generado por el formulario anterior para avisarle al usuario sobre el éxito de la adición del nuevo registro

Verificación de la existencia del nuevo registro agregado a través de nuestro formulario



Anote a continuación los principales detalles asociados al desarrollo del ejercicio realizado a la fecha El botón aceptar coloca los datos colocandos en las cajas de textos, los coloca en la última casilla y luego borra los datos colocados.

10

EXCEL AVANZADO CON MACROS Laboratorio – 04

Nro. DD-106 Página 11/19

2. Diseño de formularios (PARTE II) 2.1. Crear una nueva hoja de cálculo   

Crear una nueva hoja de cálculo en el libro. Renombrar la etiqueta con el nombre “datos2” Ingrese los siguientes títulos y aplique el formato siguiente:

2.2. Creación de un formulario de ingreso de datos personalizado 

Ingrese a la herramienta Visual Basic desde la ficha Desarrollador



En la herramienta visual Basic. Agregue un nuevo Formulario empleando la opción del menú: Insertar  UserForm



Diseñe el nuevo formulario como se indica a continuación (primero crear los marcos o frames): Marco1

Marco2



Desde la ventana de propiedades modifique las siguientes propiedades de los controles indicados: 11

EXCEL AVANZADO CON MACROS Laboratorio – 04 Control UserForm

Nuevo Valor

Propiedad (Name)

frmNotas

Caption

Ingreso de Notas

Height

270

Width

300

Frame1

Caption

Datos requeridos

Frame2

Caption

Acciones

Label1

Caption

Nro:

Label2

Caption

Curso:

Label3

Caption

Nota:

Label4

Caption

Estado:

TextBox1

(Name)

txtNro

Locked

True

TabIndex

1

(Name)

txtCurso

TabIndex

2

(Name)

txtNota

Locked

True

TabStop

False

(Name)

SpinNota

Min

0

Max

20

SmallChange

1

Orientation

0 – fmOrientationVertical

Value

0

TabIndex

3

(Name)

optAprobado

Caption

Aprobado

GroupName

grupoEstado

TabIndex

5

(Name)

optDesaprobado

Caption

Desaprobado

GroupName

grupoEstado

TabIndex

6

TextBox2 TextBox3

SpinButton1

OptionButton1

OptionButton2

Nro. DD-106 Página 12/19

CommandButton1 (Name)

btnGrabar

Caption Picture

_active__save.gif

PicturePosition

12 – fmPicturePositionCenter

TabIndex

0 12

EXCEL AVANZADO CON MACROS Laboratorio – 04 CommandButton2 (Name)

Nro. DD-106 Página 13/19

btnSalir

Caption Picture

_active__exit.gif

PicturePosition

12 – fmPicturePositionCenter

TabIndex

1

2.3. Programación de código del formulario 

Desde la ventana de proyectos cambie a la vista de código del formulario presionando el siguiente botón:



En la ventana de código seleccione el evento Initialize del Formulario:



Adicione la línea indicada al evento Initialize: Private Sub UserForm_Initialize() limpiarControles End Sub



La Subrutina “limpiarControles” se encargará de reinicializar el contenido de los controles del formulario. Debemos crear dicha subrutina. Adicione la subrutina indicada inmediatamente después del evento Initialize con el siguiente código: Private Sub limpiarControles() txtNro.Text = SiguienteNro() txtCurso.Text = "" txtNota.Text = 0 SpinNota.Value = 0 optAprobado.Value = False optDesaprobado.Value = True End Sub



La Subrutina anterior hace referencia a una función denominada SiguienteNro(). Esta función se encargará de calcular en forma automática el número del nuevo registro a ingresar. Adicionaremos dicha función a continuación de la subrutina limpiarControles() con el siguiente código: Private Function SiguienteNro() As Integer Dim fila As Integer fila = 2 Do While Worksheets("datos2").Cells(fila, 1).Value "" fila = fila + 1 Loop SiguienteNro = fila - 1 End Function 13

EXCEL AVANZADO CON MACROS Laboratorio – 04 

Nro. DD-106 Página 14/19

Para modificar el valor de la nota se hará uso del control SpinButton denominado SpinNota en nuestro formulario. Para ello dicho control posee un evento denominado Change() el cual se ejecuta cada vez que el usuario interactúa con el control. A continuación se muestra el código de dicho control. (Nota: Para acceder al evento Change()presione doble click sobre el control numérico): Private Sub SpinNota_Change() txtNota.Text = SpinNota.Value End Sub



Ahora, cada vez que el contenido del Cuadro de Texto de la nota sea modificada por el control numérico anterior, se debe verificar si la nota que se vaya asignando corresponde a una nota aprobatoria o no y por lo tanto se debe actualizar en forma automática el estado de los botones de radio asociados al estado. Por lo tanto activaremos el evento Change() del cuadro de texto de la nota, para que al modificarse su contenido, automáticamente se actualice su estado: Private Sub txtNota_Change() If txtNota.Value * 1 > 10 Then optAprobado.Value = True optDesaprobado.Value = False Else optAprobado.Value = False optDesaprobado.Value = True End If End Sub



Al presionar el botón Salir deberá cerrarse la ventana, para ello es necesario adicionar el siguiente código al evento click del botón Salir: Private Sub btnSalir_Click() Unload Me End Sub



En la mayoría de los casos es necesario preguntar al usuario si realmente desea cerrar la ventana para evitar manipulaciones involuntarias del botón Salir o del botón Cerrar del propio formulario. Para ello adicionaremos el siguiente código de validación en el formulario en el evento QueryClose() Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer) If MsgBox("Esta seguro de salir", _ vbYesNo, _ "Aviso de seguridad") = vbNo Then Cancel = True End If End Sub

14

EXCEL AVANZADO CON MACROS Laboratorio – 04 

Nro. DD-106 Página 15/19

Finalmente será necesario adicionar el código para el evento click del botón Grabar. A continuación se muestra dicho código: Private Sub btnGrabar_Click() If MsgBox("Esta seguro de grabar el registro?", _ vbYesNo, "Aviso de confirmacion") = vbYes Then With Worksheets("datos2") .Cells(txtNro.Text + 1, 1).Value = txtNro.Text .Cells(txtNro.Text + 1, 2).Value = txtCurso.Text .Cells(txtNro.Text + 1, 3).Value = txtNota.Text .Cells(txtNro.Text + 1, 4).Value = _ IIf(optAprobado.Value = True, "Aprobado", _ "Desaprobado") End With limpiarControles End If End Sub



Adicione una macro a su documento Excel denominada: m_ing_notas con el siguiente código: Public Sub m_ing_notas() frmNotas.Show End Sub

 

Adicione un Botón, de la categoría controles de formularios, a su documento Excel y asocie dicho botón a la macro anterior Ejecute el formulario anterior y adicione registros de cursos y notas:

15

EXCEL AVANZADO CON MACROS Laboratorio – 04



Nro. DD-106 Página 16/19

Anote los detalles más importantes del ejercicio realizado: Se utilizo UserForm para colocar los controles Active X, se cambió en todos los parámetros para poder Colocar los eventos y programar los códigos necesarios que nos permitan grabar salir y colocar todos los datos en la celda de la hoja seleccionada.

2.4. Navegación entre registros 

Modifique el aspecto del formulario y agregue dos botones como se indica a continuación:

16

EXCEL AVANZADO CON MACROS Laboratorio – 04 

Modifique las siguientes propiedades de los nuevos botones: Control Propiedad CommandButton1 (Name)

Nro. DD-106 Página 17/19

Nuevo Valor

btnAnterior

Caption

Anterior

CommandButton2 (Name)

btnSiguiente

Caption

Siguiente



En la ventana de código del formulario seleccione la sección General como se indica a continuación:



En la sección general adicione una variable de nombre “fila”. Dicha variable servirá para referenciar el numero de fila del registro actual:



Adicione la siguiente línea al evento Initialize creada en la sección anterior: Private Sub UserForm_Initialize() limpiarControles fila = 1 End Sub



Adicione el siguiente código al evento click del botón Anterior: Private Sub btnAnterior_Click() With Worksheets("datos2") If fila > 2 Then fila = fila - 1 mostrarRegistro End If End With End Sub



Adicione el siguiente código al evento click del botón Siguiente: Private Sub btnSiguiente_Click() With Worksheets("datos2") If .Cells(fila + 1, 1).Value "" Then fila = fila + 1 mostrarRegistro End If End With End Sub



Los últimos dos eventos requieren el empleo de una subrutina denominada mostrarRegistro. Dicha subrutina se encargará de cargar el formulario con los datos del registro actual. A continuación se muestra el código (agregarlo al final de todo):

17

EXCEL AVANZADO CON MACROS Laboratorio – 04

Nro. DD-106 Página 18/19

Private Sub mostrarRegistro() With Worksheets("datos2") txtNro.Value = .Cells(fila, 1).Value txtCurso.Value = .Cells(fila, 2).Value SpinNota.Value = .Cells(fila, 3).Value End With End Sub



Ejecute el formulario y verifique la operación de los nuevos botones



Anote los detalles más importantes del ejercicio realizado:

3. Trabajo adicional  Adicione al formulario anterior dos botones adicionales (Inicio, Último). Los cuales permitirán desplazarnos hacia el primer registro o hacia el último registro respectivamente  Adicione al formulario anterior un botón denominado BUSCAR el cual al ser presionado buscará el primer registro cuyo nombre de curso sea igual al texto ingresado en el cuadro de texto “txtCurso”

18

EXCEL AVANZADO CON MACROS Laboratorio – 04

Nro. DD-106 Página 19/19

OBSERVACIONES Y CONCLUSIONES Se utiliza el useform para distribuir mejor los controles de ActiveX Los controles de Active X nos sirven para poder generar los códigos que nos permitirán realizar las funciones que deseamos Si se coloca bien el código de programación se podrá interactuar adecuadamente con los controles de Activex Useform , sirven para ejecutar operaciones como el registro, actualización, eliminación o procesamiento de los datos existentes en Excel, o como una interfaz de usuario desde la que se pueden ejecutar macros previamente existentes. Pueden ser empleados desde cualquier hoja, por medio de algún botón que coloquemos, o desde la alguna Macro existente en Excel, para lo cual deberemos colocar la invocación Puede ser empleado para interactuar con algún contenido en cualquiera de las hojas que encuentren en nuestro archivo Excel, o solo interactuar entre los datos disponibles desde el mismo Userform.

19