Una de las frustraciones típicas al programar aplicaciones o macros en Excel con VBA, es el hecho de que no hay un objeto tipo tabla nativo que se pueda utilizar en los formularios. Cómo Excel per se manejo objetos tipo tabla, se entiende la ausencia de estos dentro del editor de Visual Basic. No obstante, a través de un objeto básico como el ListBox, podemos suplir esa necesidad ¿Quieres saber cómo hacerlo? ¡Manos a la obra!
Tabla de Contenido
Preparando los datos que utilizaremos
En un archivo nuevo de Excel, capturamos los siguientes datos que utilizaremos para probar.
Una vez capturados los datos, seleccionamos todo, luego vamos al menú Insertar y en el grupo Tablas, presionamos el botón Tabla.
En la ventana de diálogo llamada Crear tabla, marcamos la casilla titulada La tabla tiene encabezados, luego presionamos el botón Aceptar.
En el menú Diseño de tabla, cambiamos el Nombre de la tabla a productos
Preparando el formulario para los datos
Ahora que ya tenemos los datos listos, necesitamos crear el formulario con el que vamos a trabajar. En la esquina superior izquierda, encontramos la opción Personalizar barra de herramientas de acceso rápido. Con un click abrimos dicha opción y elegimos Más comandos.
En la ventana de diálogo llamada Opciones de Excel, seleccionamos la categoría Personalizar cinta de opciones y marcamos la casilla Programador que se encuentra dentro de la sección Pestañas principales. Finalmente presionamos el botón Aceptar.
En el menú Programador, presionamos el botón Visual Basic (Alt + F11)
En la ventana de Visual Basic, presionamos el botón derecho del ratón sobre VBAProject, entonces seleccionamos Insertar y luego damos click en UserForm.
Una vez tengamos el formulario a la vista, lo ampliamos un poco con el finde hacer espacio para nuestros datos.
Vamos a necesitar algunos objetos Label para las etiquetas, TextBox para las cajas de texto, ComboBox para las listas, un ListBox que será nuestra tabla y algunos CommandButton. En el Cuadro de herramientas, los podemos encontrar de esta manera.
Después de trabajar un rato el espacio para la captura de nuevos productos, debe quedar aproximadamente como sigue.
Ajustamos la propiedad Name de cada objeto, excepto los Label (no lo necesitan), los dejaremos en: txtProducto, txtMarca, cmbPresenta, txtNeto, cmbMedida, txtCantidad, tatPrecio, el botón llevará el nombre de btnNuevo y en su propiedad Caption la leyenda “Añadir Nuevo Producto”.
Después de eso, colocamos un ListBox y lo ajustamos lo suficientemente grande para que ocupe la mayor parte del espacio restante y debajo colocamos dos botones. La propiedad Name del ListBox será tblProductos y de los botones será btnEliminar y btnModificar. El Caption de estos dos últimos será cada uno de los texto que se pueden ver en la imagen de abajo.
Lo último por hacer, modificar la propiedad Caption del UserForm con la leyenda “Inventario de Productos”. También seleccionamos el ListBox y cambiamos su propiedad ColumnCount a 8, y su propiedad RowSource para que apunte a nuestra tabla, con la fórmula:
=productos
Debe quedar como sigue:
Algo importante a resaltar es que el valor de la propiedad ColumnCount es exactamente la misma cantidad de campos o columnas que tienen nuestro datos, así cuando establecemos RowSource con nuestra tabla, esta hace la comparación y encaja cada nombre de columna de forma automática.
Esto también funciona con un rango de celdas de Excel, si nos aseguramos de que arriba de ese rango exista una fila con los títulos o encabezados de cada columna.
Programación inicial del formulario
En la parte superior, encontraremos el botón Ejecutar Sub / User Form, junto con un botón de Interrumpir (pausa) y otro para Reestablecer (Detener)
Al ejecutar el formulario, podemos notar que los Combos se encuentran vacíos y la columna Precio de nuestra tabla no se alcanza a notar.
Para corregir eso, vamos a comenzar nuestra programación. Entonces, presionamos la tecla F7 para ingresar al editor de código.
Borramos el código autogenerado para comenzar con nuestras variables. La variable fila nos permitirá conocer la fila actual, la variable cuantos nos dirá las filas que llevamos. Las variables arPresenta y arMedida seran dos Arrays de tipo Variant con los datos para llenar los ComboBox. La variable tabla representa la tabla que es un objeto ListObject y filaProd nos permitirá añadir nuevas filas con datos.
Dim fila, cuantos As Integer
Dim mensaje As String
Dim arPresenta As Variant
Dim arMedida As Variant
Dim tabla As ListObject
Dim filaProd As ListRow
Ahora, abrimos la lista General y seleccionamos UserForm.
Después, abrimos la lista de eventos y cambiamos de Click a Activate.
Debe quedar así:
Por cierto, se puede borrar por completo el evento UserForm_Click.
Comenzamos estableciendo la fila activa a cero ya que no hay ninguna al inicio.
fila = 0
Luego, llenamos el Array arPresenta con la lista de presentaciones que necesitamos para nuestros productos.
arPresenta = Array("Lata", "Botella", "PET", "Bolsa", "Tetrapack", "Empaque", "Caja", "Individual")
Hacemos lo mismo con las unidades de medida.
arMedida = Array("Mililitros", "Litros", "Gramos", "Kilos", "Piezas")
una vez hechos, nos aseguramos que nuestros dos ComboBox los utilicen asignándolos a su propiedad List.
cmbPresenta.List = arPresenta
cmbMedida.List = arMedida
Para evitar que aparezcan vacíos al inicio, nos aseguramos que apunte al elemento en la posición 0 de su respectivo Array. Eso lo logramos con la propiedad ListIndex.
cmbPresenta.ListIndex = 0
cmbMedida.ListIndex = 0
Por último, hacemos que cada columna dentro del ListBox tenga un ancho fijo con la propiedad ColumnWidths. Para ello, le pasamos una lista separada por punto y coma con los valores en píxeles. Estos valores pueden variar según el ancho del ListBox y también del ancho que queremos asignar a cada columna. En mi caso, después de probar un rato, me quedo así:
tblProductos.ColumnWidths = "20;120;75;80;40;60;60;50"
Al momento, la programación luce así:
Entonces, al ejecutar de nuevo el formulario, podemos observar que los ComboBox ya no están vacíos y la columna Precio de nuestra tabla no desaparece.
Añadir elementos nuevos a la tabla
Con un doble click al botón Añadir Nuevo Producto, debe ser suficiente para ingresar al evento Click del mismo. También se puede desde el editor de código, al cambiar el objeto de UserForm a btnNuevo.
Comenzaremos a trabajar justo donde esta el curso de escritura.
Comenzaremos con una variable local de tipo Boolean. Esta variable comenzará en True ya que nos indicará si podemos insertar el registro o no.
Dim insertar As Boolean
insertar = True
Limpiamos la variable para los mensajes.
mensaje = ""
Después con la función Len, revisamos si la longitud (cantidad de caracteres) de cada objeto con datos es cero, en cuyo caso preparamos un mensaje apropiado para cada caso y cambiamos nuestra bandera a False, pues no se podrá insertar el registro si falta alguno.
If Len(txtProducto.Value) = 0 Then
mensaje = mensaje + "Nombre del producto" + vbNewLine
insertar = False
End If
If Len(txtMarca.Value) = 0 Then
mensaje = mensaje + "Marca del producto" + vbNewLine
insertar = False
End If
If Len(txtNeto.Value) = 0 Then
mensaje = mensaje + "Contenido Neto" + vbNewLine
insertar = False
End If
If Len(txtCantidad.Value) = 0 Then
mensaje = mensaje + "Cantidad de producto" + vbNewLine
insertar = False
End If
If Len(txtPrecio.Value) = 0 Then
mensaje = mensaje + "Precio del producto" + vbNewLine
insertar = False
End If
Si la bandera se movió a False (Not) significa que no se puede insertar el registro ya que falta algún dato, para cuando eso suceda, enviamos un mensaje para notificarle al usuario.
If Not insertar Then
MsgBox mensaje, vbInformation, "ATENCIÓN: Datos faltantes"
Pero, si la bandera no cambió a False, significa que tenemos los datos completos.
Else
Limpiamos temporalmente la propiedad RowSource del ListBox que estamos usando como tabla, ya que de no hacerlo, no nos permitirá añadir elementos nuevos a nuestro ListObject por estar bloqueado.
tblProductos.RowSource = ""
Asignamos nuestra tabla productos al objeto ListObject con la sentencia Set.
Set tabla = Hoja1.ListObjects("productos")
Contamos las filas de nuestra tabla con la propiedad Count de la colección ListRows que pertenece a nuestro ListObject.
cuantos = tabla.ListRows.Count
Con la sentencia Set, asignamos a nuestro objeto de tipo ListRow el método Add de la colección ListRows de nuestra tabla.
Set filaProd = tabla.ListRows.Add
El objeto ListRow contiene el método Range que es un número entero con el valor posicional de cada columna empezando con 1 (no 0, pues no es un Array). Como la primera columna es el ID, asignamos a ella el valor de la variable cuantos (la cuenta de las filas) y le sumamos 1 para crear el ID correspondiente. Las demás columnas las llenamos con los valores de cada objeto, usando la función Val para convertir a número los elementos como cantidad y precio.
filaProd.Range(1) = cuantos + 1
filaProd.Range(2) = txtProducto.Value
filaProd.Range(3) = txtMarca.Value
filaProd.Range(4) = cmbPresenta.Value
filaProd.Range(5) = Val(txtNeto.Value)
filaProd.Range(6) = cmbMedida.Value
filaProd.Range(7) = Val(txtCantidad.Value)
filaProd.Range(8) = Val(txtPrecio.Value)
Generamos el mensaje de éxito.
mensaje = "El producto se ha insertado con éxito"
Eliminamos la instancia de ListRow al asignarla a Nothing, pues ya no la necesitamos.
Set filaProd = Nothing
Asignamos nuestra tabla nuevamente como el RowSource de nuestro ListBox
tblProductos.RowSource = tabla
Limpiamos la propiedad Value de cada objeto TextBox y Regresamos el ListIndex de cada ComboBox al primer valor de su lista, con eso estamos listos para insertar un nuevo producto.
txtProducto.Value = ""
txtMarca.Value = ""
cmbPresenta.ListIndex = 0
txtNeto.Value = ""
cmbMedida.ListIndex = 0
txtCantidad.Value = ""
txtPrecio.Value = ""
Y mostramos el mensaje de éxito con una ventana de diálogo de tipo vbInformation
MsgBox mensaje, vbInformation, "¡ATENCIÓN!"
Por último, cerramos el If – Else que tenemos abierto.
End If
¡Es hora de probar lo que hemos programado!
Eliminar elementos de la tabla
Es momento de programar el botón de eliminar. Dado que estamos usando un ListBox como una tabla, necesitamos seleccionar el objeto llamado btnEliminar de la lista de Objetos en el editor de código.
El evento principal de cada botón es Click, así que trabajaremos dentro del evento que le corresponde.
Lo primero que necesitamos es una variable de tipo Variant que almacenará la respuesta de una caja de diálogo; ya que para evitar eliminar productos de manera accidental, primero preguntaremos por ello.
Dim respuesta As Variant
Si la variable fila sigue en cero, significa que no hemos seleccionado ningún registro, así que creamos el mensaje correspondiente y lo mostramos.
If fila = 0 Then
mensaje = "Seleccione un producto, por favor"
MsgBox mensaje, vbCritical, "¡AVISO IMPORTANTE!"
En caso contrario…
Else
Creamos la pregunta incluyendo el número de registro concatenado con la función Str, y la lanzamos al usuario como una ventana de diálogo de tipo vbYesNo (Sí/No)
mensaje = "¿Desea eliminar el registro número " + Str(fila) + "?"
respuesta = MsgBox(mensaje, vbYesNo)
Y si la respuesta fue el botón de Sí (vbYes)
If respuesta = vbYes Then
Nuevamente, desactivamos la propiedad RowSource del ListBox para que nos deje manipular la tabla.
tblProductos.RowSource = ""
Con la sentencia Set, apuntamos nuestra ListObject hacia la tabla de productos.
Set tabla = Hoja1.ListObjects("productos")
Con la fila seleccionada, borramos la fila de la colección ListRows utilizando el método Delete.
tabla.ListRows(fila).Delete
Asignamos nuevamente la propiedad RowSource hacia nuestro ListObject para que se actualice el ListBox.
tblProductos.RowSource = tabla
Creamos el mensaje de éxito.
mensaje = "Producto eliminado con éxito"
Y lo lanzamos con una ventana de diálogo de tipo vbInformation.
MsgBox mensaje, vbInformation, "¡AVISO IMPORTANTE!"
Por último, cerramos los dos IF que tenemos abiertos.
End If
End If
Nuestro código debe lucir como sigue:
Para que esto último funcione, necesitamos obtener la fila seleccionada. La forma de lograrlo es simplemente utilizando el evento Click de nuestro ListBox, así que lo seleccionamos de la lista de Objetos.
Eso nos debe generar su evento Click.
Los objetos ListBox se comportan como un Array, es decir parten de la primera posición o índice 0 a la posición n. Este valor lo obtenemos con la propiedad ListIndex que nos devuelve la posición dentro del ListBox a la que le dimos click. Sin embargo, dado que los ListObject no son Arrays, estos comienzan desde la posición 1, así que siempre tendremos que sumar 1 para obtener la posición correcta.
fila = tblProductos.ListIndex + 1
Eso es todo lo que colocaremos ahí.
¡Es hora de probar lo que hemos programado!
Modificar elementos de la tabla
La modificación de filas, la haremos en dos partes. Primero tomaremos los datos de la fila seleccionada y los enviaremos a los objetos de arriba. Segundo, modificaremos el botón de Nuevo producto para que nos sirva para actualizar dichos datos, sólo cuando presionamos el botón de modificar. De esa manera, nos evitaremos repetir mucho código.
Para comenzar, tomamos el objeto btnModificar de la lista de objetos.
Con eso generamos su evento Click.
Igual que hicimos con el botón de eliminar, si la fila seleccionada vale cero, entonces no hemos seleccionado nada, así que generamos el mensaje de error y lo enviamos en una ventana de diálogo de tipo vbCritical.
If fila = 0 Then
mensaje = "Seleccione un producto, por favor"
MsgBox mensaje, vbCritical, "¡AVISO IMPORTANTE!"
En caso contrario…
Else
Con la sentecia Set, apuntamos nuestro ListObject hacia la tabla de productos.
Set tabla = Hoja1.ListObjects("productos")
El objeto ListObject contiene la colección DataBodyRange que representa los datos de la tabla (sin encabezados) ordenados por fila y columna. En este caso nuestra variable fila contiene el número de la fila seleccionada, así que simplemente hacemos que cada objeto tome el valor de ahí en el orden de presentación de los datos. Es decir, el nombre del producto es la columna 2, la marca es la columna 3 y así sucesivamente.
txtProducto.Value = tabla.DataBodyRange(fila, 2)
txtMarca.Value = tabla.DataBodyRange(fila, 3)
cmbPresenta.Value = tabla.DataBodyRange(fila, 4)
txtNeto.Value = tabla.DataBodyRange(fila, 5)
cmbMedida.Value = tabla.DataBodyRange(fila, 6)
txtCantidad.Value = tabla.DataBodyRange(fila, 7)
txtPrecio.Value = tabla.DataBodyRange(fila, 8)
Para evitar dejar una edición de datos a medias, deshabilitamos el botón de eliminar.
btnEliminar.Enabled = False
Hacemos lo mismo con el botón de Modificar.
btnModificar.Enabled = False
Y cambiamos la propiedad Caption del botón de nuevo producto, ya que ahí continuaremos.
btnNuevo.Caption = "Actualizar Datos"
Por último, cerramos el único IF que tenemos abierto.
End If
El código del botón de modificar, debe lucir así:
Para cerrar el proceso, haremos algunos cambios al botón de nuevo producto (btnNuevo) Abrimos su código, localizamos justo la línea donde apunta la flecha.
Damos Enter. Después preguntamos si el botón de nuevo contiene la leyenda “ActualizarDatos”
If btnNuevo.Caption = "Actualizar Datos" Then
Enviamos los datos de los objetos del formulario hacia el objeto ListObject con la tabla de productos, uno por uno, con la fila seleccionada.
tabla.DataBodyRange(fila, 2) = txtProducto.Value
tabla.DataBodyRange(fila, 3) = txtMarca.Value
tabla.DataBodyRange(fila, 4) = cmbPresenta.Value
tabla.DataBodyRange(fila, 5) = txtNeto.Value
tabla.DataBodyRange(fila, 6) = cmbMedida.Value
tabla.DataBodyRange(fila, 7) = txtCantidad.Value
tabla.DataBodyRange(fila, 8) = txtPrecio.Value
Habilitamos los botones de eliminar y modificar.
btnEliminar.Enabled = True
btnModificar.Enabled = True
Regresamos la propiedad Caption del botón nuevo con la leyenda “Añadir Nuevo Producto”
btnNuevo.Caption = "Añadir Nuevo Producto"
Y creamos el mensaje de actualización exitosa.
mensaje = "El producto se ha actualizado con éxito"
En caso contrario…
Else
Así vamos al momento.
La damos una sangría a las 11 líneas que están debajo de ese Else, y justo donde apunta la flecha, presionamos Enter.
Y colocamos ahí el final del IF que tenemos abierto.
End If
Es decir…
¡Es momento de probar todo el código que hemos escrito!
¿Te ha resultado? Déjanos saber en los comentarios aquí abajo, en nuestra cuenta de twitter @cablenaranja7 o en nuestra página de facebook.
Docente, IT Manager, Blogger & Developer. Escribo por diversión, educo por pasión. | Grandstanding is not my thing.
