LIMPIAR CONTENIDO DE CONTROLES DE FORMULARIO EN HOJA EXCEL O EN USERFORM

Hola a todos!.

Hoy me gustaría dejaros un par de códigos para borrar el contenido de los controles de formulario, tanto aquellos que se insertan en la hoja excel como aquellos que insertamos en nuestros userform o formularios en el editor de VBA.

Voy a comenzar con los controles insertados en una hoja (controles ActiveX), por ejemplo una en la que tengamos varios: Textbox, ComboBox, ListBox y CheckBox. Para que podamos automatizar el proceso de forma óptima debemos utilizar la instrucción Select Case:

Sub LIMPIAR_CONTROLES()
With ActiveSheet
'Por cada objeto que indiquemos en el select case, borramos contenido
For Each Control In .OLEObjects
Select Case TypeName(Control.Object)
Case "TextBox"
Control.Object.Text = vbNullString
Case "ComboBox"
Control.Object.Clear
Case "ListBox"
Control.Object.Clear
Case "CheckBox"
Control.Object.Value = False
End Select
Next Control
End With
End Sub

Dado que estamos trabajando con controles ActiveX debemos tratar en nuestro código con los objetos OLEObject, que son los que nos van a permitir interactuar con nuestra hoja. A continuación solo tendremos que especificar el tipo de objeto y la forma en la que debemos limpiarlo o vaciarlo en el Select – Case.

Con los Formularios o Userforms resulta más sencillo, dado que haremos referencia a la colección Controls. El resultado del código es similar al anterior:

Private Sub CommandButton1_Click()
'Por cada objeto que indiquemos en el select case, borramos contenido
For Each Control In Me.Controls
Select Case TypeName(Control)
Case "TextBox"
Control.Text = vbNullString
Case "ComboBox"
Control.Clear
Case "ListBox"
Control.Clear
Case "CheckBox"
Control.Value = False
End Select
Next Control
End Sub

Como podéis ver, ambas estructuras son idénticas (salvo por la forma de hacer referencia a los controles de formulario).

Dado que se trata de fragmentos de código bastante específicos y sencillos, considero que no requieren un archivo de ejemplo. Os invito a que los probéis en vuestros proyectos : )

¿Te ha resultado de interés?, puedes apoyar a Excel Signum con una pequeña donación.

Donate Button with Credit Cards

¡¡Muchas gracias!!

Mediante la suscripción al blog, la realización comentarios o el uso del formulario de contacto estás dando tu consentimiento expreso al tratamiento de los datos personales proporcionados según lo dispuesto en la ley vigente (LOPD). Tienes más información al respecto en esta página del blog: Política de Privacidad y Cookies

Anuncios

CARGAR DATOS EN LISTBOX Y REALIZAR BÚSQUEDAS CON ADO Y CONSULTAS SQL

Aunque estoy preparando varios post sobre análisis estadístico de los datos, hoy trataré sobre algo totalmente diferente, los listbox en formularios y la posibilidad de utilizar ADO y las consultas de SQL para buscar y filtrar información.

Vamos seguidamente con un ejemplo para ilustrar el ejercicio. Utilizaré el listado de alumnos que usé para el post de las funciones matriciales, este:

CARGAR DATOS EN LISTBOX Y REALIZAR BUSQUEDAS CON ADO Y CONSULTAS SQL

Y ahora vamos a construir un sencillo formulario en el que vamos a incluir 5 listbox en los que mostraremos la información de la hoja ALUMNOS y también un cuadro de texto (textbox) y un botón de comando para realizar las búsquedas:

Ahora vamos utilizar el siguiente código VBA que vamos a pegar en el evento click del botón de búsqueda, luego lo iremos comentando:

Private Sub CommandButton1_Click()
'Definimos las variables
Dim Dataread As ADODB.Recordset, obSQL As String
Dim cnn As ADODB.Connection, MiLibro As String
Dim Control As control, Nombre As String
'Vaciamos todos los Listbox
For Each control In Me.Controls
If TypeName(Control) = "ListBox" Then
Control.Clear
End If
Next
'grabamos el dato a buscar, si no hay dato la variable Nombre es nula
Nombre = IIf(UCase(Me.TextBox1.Value) = vbNullString, IsNull(Me.TextBox1.Value), UCase(Me.TextBox1.Value))
'Creamos la instrucción SQL según los parámetros que nos interesan, en este caso, el nombre
'y los diferentes carácteres comodín y el operador "like"
obSQL = "SELECT [ALUMNOS$].* " & _
"FROM [ALUMNOS$] " & _
"Where [ALUMNOS$].[NOMBRE] like " & "'%" & Nombre & "%'"
'Guardamos el nombre del libro activo para utilizarlo en la conexión ADO
MiLibro = ActiveWorkbook.Name
'Dejamos el cuadro de búsqueda vacío después de iniciar la consulta
Me.TextBox1.Value = vbNullString
'Iniciamos la conexión ADO
Set cnn = New ADODB.Connection
With cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "DATA SOURCE=" & Application.ActiveWorkbook.Path + "\" & MiLibro
.Properties("Extended Properties") = "Excel 8.0"
.Open
End With
'Procedemos a grababar los datos de la consulta
Set Dataread = New ADODB.Recordset
With Dataread
.Source = obSQL
.ActiveConnection = cnn
.CursorLocation = adUseClient
.CursorType = adOpenForwardOnly
.LockType = adLockReadOnly
.Open
End With
Do Until Dataread.EOF
'Pasamos la información a cada ListBox a través del recordset
With UserForm1
.ListBox1.AddItem Dataread("ID")
.ListBox2.AddItem Dataread("NOMBRE")
.ListBox3.AddItem Dataread("CLASE")
.ListBox4.AddItem Dataread("ASIGNATURAS")
.ListBox5.AddItem Dataread("CALIFICACIONES")
End With
Dataread.MoveNext
Loop
'Liberamos y cerramos variables
Dataread.Close: Set Dataread = Nothing
cnn.Close: Set cnn = Nothing
End Sub

Con esta macro podremos realizar consultas a través de lo que indiquemos en el cuadro de búsqueda del formulario (textbox1) o lo que es lo mismo, en la variable “Nombre”.

Pero vamos explicando poco a poco el código. Para borrar o limpiar los datos que van a contener los listbox durante las sucesivas búsquedas tenemos que utilizar el siguiente proceso.

For Each control In Me.Controls
If TypeName(Control) = "ListBox" Then
Control.Clear
End If
Next

Una vez que tenemos los listbox libres de datos ya podemos iniciar la consulta SQL:

obSQL = "SELECT [ALUMNOS$].* " & _
"FROM [ALUMNOS$] " & _
"Where [ALUMNOS$].[NOMBRE] like " & "'%" & Nombre & "%'"

Me gustaría comentar que aquí utilizamos el dato contenido en la variable “Nombre”, importante para poder utilizar el operador Like y por lo tanto, comodines en nuestra consulta.

Cuando la información del cuadro de texto está vacía, simplemente no mostrará nada en los listbox, eso es porque he introducido mediante una condición que si el dato de búsqueda es vacío, entonces es nulo (y por eso no muestra nada).

Nombre = IIf(UCase(Me.TextBox1.Value) = vbNullString, IsNull(Me.TextBox1.Value), UCase(Me.TextBox1.Value))

Podríamos quitar la condición y entonces al pulsar “Buscar” y cargaríamos todos los datos en los listbox, pero creo que así es más funcional.

En la parte condicional de la sentencia SQL, estamos indicando mediante el uso de caracteres comodín, que se busquen los nombres que empiecen por la información escrita en el textbox.

"Where [ALUMNOS$].[NOMBRE] like " & "'" & Nombre & "%'"

Por ejemplo, todos los nombre que empiecen por “MA”:

CARGAR DATOS EN LISTBOX Y REALIZAR BUSQUEDAS CON ADO Y CONSULTAS SQL2

Si queremos extraer los nombres que acaban en “A”, el código sería así:

"Where [ALUMNOS$].[NOMBRE] like " & "'%" & Nombre & "'"

Y el resultado este:

CARGAR DATOS EN LISTBOX Y REALIZAR BUSQUEDAS CON ADO Y CONSULTAS SQL3

Y si quisiéramos que se buscase el nombre a partir de cualquier fragmento de texto, simplemente colocaríamos “%” a ambos lados de la variable “Nombre”:

"Where [ALUMNOS$].[NOMBRE] like " & "'%" & Nombre & "%'"

y podríamos realizar búsquedas más abiertas, por ejemplo, todos los nombres que contengan “AL” (independientemente si está delante, en el centro o al final). Este es el resultado:

CARGAR DATOS EN LISTBOX Y REALIZAR BUSQUEDAS CON ADO Y CONSULTAS SQL4

Como podéis observar, la clave está en saber utilizar los caracteres comodín correctamente en la sentencia SQL. Es algo sencillo pero hay que tener especial cuidado con la posición de las comillas simples y los espacios.

Por último, ya sabéis que para este tipo de método es necesario activar la referencia Microsoft ActiveX Data Object 2.8 Library en el editor de VBA.

En el archivo de descarga, la búsqueda de los nombres está condicionada a que contengan parte del dato contenido en el buscador.

Descarga el archivo de ejemplo pulsando en: CARGAR DATOS EN LISTBOX Y REALIZAR BÚSQUEDAS CON ADO Y CONSULTAS SQL

SELECCIONAR Y ACTIVAR HIPERVÍNCULO EN LISTBOX

Hace unos días recibía una consulta de cómo insertar y ejecutar hipervínculos desde un formulario, en concreto subir la url de diferentes web y ejecutar el vínculo para que nos direccione a la web que hemos añadido.

Pues bien, lo fundamental es tener claro en qué tipo de control quieres que aparezcan las url´s, para este caso en particular, he sugerido un listbox en propio formulario que cargue las direcciones web desde una página de Excel en la que las vamos añadiendo a una columna.

Partiendo de este ejemplo, vamos a denominar la página como “DATOS” y en la primera columnas, que llamaremos “DIRECCIONES” vamos a ir pegando las páginas que consideremos de nuestro interés.

Ahora, tenemos que añadir un userform y seguidamente un listbox, y para que el listbox cargue los datos, usaremos el siguiente código:

Private Sub ListBox1_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
With Sheets("DATOS")
fin = 2 + Application.CountA(.Range("A:A"))
ListBox1.List = .Range("A2:A" & fin).Value
End With
End Sub

La particularidad de este código es que los datos se cargarán en el listbox cuando pasemos el puntero del ratón justo encima del formulario. Los datos se cargarán siempre a partir de la fila 2 de la primera columna.

Ahora lo siguiente es incluir un código que ejecute la url una vez la hayamos seleccionado en el listbox, para ello, vamos a utilizar esta macro:

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
enlace = ListBox1.List(ListBox1.ListIndex)
ActiveWorkbook.FollowHyperlink Address:=enlace, NewWindow:=True
End Sub

Como podéis observar, para capturar el valor del Item seleccionado, utilizamos:

enlace = ListBox1.List(ListBox1.ListIndex)

Una vez que tenemos la url almacenada en “enlace” ya podemos ejecutarla en la siguiente línea:

ActiveWorkbook.FollowHyperlink Address:=enlace, NewWindow:=True

Es necesario que hagáis doble click encima del item seleccionado para ejecutar el hipervínculo.

El resultado es este:

SELECCIONAR Y ACTIVAR HIPERVINCULO EN LISTBOX

* En el archivo que os dejo de descarga he incluido una serie de control de errores para que no salten errores a la hora de descargar el archivo desde el correo, o si modificáis el nombre de la hoja o si pincháis en un ítem vacío del listbox.

Con este sencillo post ya podéis subir vuestros enlaces a un userform, sin problemas.

Descarga el archivo de ejemplo pulsando en: SELECCIONAR Y ACTIVAR HIPERVÍNCULO EN UN LISTBOX

 

COMBOBOX DEPENDIENTES EN USERFORM CON SQL

ISuele ser una consulta recurrente en Excel el tema de los combobox dependientes, y es que para algunas aplicaciones o proyectos donde el usuario ha de seleccionar ciertos ítems con dependencia entre ellos, el uso de los combos dependientes es realmente útil.

Se pueden realizar combos dependientes de diversas formas, pero siempre vamos a tener que usar VBA, (al contrario de Access, donde esta posibilidad está incluida en los formularios). Esta vez, además de hacerlo a través de VBA vamos a implementar el ejercicio con ADO y SQL para obtener el mismo resultado.

Este ejemplo voy a realizarlo en un userform, pero también os dejaré el ejemplo en una hoja Excel. Los que leéis esta web habitualmente ya sabéis que si vamos a usar ADO, debemos habilitar en nuestro editor VBA las siguientes referencias:

COMBOBOX DEPENDIENTES EN USERFORM CON SQL

Ahora ya podemos comenzar con el post. Utilizaremos para este ejemplo la base de datos habitual de los grandes almacenes, pero utilizamos los siguientes campos:

COMBOBOX DEPENDIENTES EN USERFORM CON SQL1

Una vez que tenemos estos datos y los campos que vamos a mostrar en los combos (en este caso serán 4), ya podemos ir a VBA e insertar los combobox:

COMBOBOX DEPENDIENTES EN USERFORM CON SQL2

Teniendo en cuenta que el userform lo he denominado “DEPENDIENTES”, debéis pegar en código del userform, la siguiente macro:

Private Sub UserForm_Initialize()
Dim Dataread As ADODB.Recordset, sSQL As String, cnn As ADODB.Connection
'Llamamos a función Abre_Cnn para conectarnos con la base de datos
Set cnn = Abre_Cnn
With DEPENDIENTES
'Realizamos consulta sql para seleccionar con datos agrupados todos los departamentos,
'incluimos is not null, por si en la hoja excel tenemos al final de la base de datos registros en blanco.
sSQL = "SELECT [DATOS$].[SECCION] FROM [DATOS$] WHERE [DATOS$].[SECCION] Is NOT Null " & _
"GROUP BY [DATOS$].[SECCION]"
'limpiamos combobox1
.ComboBox1.Clear
Set Dataread = Recset(sSQL, cnn)
'Con un bucle do cargamos desde el recordset los datos de sección en el combo1
Do Until Dataread.EOF
DEPENDIENTES.ComboBox1.AddItem Dataread("SECCION")
Dataread.MoveNext
Loop
End With
'Desconectamos
Dataread.Close: Set Dataread = Nothing
cnn.Close: Set cnn = Nothing
End Sub

Esta primera macro se inicia cuando el userform se inicializa, básicamente lo que hace es cargar el Combobox1 con los datos de “Sección” y agrupados (registros únicos). Le he incluido un “is not null”, por si al final de la base de datos existiesen registros nulos o en blanco que ocasionen un error de carga.

Pero si os habéis fijado, en la macro he incluido dos “set” llamando a dos funciones: Abre_Cnn y Recset(sSQL, cnn)

Estas dos funciones declaradas publicas, son necesarias para la conexión con la base de datos y con el recordset que grabará información de cada consulta. De esta forma nos evitamos tener que escribir en cada macro el mismo código para conectar y grabar, y simplemente cuando lo necesitemos, invocamos las funciones.

Son estas y también se deben incluir en código del formulario:

Public Function Abre_Cnn() As ADODB.Connection
'Con esta función realizamos la conección con la base de datos
Dim cnn As New ADODB.Connection
Dim bBien As Boolean
bBien = True
On Error GoTo ControlaError
Set cnn = New ADODB.Connection
With cnn
.Provider = "Microsoft.ACE.OLEDB.12.0"
.ConnectionString = "DATA SOURCE= " & ThisWorkbook.Path & "\" & "COMBOBOXUSERFORM.xls"
.Properties("Extended Properties") = "Excel 12.0; HDR=YES"
.Open
End With
SALIR:
Set Abre_Cnn = cnn
Exit Function
ControlaError:
bBien = False
Resume SALIR
End Function

Es importante que tengáis en cuenta que la fuente de datos siempre ha de tener la referencia del archivo donde se encuentran los datos y también podéis especificar una ubicación en otra carpeta, en este caso, es el archivo en uso, es decir, el actual:  “DATA SOURCE= ” & ThisWorkbook.Path & “\” & “COMBOBOXUSERFORM.xls”

Public Function Recset(ByVal sSQL As String, ByRef cnn As ADODB.Connection) As ADODB.Recordset
'Con esta función utilizamos el recordset para grabar la información que cargará cada combo
Dim Dataread As New ADODB.Recordset
Dim bBien As Boolean
bBien = True
On Error GoTo ControlaError
With Dataread
.Source = sSQL
.ActiveConnection = cnn
.CursorLocation = adUseClient
.CursorType = adOpenForwardOnly
.LockType = adLockReadOnly
.Open
End With
SALIR:
Set Recset = Dataread
Exit Function
ControlaError:
bBien = False
Resume SALIR
End Function

Ahora que tenemos cargado el combobox1, debemos ir cargando el resto de combos y que vayan haciendo referencia al combo anterior de forma que sean así dependientes. En el código de cada combo debéis insertar cada una de estas macros:

En el Combobox1:

Private Sub ComboBox1_Change()
Dim Dataread As ADODB.Recordset, sSQL As String, cnn As ADODB.Connection
'Llamamos a función Abre_Cnn para conectarnos con la base de datos
Set cnn = Abre_Cnn
With DEPENDIENTES
vSeccion = .ComboBox1.Value
'realizamos consulta seleccionando los estudios filtrados según la seccion a la que pertenecen
sSQL = "SELECT [DATOS$].[ESTUDIOS] FROM [DATOS$]" & _
"WHERE [DATOS$].[ESTUDIOS] AND [DATOS$].[SECCION]='" & vSeccion & "' " & _
"GROUP BY [DATOS$].[ESTUDIOS]"
Set Dataread = Recset(sSQL, cnn)
'limpiamos resto de combos
.ComboBox2.Clear
.ComboBox3.Clear
.ComboBox4.Clear
'cargamos el resultado de la consulta en el combo2 con el recordset
Do Until Dataread.EOF
.ComboBox2.AddItem Dataread("ESTUDIOS")
Dataread.MoveNext
Loop
End With
Dataread.Close: Set Dataread = Nothing
cnn.Close: Set cnn = Nothing
End Sub

En el Combobox2:

Private Sub ComboBox2_Change()
Dim Dataread As ADODB.Recordset, sSQL As String, cnn As ADODB.Connection
'Llamamos a función Abre_Cnn para conectarnos con la base de datos
Set cnn = Abre_Cnn
With DEPENDIENTES
vSeccion = .ComboBox1.Value
vEstudios = .ComboBox2.Value
'realizamos consulta seleccionando el idioma filtrado según la seccion a la que pertenecen y los estudios que poseen
sSQL = "SELECT [DATOS$].[IDIOMA] FROM [DATOS$] WHERE" & _
"[DATOS$].[IDIOMA] AND [DATOS$].[ESTUDIOS]='" & vEstudios & "' AND [DATOS$].[SECCION]='" & vSeccion & "' " & _
"GROUP BY [DATOS$].[IDIOMA]"
Set Dataread = Recset(sSQL, cnn)
.ComboBox3.Clear
.ComboBox4.Clear
'cargamos el resultado de la consulta en el combo3 con el recordset
Do Until Dataread.EOF
.ComboBox3.AddItem Dataread("IDIOMA")
Dataread.MoveNext
Loop
End With
Dataread.Close: Set Dataread = Nothing
cnn.Close: Set cnn = Nothing
End Sub

En el Combobox3

Private Sub ComboBox3_Change()
Dim Dataread As ADODB.Recordset, sSQL As String, cnn As ADODB.Connection
Set cnn = Abre_Cnn
With DEPENDIENTES
vSeccion = .ComboBox1.Value
vEstudios = .ComboBox2.Value
vIdioma = .ComboBox3.Value
'realizamos consulta seleccionando la persona filtrada según la seccion a la que pertenecen y los estudios que poseen y los idiomas
sSQL = "SELECT [DATOS$].[NOMBRE COMPLETO] FROM [DATOS$]" & _
"WHERE [DATOS$].[IDIOMA] AND [DATOS$].[ESTUDIOS]='" & vEstudios & "' AND [DATOS$].[SECCION]='" & vSeccion & "' AND [DATOS$].[IDIOMA]='" & vIdioma & "' " & _
"GROUP BY [DATOS$].[NOMBRE COMPLETO]"
Set Dataread = Recset(sSQL, cnn)
.ComboBox4.Clear
'cargamos el resultado de la consulta en el combo4 con el recordset
Do Until Dataread.EOF
.ComboBox4.AddItem Dataread("NOMBRE COMPLETO")
Dataread.MoveNext
Loop
End With
Dataread.Close: Set Dataread = Nothing
cnn.Close: Set cnn = Nothing
End Sub

Ni que decir tiene que para adaptar estas macros a vuestros proyectos es necesario que tengáis en cuenta el nombres de los campos y la sintaxis SQL.

Finalmente tendréis los cuatro combos dependientes los unos de los otros, y quedaría así.

COMBOBOX DEPENDIENTES EN USERFORM CON SQL3

Este trabajo también se puede hacer en VBA sin incluir SQL ni ADO, pero me ha parecido muy interesante desarrollarlo con de esta forma.

Adicionalmente os dejo otro archivo pero sin el userform, donde he insertado directamente los combobox en la hoja Excel. Existen algunas modificaciones, la macro se inicializa con un evento “Activate” en la hoja “COMBOS”, para cargar el primer Combobox. La diferencia entre uno y otro archivo es que en uno hacemos referencia al userform y en el otro a la hoja.

Sobre el archivo que contiene los combos en la hoja Excel, os comento que tiene una pequeña macro en ThisWorkbook, para que antes de cerrar no guarde los cambios (esto es porque el evento de activar la hoja y subir los datos a los combos, hacen que aunque no hagáis nada en la hoja, cuando la cerráis siempre os solicite guardar o no cambios.

ThisWorkbook.Saved = True
Application.Quit
End Sub

Si no la necesitáis, solo tenéis que borrarla, a vuestro gusto 🙂

Os dejo los dos archivos y si tenéis dudas, ya sabéis, me lo comentáis.

Ahora, como siempre, os dejo los dos archivos 🙂  espero que os sean de utilidad.

Descarga el archivo de ejemplo pulsando en: COMBOBOX EN USERFORM

Descarga el archivo de ejemplo pulsando en: COMBOBOX EN HOJA