sábado, 5 de mayo de 2012

VBA: macro para obtener rango de celdas asociado a una selección.

Tiempo atrás expliqué como mediante el uso de un objeto Collection en una macro de Excel conseguíamos un listado de valores únicos de una forma muy sencilla (ver entrada).
Esta entrada nos servirá para responder la cuestión planteada, en la que se pide extraer los valores asociados a un dato previamente seleccionado; y veremos como podemos combinar diferentes Eventos sobre un mismo control(ActiveX) para conseguir diferentes efectos, en este ejemplo, usaremos el evento GotFocus y el evento Change.
Esta es la cuestión:

...La siguiente lista me muestra que en la columna A tengo un dato y en la columna B me muestra todos los valores asociados a la celda de la columna A. La pregunta es cómo puedo buscar, utilizando el dato de la columna A, los datos de asociados de la columna B...

Veamos la imagen de la tabla origen:

VBA: macro para obtener rango de celdas asociado a una selección.


Se puede observar en la imagen que en la columna A (campo 'Código') existen varios valores repetidos (ax, bx, cx, dx, etc.), y que asociados a estos, en la columna B (campo 'Descripción') les corresponden diferentes valores; para ax le toca ax-01, ax-02, ax-03 y ax-04; y así sucesivamente.
La idea es ser capaz de seleccionar un elemento único del campo 'Código' para de manera inmediata se despliegue en la Hoja de cálculo los valores asociados del campo 'Descripción'.

Para trabajar de manera más cómoda hemos convertido en Tabla el origen de datos, asignando además Nombres definidos a ambos campos:
Código =Hoja1!$A$2:$A$12
Descripción =Hoja1!$B$2:$B$12

El siguiente paso es sencillo, insertaremos desde la Ficha Programador > Controles > Insertar > Control ActiveX > Cuadro Combinado, es decir, insertaremos un control ComboBox en la hoja de cálculo, al que asignaremos para empezar un evento GotFocus, que habilitará el listado de valores únicos como elementos (items) del ComboBox al presionarlo.
Al hacer click sobre el Cuadro Combinado recien insertado se abre una ventana de código (en la Hoja1) en la que insertaremos lo siguiente:

Private Sub comboBox1_gotfocus()

Dim celda As Object
Dim i As Integer

ComboBox1.Clear
Set unicos = New Collection

For Each celda In Range("Código")
    On Error Resume Next
    unicos.Add celda.Value, CStr(celda.Value)
    On Error GoTo 0
Next celda

For i = 1 To unicos.Count
    ComboBox1.AddItem unicos(i)
Next i

End Sub


Con esto hemos conseguido rellenar el ComboBox con los elementos únicos que componen en campo 'Código', como vemos en la imagen:

VBA: macro para obtener rango de celdas asociado a una selección.


De momento el ComboBox no hace nada más, pero si añadimos a continuación un nuevo evento Change sobre él mismo, conseguiremos nuestro objetivo, que es listar los valores del campo 'Descripción' asociados a nuestra selección.
Asi que a continuación del código anterior escribimos:

Private Sub comboBox1_change()
Dim seleccionado As Variant
seleccionado = ComboBox1.Value
x = 0
Range(Range("E4"), Range("E4").End(xlDown)).Clear
Range("E4").Select
Dim celda2 As Object
For Each celda2 In Range("Código")
    If celda2.Value = seleccionado Then
    ActiveCell.Offset(x, 0).Value = celda2.Offset(0, 1)
    x = x + 1
    End If
Next celda2
End Sub


Con este segundo código, con el evento Change, indicamos que al seleccionar algún elemento del Cuadro combinado (ComboBox) liste, a partir de la celda E4 de la hoja las diferentes 'Descripciones' asociadas.
Podemos ver el resultado en la imagen:

VBA: macro para obtener rango de celdas asociado a una selección.

23 comentarios:

  1. Excelente aportacion Ismael!
    Ésta, permite tener dos combos dependientes, y hacerlo por VBA. Hay muchos ejemplos de dependientes, pero todos son directamente, no por VBA.
    En mi caso, me encuentro con un problema que tal vez me puedas hechar una mano o iluminar por donde ir.

    Por ahora unicamente he aplicado la función de rellenar el combo. Y me encuentro con ún error 1004, en el Rango("Codigo"). No lo encuentra.

    El Combo lo tengo en una hoja, el rango de celdas en otra hoja distinta. El codgo se ejecuta en la hoja que tiene el combo. No consigo que reconozca el nombre del rango. Las celdas tienen formato "General"... y por mas que he probado con dobles comillas, comillas simples, con un variable.. nada.
    Si tuvieses alguna idea al respecto te lo agradeceria.
    Un saludo,
    Oscar

    ResponderEliminar
    Respuestas
    1. Hola Oscar,
      gracias por tus palabras.

      Si estás empleando un nombre definido, yo empezaría por asegurarme que en su definición, el nombre tiene como ámbito todo el Libro (y no sólo la hoja en que esté).
      Luego, si el combobox lo tienes en una hoja diferente, quizás deberías (por asegurarte) añadir al código siempre la expresión
      Sheets("nombrehoja").Range(....
      así dirijes cada cosa a su sitio. No se me ocurre ahora mismo otra posibilidad.
      Slds

      Eliminar
  2. Hola Ismael,
    Pues fue la segunda opción, ya que el nombre estaba bien definido para todo el libro. Que facil!
    La verdad, es que no he tenido nunca al 100% claro el funcionamiento de estos redireccionamientos de rangos, porque en este caso esta definido para todo el libro, no?... pero bueno en fin!

    Muchas gracias, por la ayuda!
    Y a continuar el blog, que ayuda a mas de un visitante silencioso!
    Saludos!

    ResponderEliminar
    Respuestas
    1. Perfecto!!
      en general cuanto más completos seamos (yo el primero) dirijiendo expresiones mejor...
      incluso si trabajaramos con diferentes libros, serái conveniente empezar con WorkBook("xxx.xlsx").Sheets("hoja").range(...

      pero es más cómodo dejarlo en manos del Editor... aunque a veces pasan estas cosas.
      Slds

      Eliminar
  3. Muchas gracias por tus consejos y trucos....
    Me interesa una variación de este ejemplo, verás yo tengo un listado de articulos cada uno con su código, y luego cada articulo tiene unos cinco precios diferentes (columna 1= codigo; columna 2= descripcion; columna 3= precio 1; columna 4= precio 2.....), mi pregunta es qué modificación habria que hacerle al código del ComboBox del articulo para que me diese el listado de los precios al seleccionar el código de un producto?

    ResponderEliminar
    Respuestas
    1. Hola,
      no veo para que necesitas una macro para conseguir lo que quieres; ya que sería suficiente incluir una Celda validada, por ejemplo en H1, sobre el campo 'código'; y luego en I1, J1, etc introducir una fórmula BUSCARV, con valor buscado H1 y matriz la tabla con el listado de artículos.

      Pero si quieres aplicar la macro, por el motivo que sea, la modificación la tendrías que hacer en:
      If celda2.Value = seleccionado Then
      ActiveCell.Offset(x, 0).Value = celda2.Offset(0, 1)
      ActiveCell.Offset(x, 1).Value = celda2.Offset(0, 2)
      ActiveCell.Offset(x, 2).Value = celda2.Offset(0, 3)
      'etc
      x = x + 1
      End If

      Espero te sirva.
      Slds

      Eliminar
    2. Gracias por tu pronta respuesta...aunque he pensado algo que creo me seria mas util, espero me puedas ayudar porque aun soy un poco novato con el excel. Verás como te comenté tengo un listado de articulos con su código correspondiente y cada articulo tiene más de un precio. Qué solución me podrias dar para que cuando introduzca en una celda un código pueda obtener en otra celda un listado con sus diferentes precios para poder elegir uno de ellos, a modo de poder hacer una especie de factura, para que me entiendas. Gracias por todo de antemano, un saludo.

      Eliminar
    3. Hola,
      la solución pasaría por emplear validaciones de celdas, sobre rangos con Nombres definidos.
      Puedes ver un ejemplo parecido en
      http://excelforo.blogspot.com.es/2010/04/validacion-de-celdas-anidadas-y.html
      Slds

      Eliminar
    4. Bueno, la verdad es que me cuesta un poco seguir los conceptos del ejemplo, serias tan amable de hacer algún ejemplo que se parezca más a esto?...verás, como te comenté tengo un listado de articulos, cada uno con su código, y cada articulo con cinco precios diferentes, realicé una validación de celdas tanto de los códigos como de los articulos, y de los precios por columnas(aunque no sé si realmente me sirve asi), sería un estilo como esto...
      CODIGO ARTICULO PRECIO1 PRECIO2 PRECIO3 PRECIO4 PRECIO5
      1 LECHE 0.25 0.27 0.30 0.32 0.35
      2 .....
      Y lo que necesito, si es posible, es crear en una celda algún desplegable en el cual me dé la opción de elegir los diferentes precios de un producto cuando introduzca el código del mismo en otra celda. Tengo varias hojas, una con los articulos con su codigo y sus precios, otra para hacer las facturas (que es donde necesito esto), y otra para los clientes.
      Espero me puedas ayudar, te lo agradeceria enormemente. Un saludo y gracias de antemano.

      Eliminar
    5. Hola,
      enviame el fichero a
      excelforo@gmail.com
      Slds

      Eliminar
    6. Muchisimas gracias por todo Ismael, eres un máquina. Pronto tendrás más noticias mias (jeje), porque me gustaria hacer el libro un poco más completo, sobre todo para generar informes de clientes y productos o productos y lotes por fechas, etc...
      Lo dicho, mil gracias, saludos maestro.

      Eliminar
    7. Gracias a ti Victor,
      entiendo que la solución te ha servido...

      Era sencillo siguiendo las indicaciones
      ;-)

      Un cordial saludo

      Eliminar
  4. Hola Ismael,
    todo esta bien se entiende, pero la lista de valores unicos cargados no son ordenados, ya sea si fue un nuero en forma ascendente.
    como podria ordenarlos?
    en mi caso tengo un rango con años que van desde 2008 al 2013 son repetidos el cual me identifica el año de muerte y son valores repetidos.
    con tu macros me queda algo asi.
    2009
    2013
    2010
    2008
    2011

    Gracias

    ResponderEliminar
    Respuestas
    1. Hola, hacerlo mediante macros es algo complejo, ya que requiere trabajar con Arrays y aplicarle un método de ordenación tipo burbuja
      http://excelforo.blogspot.com.es/2012/12/vba-algoritmo-de-ordenacion-tipo.html

      Te diría que lo más simple es que ordenes tu base de datos en ascendente por el campo 'Años'...

      Intentaré, en cuanto pueda, subir una entrada en el blog explicando cómo realizar esta ordenación.
      Slds cordiales

      Eliminar
  5. Que tal Ismael, mi duda es la siguiente, tengo un Formulario que estoy armando para cargar datos en una hora de excel, y en ese Formulario le agrego con Visual un Combo Box y quiero cargar ahi datos de una hoja de calculo. Agregando el Combo Box solo puedo hacerlo, pero en visual agregando como parte de un formulario me esta costando hacer referencia a un rango de celdas para que Visual lo cargue al Combo Box dentro de mi Formulario. Es para que al Usuario le sea facil, rapido y exacto la carga de por ejemplo un cliente.

    ResponderEliminar
    Respuestas
    1. Hola Ever,
      para cargar con datos un ComboBox (desde la hoja de cálculo) deberías emplear el método .AddItem, indicando que celda quieres utilizar, o bien la propiedad RowSource, que admite directamente la carga de un Rango de la hoja de cálculo...

      Un cordial saludo

      Eliminar
    2. Excelente y en el ojo como siempre, un abrazo Ismael, gracias x 1.000!!!!

      Eliminar
  6. Hola excelforo, una consulta como se puede hacer en el comboBox, cuando comienzo a escribe una letra me salta a la celda E4. Lo que quiero es que no me salte a la celda E4 que permanezca en el comboBox y me dé el resultado en la celda E4.
    muchas gracias
    Slds cordiales
    Carmen.

    ResponderEliminar
    Respuestas
    1. Hola Carmen,
      bastaría con una instrucción
      Range("E4").value=ComboBox1.Value

      Espero haberte comprendido bien.
      Saludos

      Eliminar
  7. Gracias por responderme excelforo, la fórmula me funciona asta ahí muy bien pero el resultado me da en diferentes celdas y no se borra el resultado anterior quiera saber porqué o estoy haciendo algo mal.
    un saludo
    Carmen

    ResponderEliminar
    Respuestas
    1. Perdona Carmen, pero ya me costó entender el primer comentario...
      ¿no se borrar el resultado anterior de donde???.. ¿quizá del ComboBox???
      si quieres borrar el contenido del ComboBox una vez lo hayas empleado, tendrías que emplear la instrucción
      ComboBox1.Clear

      Saludos

      Eliminar
  8. Sólo una consulta, estoy trabajando un formulario el cual tiene 3 combobox y un listbox, la idea es que estos combobox jalen información de un archivo consolidado donde tengo 70 proveedores distintos, 500 marcas de productos y 2500 subfamilias y 7000 artículos. Osea que cuando seleccione un proveedor en el siguiente combobox aparesca sólo las marcas que vende este proveedor, así si selecciono una de las marcas me de las subfamilias de la marca y del proveedor, la idea con el listbox es que a medida de que vaya seleccionando los datos dentro de los combobox la lista original de 7000 se vaya reduciendo hasta la cantidad mínima que cumpla con lo que digan los 3 Combobox.
    Espero pueda ayudarme, gracias :D

    ResponderEliminar
    Respuestas
    1. Hola Jorge,
      yo ejecutaría un evento Change en el último de los 3 combobox, para que al realizar la última selección cambie el ListBox.. aunque sería posible hacerlo para cada combobox, desarrollando las diferentes combinaciones posibles de tres elementos.
      El trabajo seguiría construyendo la carga del ListBox.. lo más sencillo sería un recorrido o bucle, del tipo FOR.. NEXT, que recorra la lista original de 7000 productos, aplicando un triple condicional
      IF ... AND ... AND.. THEN
      y cuando se cumplan cargar el registro al LisBox...

      Espero haberte orientado, es algo complejo contestar o responder a la petición de un desarrollo (ver Normas de uso del blog) por comentarios.

      Un saludo!

      Eliminar