Creating charts in Excel VBA - Missing Labels

Question

UPDATE 2: Trying to define the Variable with the column range in the VBA. Can anyone guess what's wrong in this code ? Thanks in advance...

UPDATE 1: I have been successful in generating the chart. Thanks to you guys for the constructive criticism. I'm a newbie to VBA, just learning :) My challenge now is to define the row selected for the graph as a variable.

ie. The user gives an input for the ROW and the macro generates the chart for the intended Row.

Find the UPDATED CODE BELOW. Thanks to all

I need to write a macro to create a individual performance chart in Excel. I have a few lines of code recorded, but the resultant chart does not have any labels on the X & Y Axis.

My requirement is to create a chart with the following features:

Option to choose the row no. in the beginning of the macro (for which row the chart needs to be prepared) - some input box
Comparison Feature to compare Row 1 with Row 2. (some input box)
Data Series Label (X Axis)
Chart Title

MY EXCEL LOOKS LIKE THIS:

Sales Achieved  |Clients Met|   Client Responsiveness|  

Employee 1 |           6    | 7         |            8           |

Employee 2 |           6    | 7         |            8           |

Employee 3 |           6    | 7         |            8           |

Employee 4 |           6    | 7         |            8           |

Sub generatecharts()
Dim xValRange As Range
Dim r
r = irow
irow = InputBox("Which Chart do you want to generate?")

With ActiveSheet
Set xValRange = ActiveSheet.Range("B" & r & ":" & "Q" & r)
End With

With ActiveSheet
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).Values = xValRange
ActiveChart.SeriesCollection(1).XValues = "=Sheet2!$B$1:$Q$2"
ActiveChart.SeriesCollection(1).Name = "=Sheet2!$A$" & r
With ActiveChart.Parent
.Height = 400
.Width = 800
End With
End With
End Sub

This reads more like the start of a blog entry. What is your question? — sous2817
Hi Sous,My requirement is to create a chart with the MACRO with the above mentioned features. — Yusuf
yep, I got that. Unfortunately, this ins't really a code writing service. Best to break down your requirements, try to solve each requirement. If you get stumped on something, then come here with a specific question, what you tried, what didn't work, what the expected output is...etc. Typically the "here do this for me" type of posts aren't received very well. — sous2817
Hi Everyone... The code is working fine now and as Intended :) — Yusuf

Yusuf Yusuf · Accepted Answer · 2015-10-17T07:40:00

Sub Macro4()
Dim xValRange As Range
Dim r As Integer
Range("A30").Select 'selected a blank cell on purpose to avoid undefined charts
r = InputBox("Enter the Row Number to generate Chart")
With ActiveSheet

Set xValRange = ActiveSheet.Range("$B$" & r & ":" & "$T$" & r)
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlColumnClustered
ActiveChart.HasTitle = True
ActiveChart.ChartTitle.Text = Cells(r, 1)
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).Values = xValRange
ActiveChart.SeriesCollection(1).XValues_
=ActiveSheet.Range("=KRAs!$B$1:$T$2")
ActiveChart.SeriesCollection(1).Name = ActiveSheet.Range("=KRAs!$A$" & r)
ActiveChart.SetElement (msoElementDataLabelInsideEnd) 'to add the data labels
End With
With ActiveChart.Parent
.Height = 400
.Width = 800
.Top = 150    ' reposition
.Left = 200   'reposition
End With
End Sub

Creating charts in Excel VBA - Missing Labels

1 Answers