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:

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:

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:

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í.

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