viernes, 18 de noviembre de 2011

VBA: Asociar los elementos de un ComboBox a un campo de tabla dinámica en Excel.

Hace algún tiempo me llegó un mail solicitando ayuda sobre un tema que ya entonces llevaba tiempo pensando en subir al blog. Se trata de asociar los elementos de un ComboBox a un campo de tabla dinámica en Excel. Concretamente lo explicaré basándome en la petición del lector.:

...Tengo 15 graficas (pivot charts) que he conseguido con una macro se actualicen al mismo tiempo cuando cambio el filtro en una de ellas. Ahora le jefe quiere que en cada una de esas graficas distinga con un color or etiqueta una sola de las series. Me explico:
Los graficos son serie de barras, cada serie es un cliente y cada grafico es un producto diferente. Nuestro departamento quiere enviar cada mes esos graficos a cada cliente para informarle como esta su consumo en relacion al resto, pero sin que conozca quienes son el resto de clientes. Neceisto identificar ese cliente al que le enviare los graficos, con un color de barra diferente o con una etiqueta en la serie
¿Es posible construir una macro para hacer esto o hay alguna otra manera?...


Lo que pide nuestro amigo es que , basándonos en los elementos de un campo de una Tabla dinámica, poder seleccionar fuera de la tabla dinámica, por ejemplo, con un ComboBox uno de los elementos, y que ese elemento aparezca en el Gráfico dinámico con una columna de color distinto a los demás elementos.
Veamos cuál es nuestro origen de datos y como queda tanto nuestra Tabla dinámica como nuestro Gráfico dinámico:

VBA: Asociar los elementos de un ComboBox a un campo de tabla dinámica en Excel.


Simplemente hemos generado una Tabla (Ctrl+q) sobre nuestro origen de datos, y la hemos resumido con una Tabla dinámica, de la cual hemos generado un Gráfico dinámico tal cual muestra la imagen.
Nuestro objetivo ahora es conseguir que a nuestra elección, sobre un Concepto cambie nuestro gráfico, mostrando la columna del 'Concepto' seleccionado de otro color.
Para ello, en primer lugar, insertaremos un Combobox(cuadro combinado) desde la Ficha Programador > Grupo opciones Controles > Insertar > Controles ActiveX > Cuadro Combinado

VBA: Asociar los elementos de un ComboBox a un campo de tabla dinámica en Excel.


Una vez 'incrustado' en la hoja de cálculo, desde el mismo gupo de opciones, seleccionaremos y mostraremos la ventana de Propiedades:

VBA: Asociar los elementos de un ComboBox a un campo de tabla dinámica en Excel.


dentro de estas Propiedades buscaremos 'LinkedCell' y escribiremos la referencia de una celda de nestra hoja de cálculo, en mi caso la celda H1. Con esto hemos conseguido que cuando seleccionemos y empleemos el desplegable del ComboBox(Cuadro combinado) ese valor aparezca en la celda H1... valor que usaremos, en un paso siguiente para conseguir que el gráfico cambie.
Añadiremos el siguiente código VBA a este ComboBox, para ello, haremos doble click sobre el objeto recién incrustado y llegaremos a la ventana de código de la hoja activa; en ella escribiremos:

Private Sub ComboBox1_GotFocus()
Dim eltos As Long

'Refrescamos la tabla dinámica
'de la que mostraremos los elementos en el Combobox
' y los ordenamos...
With Sheets("Datos").PivotTables("Tabla dinámica1")
.PivotCache.MissingItemsLimit = _
xlMissingItemsNone
.RefreshTable
.PivotFields("Concepto").AutoSort _
xlAscending, "Concepto"
End With

'limpiamos el ComboBox
ComboBox1.Clear

'Vamos a llenar dinámicamente el combobox
'con los ELEMENTOS  del campo de TD
With ActiveSheet.PivotTables("Tabla dinámica1")
eltos = .PivotFields("Concepto").PivotItems.Count

For i = 1 To eltos
'Añadimos los ELEMENTOS de la Tabla dinámica al combobox
ComboBox1.AddItem ActiveSheet.PivotTables("Tabla dinámica1").PivotFields("Concepto").PivotItems(i)
Next
End With

End Sub


Con este código hemos conseguido que el desplegable del ComboBox muestre sólo los valores del campo 'Concepto' de la tabla dinámica sobre la que trabajamos.
Nos queda ahora generar el código que cambie el color de la columna del gráfico dinámico en función al valor desplegado con nuestro ComboBox. Para ello insertaremos un Módulo en el Explorador de Proyectos del Editor de VBA:

Sub ColumnaDiferente()
Dim x As Variant, n As String
Dim Val As Variant
Dim nombrehoja As Variant, rangocelda As Variant

nombrehoja = ActiveSheet.Name
rangocelda = ActiveCell.Address

x = Sheets("Datos").Range("H1").Value
n = Sheets("Datos").PivotTables("Tabla dinámica1").PivotFields("Concepto").PivotItems.Count

Sheets("Datos").ChartObjects("1 Gráfico").Activate
With ActiveChart.SeriesCollection(1)
For i = 1 To n
If Sheets("Datos").PivotTables("Tabla dinámica1").PivotFields("Concepto").PivotItems(i) = x Then
.Points(i).Interior.Color = RGB(255, 0, 0)
.Points(i).HasDataLabel = True
.Points(i).ApplyDataLabels Type:=xlValue
Else
On Error Resume Next
.Points(i).Interior.Color = RGB(0, 0, 255)
.Points(i).HasDataLabel = False
End If
Next i
End With

Sheets(nombrehoja).Range(rangocelda).Select

End Sub


Este último código genera una macro que evalua cada elemento del gráfico, comparándolo con el valor desplegado (y mostrado en la celda H1), cuando encuentre la coincidencia modifica el color de la columna.
Si asignamos esta macro llamada 'ColumnaDiferente' a un botón podemos comprobar cómo funciona.


Lo interesante de este ejemplo es que los elementos a desplegar siempre estarán vinculados a los del cmapo 'Concepto' de nuestra Tabla dinámica...

10 comentarios:

  1. Hola

    Buscaba aqui la solucion a mi problema pero me parece que este no es exactamente mi caso. Puede que lo mio sea mas sencillo.

    Yo tengo una tabala de datos a partir de la cual he sacado el grafico de lineas. Esto muestra una evolucion de cada cliente a lo largo del año (cada cliente es una fila y cada columna una fecha).

    El problema es que son muchas lineas (43) y en el grafico es imposible distinguir nada.

    Lo que busco es una manera (si la hay) de vincular de alguna manera la leyenda del grafico a las lineas, de forma que al seleccionar un cliente en la leyenda se vea de forma clara su linea y pueda compararse de forma visible con el resto de clientes.

    Por cierto trabajamos con excel 2010

    Muchas gracias

    ResponderEliminar
  2. Hola,
    creo que tu problema realmente es la cantidad de columnas dispuestas en el gráfico, el que puedas resaltar o no de cualquier manera la columna concreta de un cliente no te va a ayudar demasiado (y más en un gráfico de líneas)...
    salvo que pruebes a cambiar para ese cliente (esto es, esa Serie) el tipo de gráfico a otro de columnas por ejemplo, claro está como siempre en los gráficos habría que probar y ver si queda bien o no.
    Slds

    ResponderEliminar
  3. Hola,
    muchas gracias por tu respuesta, en serio.
    Aunque esto no me sirve. Mi jefe quiere tener un grafico en el que se vea la evolucion de cada cliente en comparacion con la masa de clientes. Ya le ofreci opciones similares a lo que tu me has comentado, pero no quiere tener mil graficos, ni tener que estar toqueteando la base de datos para ver un cliente u otro. Por eso decia lo de poner en el grafico una leyenda "activa", osea que sirviera para seleccionar un cliente y que automaticamente su linea se viera resaltada sobre el resto, o solo se viera esa linea o algo del estilo.
    Por cierto he seguido leyendo mas en el foro y me parece una pasada, no sabia que se podian hacer tantas cosas con el excel!!!
    Slds

    ResponderEliminar
  4. Muchas gracias por tu comentario...
    pero llegamos a un punto sin retorno, por que si no te vale resaltar ese punto(o cliente) como se muestra en el post, y por imposiciones 'jerárquicas' no se admiten otras propuestas, no se me ocurre otra cosa.
    Yo le daría otra vuelta a lo explicado en el post, realmente estaríamos haciendo lo que quiere tu jefe, esto es, resaltar en un mismo gráfico el cliente elegido.
    Suerte y un cordial saludo.

    ResponderEliminar
  5. Oye una pregunta puedo repetir eso para diferentes combobox?

    ResponderEliminar
    Respuestas
    1. Hola EneRenE,
      si claro, siempre que identifiques correctamente sobre qué ComboBox quieres actuar en la macro...
      Un saludo

      Eliminar
  6. Interesante el aporte y de utilidad, estoy realizando algo similar pero lo que tengo la duda es que al momento de seleccionar una opción en el ComBox la tabla dinámica muestre la gráfica correspondiente a dicha categoría, descartando las otras opciones. no se si esto sea posible

    ResponderEliminar
    Respuestas
    1. Hola, que tal?
      mechas gracias...
      no termino de comprender bien qué quieres decir con que 'la Tabla dinámica muestre la gráfica correspondiente a dicha categoría'.
      Si te refieres a que sólo muestre el elemento seleccionado, tendrías que aplicar la propiedad .Visible sobre los diferentes PivotItems, con True para mostrarlos y False para ocultarlos...
      Pero si me explicas algo mejor el sentido del problema quizá te pueda ayudar algo más.
      Un cordial saludo

      Eliminar
  7. Hola Tengo una inquietud es posible que nos facilites el archivo con este ejemplo.

    Gracais

    ResponderEliminar
    Respuestas
    1. Hola Mario,
      lo siento.. pero no conservo todos los ficheros
      :'(

      Intenta replicar los pasos expuestos, y si tuvieras algún problema puedes plantear las dudas aquí mismo..

      Un saludo

      Eliminar