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
Hola
Estoy tratando de replicar este código de VBA, en un ejemplo mío y me da error al pasar al tercer Combobox.
¿Será un problema de formato del código?
¿Puedo enviarte mi hoja de cálculo?
Gracias
Jorge Cabral
Hola Jorge:
Ya te enviado la solución al problema a tu correo.
Saludos!
DA UN ERROR ALABRIR EL ARCHIVO
Hola
ME DA EL MISMO ERROR QUE JORGE
Gracias
Hola Ricardo:
El problema entonces es que estás usando datos con formatos numéricos y debemos trabajar con formato de texto. La solución es: o bien los datos que estás usando los pasas a formato texto, o bien aplicas la función str en aquellas líneas de código que te generan el error.
Por ejemplo: en la cadena de conexión, por ejemplo si el campo estudios fuese numérico debería ser tratado así: STR([DATOS$].[ESTUDIOS]), aplicando la función STR( ).
No olvides también hacer lo mismo con el recordset, para el mismo ejemplo sería así .ComboBox2.AddItem STR(Dataread(«ESTUDIOS»)).
Si por el contrario, el datos que estás tratando es una fecha, debes usar la función CDATE ( ) en lugar de la anterior.
Si ves que sigue sin funcionar, puedes enviarme el archivo (comentado tu necesidad, datos que van en cada combo, etc.).
Saludos.
Hola, primero que todo, excelente código, esta es la única pagina que tiene lo que estaba buscando, muy interesante.
Ahora, mi duda, lo que sucede es que no me resulta jajajaja y no se por que, en mi caso solo quiero relacionar dos combobox, pero en vez de utilizar los datos de excel, están todos en la base de datos sql, quiero relacionar un trabajador con su respectiva comuna, en caso de apretar una comuna, que me habilite los trabajodes que viven en esa comuna, los dos en combobox como muestras en tu ejemplo, y mi error puede ser tanto en la conexión como en la consulta sql, no se si me puedes ayudar 🙂 te lo agradecería muchísimo!
Esta es la consulta de sql que quiero escribir, por si acaso: Select Trabajador.Nombre1 As Trabajador, Comuna.Nombre As Comuna From Trabajador inner join Comuna on Trabajador.ID_Comuna = Comuna.ID_Comuna
Y siguiendo los códigos que tienes arriba, esto es lo que hice, quizás esta desastrosamente mal hecho jajaja
Private Sub Comuna_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 TrabajadoresComuna
vSeccion = .Comuna.Value
‘realizamos consulta seleccionando los estudios filtrados según la seccion a la que pertenecen
sSQL = «Select Trabajador.Nombre1 From Trabajador inner join Comuna on Trabajador.ID_Comuna = Comuna.ID_Comuna»
Set Dataread = Recset(sSQL, cnn)
‘limpiamos resto de combos
.Trabajador.Clear
‘cargamos el resultado de la consulta en el combo2 con el recordset
Do Until Dataread.EOF
.Trabajador.AddItem Dataread(«Nombre1»)
Dataread.MoveNext
Loop
End With
Dataread.Close: Set Dataread = Nothing
cnn.Close: Set cnn = Nothing
End Sub
Private Sub Trabajador_Change()
End Sub
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 TrabajadoresComuna
‘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 Comuna.Nombre From Trabajador inner join Comuna on Trabajador.ID_Comuna = Comuna.ID_Comuna»
‘limpiamos combobox1
.Comuna.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
TrabajadoresComuna.Comuna.AddItem Dataread(«Nombre»)
Dataread.MoveNext
Loop
End With
‘Desconectamos
Dataread.Close: Set Dataread = Nothing
cnn.Close: Set cnn = Nothing
End Sub
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 OLE DB Provider for SQL Server»
.ConnectionString = «Driver={SQL Server};Server=NT_STG_SECASTAN\SQL2012;Database=BD_Excel;Uid=sa;Pwd=Sebastian#»
‘»DATA SOURCE= » & ThisWorkbook.Path & «\» & «Proyecto15_Prueba.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
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
Hola Sebastian:
En estos momentos por temas de salud, no puedo responderte ni analizar tu consulta. Analizar detenidamente el ejemplo e intenta solucionarlo. Saludos
Saludos, esta excelente y bien explicado el código cuando la base de datos esta en excel. Pero en el caso que la base de datos esta en acces?, me sucede que quiero replicar el código pero no logro conectar la base de datos. Mil gracias !!!
Tendría que realizar un post con Access. Pero puedes visitar algunos de los post en los que realizo conexión de Excel con Access y ahí podrías solucionarlo.
Saludos
hola Sebastian,
Excelente post. Quisiera saber cómo se le pasarian datos a los combobox desde una variable con el mismo código, quiero decir, que una alternativa a la propia de selección en los combobox sea la de pasarle los datos desde una o unas variables. En dependientes tengo un caso similar pero nunca consigo que cambien dos combobox, sólo me acepta uno. Lo curioso es que si lo paso con F8, manualmente, lo acepta, pero en automático no…no se más. Muchas gracias