Listas desplegables dependientes – inicialización de celdas con eventos

viernes, febrero 15, 2013

El tema “listas desplegables dependientes” es uno de los más populares entre los lectores de este blog. Las 17 notas bajo esta etiqueta contienen, al día de hoy, 529 comentarios. Si bien los comentarios son una excelente herramienta de comunicación con mis lectores, muchos aportes y soluciones quedan “enterrados” allí y de hecho no están a disposición de los lectores.

En esta nota expongo el tema de la inicialización de celdas que contienen listas desplegables dependientes, tema que ha sido desarrollado en varios comentarios en distintas notas.

Empecemos por exponer el problema. Supongamos un modelo donde tenemos dos celdas con listas desplegables dependientes (en este caso, con la opción Lista de Validación de Datos)



Como podemos ver, al elegir el continente (América del Norte) en la celda C3, la lista desplegable dependiente en la celda C4 nos muestra sólo los países de ese continente. El problema reside en que al elegir otro continente, el país elegido anteriormente queda en la celda hasta que sea reemplazado.

Esto puede generar errores por lo que necesitamos que al cambiar la elección del continente, el contenido de la celda C4 sea borrado.



Esto lo hacemos programando un evento Worksheet_Change para la hoja que contiene las listas desplegables

Private Sub Worksheet_Change(ByVal Target As Range)
    If Union(Target, Range("celContinente")).Address = _
                            Range("celContinente").Address Then
        Range("celPais").ClearContents
    End If

End Sub


Este evento se dispara cuando se produce un cambio en la celda C3; el código elimina el contenido de la celda C4.

Usamos nombres definidos para las celdas para facilitar la lectura del código y para evitar tener que corregirlo si cambiamos la ubicación de las listas desplegables en la hoja


  • celContinente =eleccion!$C$3
  • celPais =eleccion!$C$4


Otra variante del tema, como me consulta uno de mis lectores, es que exista un valor por defecto cuando se elige el continente.

Para que en la celda “país” aparezca el primer país en la lista del continente elegido ponemos este evento Worksheet_Change en la hoja correspondiente

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim strRangeName As String
 
    If Union(Target, Range("celContinente")).Address = _
                            Range("celContinente").Address Then
        If Len(Range("celContinente")) = 0 Then
            Range("celPais").ClearContents
            Exit Sub
        End If
     
        Call valDefault
    End If

End Sub


y esta macro en un módulo común del editor Vb

Sub valDefault()
    Dim n As Name
    Dim strRangeName As String
 
    strRangeName = WorksheetFunction.Substitute(Range("celContinente"), " ", "_")

    Range("celPais") = WorksheetFunction.Index(Range(strRangeName), 1)
     
End Sub


En esta macro es necesaria ya que al crear los nombres que se refieren a los rangos con las ciudades, los espacion entre las palabras han sido reemplazados por “_” (por ejemplo, América del Sur se transforma en America_del_Sur). Lo mismo hemos hecho en la regla de validación de datos, como ya hemos explicado en notas anteriores



En este modelo, al borrar el contenido de la celda C3 (continente), se elimina automáticamente el valor de la celda C4 (país); al elegir un continente, aparece el primer país en la lista correspondiente; el país puede ser cambiado luego.



Para que aparezca algún otro país como valor por defecto, la posibilidad más obvia es ponerlo en el primer lugar de la lista.

El cuaderno con el ejemplo puede descargarse aquí.

Todo sobre listas desplegables en Excel, técnicas avanzadas y descarga gratuita de ejemplos  en la Caja de Herramientas Excel - Listas Desplegables de JLD. Ver la nota o ir a la página de descarga de la guía.

19 comments:

Anónimo,  15 febrero, 2013 17:19  

Muchas gracias Jorge,

Lo que parecía un problema sencillo veo que es más complicado de lo que me imaginaba.

De nuevo gracias por tu ayuda, rápida respuesta y paciencia en respondernos a todos.

Mi gran admiración

Anónimo,  29 mayo, 2013 11:41  

Buenas de nuevo,
con respecto a este ejemplo tengo otra duda.
En el caso de que dentro de la misma hoja tenga que rellenar en varios sitios el continente y país, como puedo hacer para aplicar el código a más celdas?
Es decir, por ejemplo, tengo que rellenar en la celda C3, C6 y C9 el continente y quiero que las celdas C4, C7 y C10 muestren el pais con el mismo criterio que se hace en el ejemplo (inicialización de la celda y index a primera posición)


Jorge Dunkelman 30 mayo, 2013 08:33  

¿Las celdas C3, C6 y C9 son independientes, es decir, muestran valores distintos?

Anónimo,  30 mayo, 2013 15:31  

Buenas de nuevo:

Las celdas C3, C6 y C9 contienen los mismos valores. Digamos que lo que pretendo es repetir tú ejemplo tres veces dentro de la misma hoja.

Jorge Dunkelman 30 mayo, 2013 17:34  

¿No te bastaría con crear una referencia a las celdas? La celda que debe mostrar el mismo valor de C3 tendría la fórmula =C3.

Anónimo,  30 mayo, 2013 17:43  

Jorge,
creo que no me he explicado correctamente.
Digamos que quiero que al igual que al seleccionar Asia en la celda C3, me aparezca Afganistan en la celda C4, si selecciono en la celda C6 cualquier otro continente me aparezca en la celda C7 el primer país de su lista. Vamos repetir la formulación de las celdas C3 y C4 en más sitios de la misma página

Jorge Dunkelman 30 mayo, 2013 18:54  

Ahora está claro. Hay que agregar en el código del evento una parte para cada celda. Es decir, repetir el código dentro del evento, una vez para cada celda.

Anónimo,  31 mayo, 2013 09:21  

Jorge,
muchas gracias por tus prontas repuestas. Mi intención era ver si lo podía hacer de forma genérica ya que tengo que repetirlo unas 100 veces y no quería ir celda por celda copiando los valores y modificándolos.
Gracias.

Jorge Dunkelman 31 mayo, 2013 12:05  

Se puede, pero no es trivial y no puedo exponerlo en el marco de un comentario. La técnica básica es la que muestro en esta nota, pero aplicada a comboboxes dependientes.

Jorge Dunkelman 31 mayo, 2013 12:07  

Siguiendo con mi comentario anterior, también vale la considerar el diseño de tu modelo. Me refiero a cambios de diseño que te puedan ahorrar el tener que repetir las listas.

Sergio Berrocal 24 junio, 2015 17:57  

Jorge, muchas gracias por este post. Me ha sido de mucha utilidad! Un saludo fraterno desde Perú

Anónimo,  26 noviembre, 2015 04:12  

Buen dia Jorge, Me encuentro con la misma duda del señor Anonimo, que tiene una lista de 100 filas donde usamos las validaciones dependientes. No se me ocurre una macro que detecte en que fila hice el cambio en continente, y que solamente en es fila me resetee el pais y la provincia. Desde ya muchisimas gracias. Pablo Avegno de Argentina

Jorge Dunkelman 26 noviembre, 2015 07:02  

Tendría que ver tu hoja para determinar el código en forma precisa. En términos generales, si todas las celdas "continente" se encuentran en la misma columna, podríamos determina en qué celda de esa columna se produjo un cambio (evento Worksheet_Change), determinar la fila de la celda y así resetear las celdas correspondientes (suponiendo que están en la misma fila).

Anónimo,  13 febrero, 2016 18:36  

Buenas tardes,
continuando con este tema de las listas dependientes, les hago una consulta de un problema con el que llevo varios días peleando sin éxito.
Tengo una hoja Excel a modo de formulario con muchas listas dependientes. He aplicado el código ClearContents en muchas de ellas para borrarlas cuando haya cambios en la selección de otras listas.
El hecho es que tengo una lista que al introductor de datos en el formulario le podría interesar modificar una vez ya completado el formulario, por ver los resultados que obtendría con esa otra selección manteniendo el resto de celdas. La modificación de la selección en esa celda implicaría únicamente chequear el contenido de otra celda anterior. Podría ocurrir que tuviera que modificar dicha celda anterior o no. ¿Como podría ejecutar esta acción? ¿Como podría borrar el contenido de la celda dependiente, pero asegurando que no me lo borre cuando al formulario está vacío (en la primera introducción de datos)? ¿Podría sino dar un mensaje para recordar chequear el valor de esa celda, pero que únicamente aparezca si se modifica la celda en cuestión estando las demás completas (no en la primera introducción de datos)?

Gracias de antemano

Anónimo,  15 febrero, 2016 18:16  

Buenas tardes,
escribo para realizar una consulta sobre listas dependientes.
He creado un formulario con varias listas dependientes. Al variar ciertos valores del formulario, borro algunas de las celdas (listas) mediante la instrucción ClearContents.
El caso es que puede ocurrir, que tras finalizar la introducción de datos en el formulario, interese modificar una de las listas seleccionadas para ver la variación de resultados que se produce.
Al modificar esta lista, interesaría borrar, o al menos chequear, el contenido de otra anterior. Cuando utilizo la instrucción ClearContents, elimina el contenido de la segunda celda incluso durante la primera introducción de datos, que no me interesa, sino únicamente si tras finalizar toda la introducción de datos, varío la selección de la primera celda.
¿Como podría solucionarlo? Es decir, ¿cómo podría mantener el contenido de la segunda celda (que he introducido antes) durante la primera introducción de datos, y borrarlo o avisar de que hay que chequearlo si, tras acabar la introducción de datos en el formulario, modifico el contenido de una celda?.

Gracias de antemano por su ayuda.

Jorge Dunkelman 16 febrero, 2016 18:41  

Hola, por favor fijate en lo que pongo en el enlace Ayuda (en la parte superior del blog) y ponete en contacto conmigo por mail privado.

Anónimo,  01 diciembre, 2016 02:42  

Si quiero un listado de 10 personas las cuales puedan seleccionar continente y pais, es decir copiar las listas desplegables 10 veces hacia abajo, para que 10 personas puedan seleccionar...como quedaria el codigo para borrar las celdas dependiendo de los cambios que elija cada persona, pero sin que afecte a las demas celdas.

Jorge Dunkelman 03 diciembre, 2016 20:17  

Tienes que repetir el mismo evento (el primer evento) refiriéndote en cada uno a las celdas correspondientes. En mi ejemplo uso nombres para referirme a las celdas, pero puedes usar directamente las referencias (por ejempll, $C$3).

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP