CODEWITHSUNDEEP
excelvbabar-chart
StaceyB
StaceyB

Reputation: 35

Excel VB - Correct chart and formatting

I'm creating a chart in Excel. I'd like a different version of the chart and not sure what it would be called or if I need to do something else in the code.

There's two questions here.

  1. I would like the colorful version of the chart. Do I use something other than xlBarClustered?
  2. Notice the labels on the left hand side. I'd like these as the legend positioned to the right but it seems to think my legend is the header for column F.

I have two images:

the worksheet example: one is the worksheet example,

desired chart: the second is the chart I'm trying to achieve.

Here's the current code:

Sub CreateChart()
'
' CreateChart Macro
' Creates a chart in one worksheet
' Want the range to start with E1, go to the right and down.
' first row  is a header row.

'
       
        
    Dim rng As Range
    Dim cht As Chart
    
    'Set cht = Sheets("Chart 1")
    'Set cht = ActiveChart
    
    Set cht = ActiveSheet.Shapes.AddChart2.Chart
    Set rng = ActiveSheet.Range("E1:F5")
   
    cht.SetSourceData Source:=rng
    cht.ChartType = xlBarClustered
    
    cht.Axes(xlValue, xlPrimary).HasTitle = True
    cht.Axes(xlValue, xlPrimary).AxisTitle.Text = "Percent"
    
    cht.HasTitle = True
    cht.ChartTitle.Text = Cells(2, 3).Value
    cht.ChartTitle.Font.Bold = True
    cht.ChartTitle.Font.Name = "Arial"
    
    cht.HasLegend = True
    cht.Legend.Position = xlLegendPositionRight
    
    
End Sub

Upvotes: 0

Views: 47

Answers (1)

Michelle Trudeau
Michelle Trudeau

Reputation: 101

I believe that creating the chart without the use of vba is a much quicker and much more flexible way to get any chart you want, especially if someone requests changes. https://support.microsoft.com/en-us/office/create-a-chart-from-start-to-finish-0baf399e-dd61-4e18-8a73-b3fd5d5680c2

Upvotes: 1

Related Questions