Nicola has attended:
Excel VBA Introduction course
Power BI Modelling, Visualisation and Publishing course
Use a Variable to select a Data Item in a chart
I am writing a macro to produce a set of reports for customers. The idea is that there is a standard template with a series of lookups to show the relevant information for each customer, which gets copied and pasted into a new excel sheet per customer to be sent out.
One of the things I am trying to do is show the relative position on a bar chart of the customer against all others in a group by highlighting the particular customer in a different colour. I have a lookup that gets the position in the chart based on the customer ID, I just can't figure out how to use this position in the code. My code is as follows:
Sub testchangecolour()
'
' testchangecolour Macro
'
'
ActiveSheet.ChartObjects("Chart 4").Activate
ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(1).Points(12).Select
With Selection.Format.Fill
.Visible = msoTrue
.ForeColor.RGB = RGB(0, 176, 80)
.Solid
End With
End Sub
In the line ActiveChart.SeriesCollection(1).Points(12).Select, I want to make Point(12) a reference to the cell that holds the position so that it will change depending on the customer selected. Adding the cell reference didn't work. How can I do this?
RE: Use a Variable to select a Data Item in a chart
Hi Nicola
Thanks for question.
Here's a modification of your code.
Sub testchangecolour()
ActiveSheet.ChartObjects("Chart 4").Activate
ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(1).Format.Fill.ForeColor.RGB = RGB(91, 155, 213)
ActiveChart.SeriesCollection(1).Points(Range("F2")).Select
With Selection.Format.Fill
.Visible = msoTrue
.ForeColor.RGB = RGB(0, 176, 80)
.Solid
End With
End Sub
Notes
The line
ActiveChart.SeriesCollection(1).Points(Range("F2")).Select
refers to cell F2 in the current sheet (change to suit yours) that contains the position of the client, which is 12 in your example.
Change 12 to another position and point gets coloured.
To reset the previous colour the line...
ActiveChart.SeriesCollection(1).Format.Fill.ForeColor.RGB = RGB(91, 155, 213)
changes all the series 1 colour to the default Blue. Again change to suit.
Hope that helps with your interactive chart.
A suggestion is to use combo box list from the Forms controls on the Developer tab. It returns the numeric position of the client selected.
The macro can be assigned to the combo box. (Example sent in case you want to see this).
Regards
Doug
STL
