Dynamic Bespoke Chart

[JimFl78]

Hi, Apologies for the multiple threads - not sure how I did that and will try to delete.

I have been able to generate a simple barchart from my results Stats worksheet with the following code

[vba]Charts.Add
ActiveChart.SetSourceData Source:=Sheets("Stats").Range("A1:C2")
ActiveChart.Location Where:=xlLocationAsObject, Name:="BARCHART"[/vba]

My Stats worksheet looks like this

SurveyName Survey1 Survey2 Survey3
Survey% 90% 75% 60%


How feasible is it to generate an excel chart on the fly from a series of data like this but I need to remove the numbers (ie %) from the x axis ? to be honest it doesnt even have to be generated on the fly - it could be set up.

I would also like to colour each of the bars. I could add the code to the generation of the worksheet so that it includes a colour reference.

Essentially what I am trying to achieve is to replace the numbers with like a colour coded scale:

for example

0-50 RED
50-70 YELLOW
70-90 ORANGE
90+ GREEN

Could I use a graphical element to replace the numbers?

Please let me know your thoughts on this and whether it can be done?

[JimFl78]

Hi,

Still dont know if anybody can help with this. This is where I have got so far, I believe you have to create a new series to be able to generate different color bars. Am I correct?

My code:

[vba]Dim myChtObj As ChartObject
Dim rngChtData As Range
Dim rngChtXVal As Range
Dim iColumn As Long

Sheets("Stats").Select

' make sure a range is selected
If TypeName(Selection) <> "Range" Then Exit Sub
' define chart data
Set rngChtData = ActiveSheet.Range("A22")
Set rngChtXVal = ActiveSheet.Range("A11")

' add the chart
Set myChtObj = ActiveSheet.ChartObjects.Add _
(Left:=250, Width:=375, Top:=75, Height:=225)

With myChtObj.Chart
' remove extra series
Do Until .SeriesCollection.Count = 0
.SeriesCollection(1).Delete
Loop
' add series from selected range, column by column
Dim newCol
newCol = 20
For iColumn = 1 To rngChtData.Columns.Count

With .SeriesCollection.NewSeries
.Values = rngChtXVal.Offset(iColumn, iColumn - 1)
'set the barcharts column titles
.XValues = rngChtXVal
.Name = rngChtData(1, iColumn)
' .Interior.ColorIndex = newCol
End With

Next
End With[/vba]

However for each bar the width is getting smaller as I enter more data - I am also struggling to set the color of each bar dynamically.

Would really appreciate any thoughts on this.

[rory]

Can you post a sample workbook (use Manage attachments under the Go Advanced option). You can have different coloured bars within the same series but they will obviously have to get smaller to fit on the chart as you add more data.