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:

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

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

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...