Cómo crear una lista desplegable de selección múltiple en Excel

El artículo muestra cómo crear un menú desplegable de Excel que permita a los usuarios seleccionar varios elementos con o sin duplicados.

Excel ha recorrido un largo camino desde sus inicios e introduce cada vez más funciones útiles con cada nueva versión. En Excel 365, agregaron la capacidad de buscar dentro de listas de validación de datos, lo que supone un gran ahorro de tiempo cuando se trabaja con grandes conjuntos de datos. Sin embargo, incluso con esta nueva opción, Excel listo para usar solo permite seleccionar un elemento de una lista predefinida de opciones. Pero no temas, porque hay una solución. Al usar VBA, puede crear listas desplegables con múltiples selecciones. Con la capacidad de evitar duplicados y eliminar elementos incorrectos, esta función puede agilizar la entrada de datos y mejorar la precisión en sus hojas de cálculo de Excel.

>Cómo hacer que Excel sea desplegable con múltiples selecciones

Crear una lista desplegable de selección múltiple en Excel es un proceso de dos partes:

También funciona en orden inverso 🙂

Crear una lista desplegable normal

Para insertar una lista desplegable en Excel, utiliza la función Validación de datos. Los pasos varían ligeramente dependiendo de si los elementos de origen están en un rango normal, un rango con nombre o una tabla de Excel.

Según mi experiencia, la mejor opción es crear una lista de validación de datos a partir de una tabla. Como las tablas de Excel son dinámicas por naturaleza, un menú desplegable relacionado se expandirá o contraerá automáticamente a medida que agregue o elimine elementos de la tabla.

Para este ejemplo, usaremos la tabla con el nombre simple Tabla 1 que reside en A2:A25 en la captura de pantalla siguiente. Para hacer una lista de selección a partir de esta tabla, los pasos son:

>El resultado será una lista desplegable expandible y actualizable automáticamente que solo permite seleccionando un elemento.

Para obtener más información, consulte Cómo crear una lista de validación de datos de Excel.

Inserte código VBA para permitir selecciones múltiples

Esta es la parte central del proceso que hace la magia. Para convertir una lista de selección única normal en un menú desplegable de selección múltiple, debe insertar uno de estos códigos en la parte posterior de su hoja de trabajo de destino:

Para agregar código VBA a su hoja de trabajo, siga estos pasos:

>¡Eso es todo! Cuando regrese a la hoja de trabajo, su lista desplegable le permitirá seleccionar varios elementos:

>Código VBA para seleccionar varios elementos en la lista desplegable

A continuación se muestra el código para hacer una lista de validación de datos que permita seleccionar múltiples elementos, incluidas selecciones repetidas:

Código VBA para seleccionar varios elementos en el menú desplegable de Excel

Opción Explícito
Privado Sub Hoja de cálculo_cambiar(porval Destino Como Rango)
Oscuro Tipo Delimitador Como Cadena
Oscuro rngDesplegable Como Rango
Oscuro valor antiguo Como Cadena
Oscuro nuevo valor Como Cadena
Tipo Delimitador = «, «
Si Destino.Recuento > 1 Entonces Salida Sub

En Error Reanudar Próximo
Colocar rngDropdown = Celdas.SpecialCells(xlCellTypeAllValidation)
En Error Ir a error de salida

Si rngDesplegable Es Nada Entonces Ir a error de salida

Si Intersección (Destino, rngDropdown) Es Nada Entonces
‘hacer nada
Demás
Aplicación.EnableEvents = FALSO
newValue = Destino.Valor Aplicación.Deshacer oldValue = Destino.Valor Destino.Value = nuevoValor
Si valorantiguo = «» Entonces
‘hacer nada
Demás
Si nuevoValor = «» Entonces
‘hacer nada
Demás
Destino.Value = oldValue & DelimiterType & newValue
‘agrega nuevo valor con delimitador
Fin Si
Fin Si
Fin Si

exitError: Aplicación.EnableEvents = Verdadero
Fin Sub

Privado Sub Hoja de cálculo_SelecciónCambio(porval Objetivo Como Rango)

Fin Sub

Cómo funciona este código:

• El código permite múltiples selecciones en todas las listas desplegables en una hoja determinada. No es necesario especificar la celda de destino o la referencia de rango en el código.
• El código es hoja de trabajo específicaasí que asegúrese de agregarlo a cada hoja donde desee permitir selecciones múltiples en las listas desplegables.
• Este código permite repeticiónes decir, seleccionar el mismo elemento varias veces.
• Los elementos seleccionados se separan con una coma y un espacio. Para cambiar el delimitador, reemplace «, » con el carácter que desee en DelimiterType = «, » (línea 7 en el código anterior).

Menú desplegable de selección múltiple de Excel sin duplicados

Al seleccionar de una lista grande, los usuarios a veces pueden elegir el mismo elemento más de una vez sin darse cuenta. El siguiente código resuelve el problema de los duplicados en una lista desplegable de validación de datos de selección múltiple. Permite a los usuarios elegir un artículo en particular sólo una vez. Si intenta seleccionar el mismo elemento nuevamente, no sucederá nada. Muy bien, ¿verdad?

Código VBA para crear un menú desplegable de selección múltiple sin repeticiones

Opción explícita
Sub privado Hoja de cálculo_cambiar(porval Destino Como Rango)
Oscuro rngDesplegable Como Rango
Oscuro valor antiguo Como Cadena
Oscuro nuevo valor Como Cadena
Oscuro Tipo Delimitador Como Cadena
Tipo Delimitador = «, «

Si Destino.Recuento > 1 Entonces Salida Sub

En Error Reanudar Próximo
Colocar rngDropdown = Celdas.SpecialCells(xlCellTypeAllValidation)
En Error Ir a error de salida

Si rngDesplegable Es Nada Entonces Ir a error de salida

Si Intersección (Destino, rngDropdown) Es Nada Entonces
‘hacer nada
Demás
Aplicación.EnableEvents = FALSO
newValue = Destino.Valor Aplicación.Deshacer oldValue = Destino.Valor Destino.Value = nuevoValor
Si valorantiguo <> «» Entonces
Si nuevoValor <> «» Entonces
Si valorantiguo = valornuevo O _ InStr(1, valor antiguo, tipo delimitador y valor nuevo) O _ InStr(1, oldValue, newValue y Reemplazar(DelimiterType, » «, «»)) Entonces
Destino.Valor = valorantiguo
Demás
Destino.Value = oldValue & DelimiterType & newValue
Fin Si
Fin Si
Fin Si
Fin Si

exitError: Aplicación.EnableEvents = Verdadero
Fin Sub

Privado Sub Hoja de cálculo_SelecciónCambio(porval Objetivo Como Rango)

Fin Sub

Menú desplegable de selección múltiple con eliminación de elementos

Cuando los usuarios necesitan seleccionar múltiples opciones pero pueden cometer errores o cambiar de opinión durante el proceso de selección, un menú desplegable de selección múltiple que permite eliminar elementos incorrectos puede ser un salvavidas.

Considere un escenario en el que necesita asignar varias tareas a los miembros del equipo mediante una lista desplegable. Con la funcionalidad predeterminada de Excel, la única forma de eliminar una tarea asignada incorrectamente es borrando todo el contenido de la celda y comenzando de nuevo. Con la capacidad de eliminar elementos individuales de la selección, el equipo puede modificar fácilmente las asignaciones de tareas, sin confusión ni errores.

El siguiente código implementa la funcionalidad de eliminación de elementos de una manera simple e intuitiva: el primer clic en un elemento lo agrega a la selección y un segundo clic en el mismo elemento lo elimina de la selección.

Código VBA para crear un menú desplegable de selección múltiple que permite eliminar elementos seleccionados

Opción Explícito
Privado Sub Hoja de cálculo_cambiar(porval Destino Como Rango)
Oscuro rngDesplegable Como Rango
Oscuro valor antiguo Como Cadena
Oscuro nuevo valor Como Cadena
Oscuro Tipo Delimitador Como Cadena
Tipo Delimitador = «, «
Oscuro DelimitadorRecuento Como Entero
Oscuro Tipo de objetivo Como Entero
Oscuro i Como Entero
Oscuro arr() Como Cadena

Si Destino.Recuento > 1 Entonces Salida Sub
En Error Reanudar Próximo

Colocar rngDropdown = Celdas.SpecialCells(xlCellTypeAllValidation)
En Error Ir a error de salida

Si rngDesplegable Es Nada Entonces Ir a exitError TargetType = 0 TargetType = Destino.Validación.Tipo
Si Tipo de objetivo = 3 Entonces ‘su tipo de validación es «lista»
Aplicación.ScreenUpdating = FALSO
Aplicación.EnableEvents = FALSO
newValue = Destino.Valor Aplicación.Deshacer oldValue = Destino.Valor Destino.Value = nuevoValor
Si valorantiguo <> «» Entonces
Si nuevoValor <> «» Entonces
Si valorantiguo = valornuevo O valorantiguo = nuevoValor y Reemplazar(DelimiterType, » «, «») O valorantiguo = valor nuevo y tipo de delimitador Entonces ‘deja el valor si solo hay uno en la lista
valorantiguo = Reemplazar(Valorantiguo, TipoDelimitador, «») valorantiguo = Reemplazar(Valorantiguo, Reemplazar(TipoDelimitador, » «, «»), «») Destino.Valor = valorantiguo
De lo contrario si InStr(1, valor antiguo, tipo de delimitador y valor nuevo) O InStr(1, valor antiguo, » » y valor nuevo y tipo de delimitador)Entonces
arr = Dividir(valorantiguo, TipoDelimitador)
Si No IsError(Application.Match(newValue, arr, 0)) = 0 Entonces
Destino.Value = oldValue & DelimiterType & newValue
Demás: Destino.Valor = «»
Para yo = 0 A UBound(llegar)
Si arr(i) <> nuevoValor Entonces
Destino.Valor = Destino.Valor & arr(i) & DelimiterType
Fin Si
Próximo i Destino.Valor = Izquierda(Destino.Valor, Len(Destino.Valor) – Len(DelimiterType))
Fin Si
De lo contrario si InStr(1, oldValue, newValue y Reemplazar(DelimiterType, » «, «»)) Entonces
valorantiguo = Reemplazar(Valorantiguo,Valornuevo, «») Destino.Valor = valorantiguo
Demás
Destino.Value = oldValue & DelimiterType & newValue
Fin Si
Destino.Valor = Reemplazar (Destino.Valor, Reemplazar (DelimiterType, » «, «») y Reemplazar (Tipo Delimitador, » «, «»), Reemplazar (Tipo Delimitador, » «, «»)) ‘elimina comas y espacios adicionales
Destino.Valor = Reemplazar (Destino.Valor, Tipo Delimitador y Reemplazar (Tipo Delimitador, » «, «»), Reemplazar (Tipo Delimitador, » «, «»))
Si Destino.Valor <> «» Entonces
Si Derecha(Destino.Valor, 2) = Tipo Delimitador Entonces ‘elimina el delimitador al final
Destino.Valor = Izquierda(Destino.Valor, Len(Destino.Valor) – 2)
Fin Si
Fin Si
Si InStr(1, Destino.Valor, Tipo Delimitador) = 1 Entonces ‘elimina el delimitador como primeros caracteres
Destino.Valor = Reemplazar (Destino.Valor, Tipo Delimitador, «»1, 1)
Fin Si
Si InStr(1, Destino.Valor, Reemplazar(TipoDelimitador, » «, «»)) = 1 Entonces
Destino.Valor = Reemplazar (Destino.Valor, Reemplazar (DelimiterType, » «, «»), «»1, 1)
Fin Si
CuentaDelimitador = 0
Para yo = 1 A Len(Destino.Valor)
Si InStr(i, Destino.Valor, Reemplazar(DelimiterType, » «, «»)) Entonces
ConteoDelimitador = ConteoDelimitador + 1
Fin Si
Próximo i
Si CuentaDelimitador = 1 Entonces ‘elimina el delimitador si es el último carácter
Destino.Valor = Reemplazar (Destino.Valor, Tipo Delimitador, «») Destino.Valor = Reemplazar(Destino.Valor, Reemplazar(DelimiterType, » «, «»), «»)
Fin Si
Fin Si
Fin Si
Aplicación.EnableEvents = Verdadero
Aplicación.ScreenUpdating = Verdadero
Fin Si

exitError: Aplicación.EnableEvents = Verdadero
Fin Sub

Privado Sub Hoja de cálculo_SelecciónCambio(porval Objetivo Como Rango)

Fin Sub

La siguiente demostración destaca cómo funciona el menú desplegable de selección múltiple con funcionalidad de eliminación en Excel. Los usuarios pueden seleccionar múltiples opciones de la lista de validación de datos y realizar ajustes sobre la marcha. ¡Un enfoque simplificado y eficaz para gestionar selecciones!

>Cómo hacer un menú desplegable de selección múltiple con delimitador personalizado

El carácter que separa los elementos de la selección se establece en el Tipo Delimitador parámetro. En todos los códigos, el valor predeterminado de este parámetro es «, » (una coma y un espacio) y se ubica en la línea 7. Para usar un separador diferente, puede reemplazar «, » con el carácter deseado. Por ejemplo:

• Para separar los elementos seleccionados con un espacio, utilice DelimiterType = » «.
• Para separar con punto y coma, utilice DelimiterType = «; » o DelimiterType = «;» (con o sin espacio, respectivamente).
• Para separar con una barra vertical, use DelimiterType = » | «.

Por ejemplo, si cambia el delimitador a una barra vertical, su lista de selección múltiple tendrá el siguiente aspecto:

>Cómo crear un menú desplegable con múltiples selecciones en líneas separadas

Para obtener cada selección en una línea separada en la misma celda, configure Tipo Delimitador a Vbcrlf. En VBA, es una constante para los caracteres de retorno de carro y avance de línea.

Más precisamente, cambia esta línea de código:

DelimiterType = «,»

a este:

DelimiterType = vbCrLf

Como resultado, cada elemento que seleccione de la lista desplegable aparecerá en una nueva línea:

>Cómo crear un menú desplegable de selección múltiple para columnas, filas y celdas específicas

Todos los códigos descritos en este tutorial funcionan en una hoja completa. Sin embargo, puede modificar fácilmente cualquiera de los códigos, de modo que solo se aplique a celdas, filas o columnas específicas, según sea necesario. Para esto, busque esta línea de código:

If rngDropdown Is Nothing Then GoTo exitError

Agregar inmediatamente después él, agregue una nueva línea que especifique dónde permitir selecciones múltiples, como se explica en los ejemplos siguientes.

Menú desplegable de selección múltiple para columnas específicas

Para permitir la selección de varios elementos en un cierta columnaagrega este código:

If Not Destination.Column = 4 Then GoTo exitError

Donde «4» es el número de la columna de destino. En este caso, el menú desplegable de selección múltiple solo estará habilitado en la columna D. En todas las demás columnas, las listas desplegables se limitarán a una única selección.

Apuntar varias columnasespecifique sus números usando este código:

If Destination.Column <> 4 And Destination.Column <> 6 Then GoTo exitError

En este caso, el menú desplegable de selección múltiple estará disponible en las columnas D (4) y F (6).

Menú desplegable de selección múltiple para ciertas filas

Para insertar varios menús desplegables en un fila específicausa este código:

If Not Destination.Row = 3 Then GoTo exitError

En este ejemplo, reemplace «3» con el número de fila donde desea habilitar los menús desplegables de selección múltiple.

Apuntar varias filasEl código es el siguiente:

If Destination.Row <> 3 And Destination.Row <> 5 Then GoTo exitError

Donde «3» y «5» son las filas donde se permite seleccionar varios elementos.

Múltiples selecciones en celdas específicas.

Para habilitar múltiples selecciones en células particularesagregue una de las siguientes líneas de código.

Para unicelular:

If Not Destination.Address = «$D$3» Then GoTo exitError

Para múltiples celdas:

If Destination.Address <> «$D$3» And Destination.Address <> «$F$6» Then GoTo exitError

Sólo recuerda reemplazar «$D$3» y «$F$6» con las direcciones de tus celdas objetivo.

Cómo habilitar la funcionalidad de selección múltiple en una hoja protegida

Para habilitar una función desplegable de selección múltiple en una hoja de trabajo protegida, simplemente inserte el siguiente código en la hoja de trabajo donde agregó el código principal.

Código VBA para permitir múltiples selecciones en la hoja protegida

Privado Sub Hoja de cálculo_SelecciónCambio(porval Objetivo Como Rango) ActiveSheet.Desproteger contraseña:=«contraseña»
En Error Ir a error de salida2

Si Validación.de.objetivo.Tipo = 3 Entonces
Demás
ActiveSheet.Proteger contraseña:=«contraseña»
Fin Si

Hecho:
Salida Sub

exitError2: Contraseña ActiveSheet.Protect:=«contraseña»
Fin Sub

Antes de agregar este código a su hoja de trabajo, recuerde reemplazar «contraseña» con la contraseña real que utilizó para proteger la hoja. Y este es el único cambio que hay que hacer. El código detectará automáticamente la presencia de una lista desplegable en una celda determinada y permitirá editar esa celda. En todas las demás celdas, la edición permanecerá restringida.

Ahí lo tiene: una lista desplegable de Excel con múltiples selecciones. ¡Agregar esta increíble característica a tus hojas de cálculo aumentará la precisión de tu entrada de datos y te ayudará a realizar tu trabajo más rápido!

Libro de práctica para descargar.

Menú desplegable de selección múltiple: ejemplos (archivo .xlsm)

Usted también podría estar interesado en