Ingreso, búsqueda y modificación de datos en Excel con Formulario ActiveX

El objetivo de este artículo es la de crear un formulario que nos sirve para además de introducir datos, tener la posibilidad de buscarlos y modificarlos.  En un artículo anterior habíamos visto como crear un formulario simple para ingreso de datos.

Por otro lado ya hemos visto varios artículos de cómo buscar datos sin embargo esta vez vamos a ver una forma sencilla de efectuar una búsqueda usando el formulario pero con formulas ya conocidas. Siempre empezamos con lo más básico para luego ir agregando funcionalidades e ir creando cosas más complejas.

Antes de empezar vamos a hablar de otras posibilidades que tenemos para manejar datos en Excel. Hay una función que propone para trabajar con formularios. Es muy fácil de usar  y puede servir para nuestro cometido sin necesidad en meterse en el uso de VBA o programación en Excel. Lo pueden chequear en la página de Microsoft con el nombre “Agregar, editar, buscar y eliminar filas usando un formulario de datos”.

En este artículo vamos a ver cómo construir un formulario con el cual podemos agregar datos, y luego poder modificarlos. Además podemos efectuar una búsqueda por código. También es posible modificar cantidades, que generalmente es lo que plantea manejar estos tipos de datos.

Click en la imagen para ver el video o en este link.

Elementos básicos:

En el artículo anterior habíamos visto como crear el formulario e introducir los botones, etiquetas y cajas de texto. A Continuación vamos a describir dichos elementos con los nombres asignados para la aplicación.

1.       Formulario (UserForm): El formulario se llama “Ingreso” y el nombre visible es para este caso “Stock”.

Es decir que las propiedades:

o   Name = Ingreso

o   Caption = Stock

2.       Botones de Comando (CommandButton): se utilizan dos botones de comando más comparado con el artículo anterior cuyas propiedades la vemos a continuación.

                                                        

·         Guardar Nuevo

o   Name = Ingresar

o   Caption = Guardar Nuevo

·         Cerrar

o   Name = Cerrar

o   Caption = Cerrar

·         Buscar

o   Name = Buscar

o   Caption = Buscar

·         Modificar

o   Name = Modifica

o   Caption = Modificar

3.       Cajas de Texto (TextBox): se utilizan las mismas ya utilizados, es decir no sé modifica el formulario del artículo anterior. Los textbox no tienen propiedad Caption. Los nombres de la propiedad “Name”  para “ID”, “Producto”, “Cantidad” y “Descripción” son: Id, Producto, Cantidad y Desc respectivamente.

4.       Etiqueta (Label): las etiquetas utilizadas son tres, Label1, Label2, y Label3 cuyos Caption dentro de las propiedades son “ID”, “Producto” y “Cantidad” respectivamente.

5.       Caja de verificación (CheckBox): para esta aplicación se agrega una caja de verificación.  La propiedad Name es “suma” y la propiedad Caption es “Sumar”.

Es necesario aclarar que para simplificar y hacer algo intuitivo los temas de referencia,  se utiliza la aplicación de los conocimientos de VBA de la forma más básica posible para no confundir ya que no es la intensión enseñar teoria de sistemas ni hablar en lenguaje tan técnico. Esto porque  a alguién muy interiorisado en programación o en sistemas le puede parecer algo burdo o básico la forma en que se utiliza el código.

Por ejemplo, a los controles se los nombra de acuerdo a lo que es y la función que cumple . El boton de ingreso  se podria llamar “ingreso_button” y la etiqueda id “id_label”.  Es la forma correcta,  se utiliza para aplicaciones mucho más avanzadas en dónde los conocimientos de progrmación son mas avanzados. Para algo sencillo como en nuestro caso que intentamos introducir ideas nuevas, no es necesario.

Funcionamiento:

El funcionamiento es similar al que habíamos visto para el formulario de ingreso de datos, cuando deseamos ingresar un dato nuevo el formulario carga automaticamente, para este caso, un ID consecutivo ya que son numeros y se deben rellenar los campos , luego guardar nuevo.

Por otro lado, si queremos modificar un dato como el cursos se posiciona automaticamente en el campo ID, cuando le damos “BackSpace o tecla retroceso” el número se borra y se me habilitan dos botones nuevos, el botón buscar y el botón modificar. Una vez que ponemos un ID que suponemos existente pulsamos el boton buscar, encuentra los datos y una vez modificados se pulsa el boton modificar y los datos han sido modificados.

Cómo ves es muy facil este metodo, es por id. En otros artículos veremos luego otras posibles formas de búsqueda. Empezemos por lo más básico.

Proceso:

Vamos a  comenzar modificando el formulario que ya habíamos visto en un artículo anterior, luego vemos el código utilizado y para qué sirve.

Empezamos desde la pantalla anterior. Tenemos el formulario que ya teníamos al cual solo le vamos a agregar tres elementos nuevos, dos botones de comando y un checkbox o botón de verificación.

El Textbox del ID lo hacemos menos ancho para que entre el botón buscar. El botón modificar y el checkbox los insertamos debajo del textbox cantidad, si es necesario hacemos más grande el formulario.

Ya vimos la denominación de los elementos del formulario al principio del artículo con lo que pasaremos a ver los códigos y a describir que es lo que realizan.

Código:

Recuerden que al hacer doble click sobre el objeto dentro del editor de VBA nos lleva a la parte de edición del evento principal del objeto. Por ejemplo si es un command_button el evento es Click, etc.

INICIALIZACIÓN DEL FORMULARIO: esto código hace que al cargarse el formulario se inicie los objetos de determinada manera o con determinada propiedad. El código hace que al iniciar el formulario la propiedad Enabled (habilitar) del control se inicie en falso para el botón buscar y modificar y en verdadero para ingresar. Además hace que el ID tome el valor del último código ingresado y suma 1 para crear uno nuevo, en este caso.

Private Sub UserForm_Initialize()

 Buscar.Enabled = False

  ingresar.Enabled = True

  modifica.Enabled = False

Id.Value = Range("f2").Value + 1

End Sub

BOTON  GUARDAR NUEVO: este código hace lo que ya  habíamos visto en el artículo relacionado anterior.

Private Sub ingresar_Click()

     With Sheets("Stock productos")

         .Range("A" & 5 + Range("F2").Value).Value = Val(Id)

         .Range("B" & 5 + Range("F2").Value - 1).Value = producto.Value

         .Range("C" & 5 + Range("F2").Value - 1).Value = cantidad.Value

         .Range("d" & 5 + Range("F2").Value - 1).Value = desc.Value

     End With

 End Sub

BOTON CERRAR: este código hace lo que ya  habíamos visto en el artículo relacionado anterior.

Private Sub Cerrar_Click()

Unload Me

End Sub

BOTON BUSCAR: ATENCIÓN CON ESTE CODIGO QUE ES NUEVO!!!! Este código hace lo mismo que hacemos para buscar elementos de una base de datos con el BUSCARV. La diferencia es que lo hacemos con código desde el formulario y no desde celdas de la planilla.  Se utiliza dentro del objeto WorkSheetFunction el método Vlookup (BuscarV en Inglés). Se deben declarar las mismas variables habituales, tal como lo usamos comúnmente. Primero la variable a buscar, luego dónde buscar, luego  el indicador de columnas y luego si queremos coincidencia exacta o no.

Usamos el “On Error Resume Next” en este caso sin peligro ya que lo único que hace es evitarnos mostrar el error de un dato no encontrado. Cuando no encuentra un Id, no muestra resultado. Con el ID introducido buscamos los tres datos que necesitamos, el nombre del producto, la cantidad y la descripción.

Private Sub buscar_Click()

On Error Resume Next

producto.Value = WorksheetFunction.VLookup(Val(Id.Value), Sheets(1).Range("A5:D12"), 2, False)

cantidad.Value = WorksheetFunction.VLookup(Val(Id.Value), Sheets(1).Range("A5:D12"), 3, False)

desc.Value = WorksheetFunction.VLookup(Val(Id.Value), Sheets(1).Range("A5:D12"), 4, False)

End Sub

BOTON MODIFICAR: en este código primeramente declaramos una nueva variable denominada “bus_id”. Para declararla usamos el método “Match” (Coincidir en Inglés) que es el mismo de las formulas de Excel.  Nos arroja como resultado la fila dónde esta el dato que coincide con el introducido en ID. Es decir que compara el valor de Id con la columna de Id’s y nos dice dónde esta el valor buscado. Se utiliza un checkbox condicional para no tener que estar sumando manualmente una cantidad previamente introducida con un valor nuevo a cargar en el stock. Una vez ejecutado el comando se actualizan todos los valores correspondientes.

Recuerden que el With únicamente nos sirve para no tener que volver a declarar una parte del código, en este caso la hoja “Stock Productos”.

Private Sub modifica_Click()

bus_id = WorksheetFunction.Match(Val(Id.Value), Sheets(1).Range("A5:A12"), 0) + 4

    With Sheets("Stock productos")

        .Range("B" & bus_id).Value = producto.Value

    If suma.Value = False Then

        .Range("C" & bus_id).Value = cantidad.Value

    Else

        .Range("C" & bus_id).Value = Range("C" & bus_id).Value + cantidad.Value

    End If

        .Range("d" & bus_id).Value = desc.Value

    End With

End Sub

TEXTBOX ID: este código responde a un evento “Change” (cambio en inglés). Si cambia algo en el textbox id ejecuta las acciones declaradas. En este caso habilita los botones “Buscar y modificar” y deshabilita el botón “Guardar Nuevo”. Cómo con en las propiedades del textbox ID esta declarado como “0”, el focus del cursor esta en el textbox ID, al hacer backspace o tecla retroceso se borra el ID, se ejecuta el código y podemos introducir numero de ID nuevo.

Private Sub Id_Change()

If Val(Id.Value) = 0 Then

 Buscar.Enabled = True

 ingresar.Enabled = False

 modifica.Enabled = True

End If

End Sub

CHECKBOX SUMAR: este código hace que cuando cambia el estado del checkbox se vuelva el focus al textbox de cantidad y  se borre el valor anterior. Se borra el valor en el textbox no así en la base de datos, si actualizamos con el botón modificar se guarda el valor según cómo este el textbox. Si esta tildado suma al valor anterior sino lo reemplaza.

Private Sub suma_Click()

If suma.Value = True Then

cantidad.SetFocus

cantidad.Value = ""

End If

End Sub

-------------------------------------- FIN CÓDIGOS

Recuerden que los artículos algunas veces requieren un mayor o menor nivel de conocimiento en ciertos temas de Excel, por ejemplo formulas avanzadas, macros o VBA, etc. Sin embargo en los artículos se nombran todos los elementos y con esta información podemos hacer nuestras pequeñas investigaciones usando internet, así desarrollamos nuestro conocimiento. Por ejemplo si  utilizamos la función “Y”, que la suponemos conocida, y no sabemos ¿qué es? podemos buscar en internet para ver como se utiliza y cuales son los componentes. Las mini aplicaciones no son más que la recopilación de muchos elementos para dar forma a una nueva idea.

Con todos los elementos vistos podemos construir una aplicación interesante para gestionar datos mediante un formulario. Esperamos que les sirvan las ideas.

Pueden descargar algunos ejemplos (plantillas/templates) de la zona de Descargas. Si tienen alguna duda o consulta pueden hacerlo a través del formulario de contacto.

Les recomendamos que sigan el blog por alguno de los medios, mail o por redes sociales, para recibir las actualizaciones y novedades.

¡Éxitos!

Keys:

·         Formulario de entrada, modificación y búsqueda de datos

·         Altas, bajas y modificaciones con formulario

·         Formulario ActiveX de entrada de datos en Excel

·         Modificaciones de datos en formulario

·         Insertar buscar y modificar datos en Excel

·         Form de búsqueda captura modificación de datos en Excel

·         Agregar, editar, buscar y eliminar filas usando un formulario de datos.