98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Use a Variable to select a Data Item in a chart | Excel forum
Use a Variable to select a Data Item in a chart | Excel forum
Resolved · Medium Priority · Version 2010
Mon 14 Nov 2016, 10:18Delegate Nicola said...
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?
Tue 22 Nov 2016, 10:50Trainer Doug said...
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
Attached files...
Tue 29 Nov 2016: Automatically marked as resolved.
Training information:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Remove unused toolbar buttonsAre there buttons on your Excel toolbars that you never use? Remove them from the toolbar by doing the following: |
