CODEWITHSUNDEEP
excelchartsvba
Yusuf
Yusuf

Reputation: 31

Creating charts in Excel VBA - Missing Labels

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:

  1. Option to choose the row no. in the beginning of the macro (for which row the chart needs to be prepared) - some input box
  2. Comparison Feature to compare Row 1 with Row 2. (some input box)
  3. Data Series Label (X Axis)
  4. 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

Upvotes: 0

Views: 638

Answers (1)

Yusuf
Yusuf

Reputation: 31

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

Upvotes: 0

Related Questions