Issue plotting charts

TeaTo

New Member
Joined
Jan 25, 2016
Messages
7
I created 3 separate modules to plot 3 separate graphs/charts based on different data ranges from the same sheet. I also added Command buttons to run the macro's from. Hence, 3 separate macros and 3 separate buttons. My issue is that I have to execute the buttons in a specific order to achieve the desired result. What I am trying to do is to click any random button at any time and have it plot the graph without following any order as it is doing now. The end result is to plot all 3 graphs in the same spreadsheet. I think the runtime error 1004 is being triggered on the range cover but I can be wrong. Any input would be greatly appreciated.

Module#1

Dim rng As Range
Dim cht1 As ChartObject
Dim RngToCover1 As Range
Dim chtob1 As ChartObject

'Set the data range for the chart
Set rng = ActiveSheet.Range("C43:C63,E43:E63, G43:G63")

'Create the chart
Set cht1 = ActiveSheet.ChartObjects.Add( _
Left:=ActiveCell.Left, _
Width:=450, _
Top:=ActiveCell.Top, _
Height:=250)

'Assign the chart its respective data
cht1.Chart.SetSourceData Source:=rng

'Assign (chart type and title)
cht1.Chart.ChartType = xlLine
cht1.Chart.HasTitle = True
cht1.Chart.ChartTitle.Text = "Breakeven Analysis"

'Set a range of data to be covered by the plotted chart
Set RngToCover1 = ActiveSheet.Range("B42:C63")
Set chtob1 = ActiveSheet.ChartObjects(1)
chtob1.Height = RngToCover1.Height ' resize
chtob1.Width = RngToCover1.Width ' resize
chtob1.Top = RngToCover1.Top ' reposition
chtob1.Left = RngToCover1.Left ' reposition

End

End Sub

Module#2

Dim xaxis As Range
Dim yaxis As Range
Dim RngToCover2 As Range
Dim chtob2 As ChartObject
Dim cht2 As ChartObject

Set xaxis = Range("$G$43:$G$43 , $E$43:$E$43")
Set yaxis = Range("$G$26 , $G$30")

'Create a chart

Set cht2 = ActiveSheet.ChartObjects.Add( _
Left:=ActiveCell.Left, _
Width:=450, _
Top:=ActiveCell.Top, _
Height:=250)

'Give chart some data
cht2.Chart.SetSourceData Source:=yaxis
'Determine the chart type
cht2.Chart.ChartType = xl3DColumnClustered
cht2.Chart.HasTitle = True
cht2.Chart.ChartTitle.Text = "Benefit Vs. Cost"
cht2.Chart.HasLegend = False
cht2.Chart.SeriesCollection(1).XValues = xaxis
cht2.Chart.SeriesCollection(1).Values = yaxis

Set RngToCover2 = ActiveSheet.Range("D42:H63")
Set chtob2 = ActiveSheet.ChartObjects(2)
chtob2.Height = RngToCover2.Height ' resize
chtob2.Width = RngToCover2.Width ' resize
chtob2.Top = RngToCover2.Top ' reposition
chtob2.Left = RngToCover2.Left ' reposition

End

Dim s As Series

Set s = cht2.SeriesCollection.NewSeries
With s
.Values = yaxis
.XValues = xaxis

End With

End Sub

Module #3

Dim cht3 As ChartObject
Dim RngToCover3 As Range
Dim chtob3 As ChartObject
Dim chtobt3 As ChartObject

Set cht3 = ActiveSheet.ChartObjects.Add( _
Left:=ActiveCell.Left, _
Width:=450, _
Top:=ActiveCell.Top, _
Height:=250)

cht3.Chart.SetSourceData Source:=Sheets("ROI Analysis").Range("C64:D74"), PlotBy:= _
xlColumns

cht3.Chart.ChartType = xl3DPie
cht3.Chart.ChartTitle.Text = "Benefits Breakdown"
cht3.Chart.ApplyDataLabels xlDataLabelsShowValue, xlDataLabelsPercent
cht3.Chart.SeriesCollection(1).HasLeaderLines = True

With cht3.Chart.SeriesCollection(1).DataLabels
.ShowPercentage = True
.Position = xlLabelPositionBestFit
.Separator = " | "
.ShowValue = False
End With

With cht3.Chart.Legend
.IncludeInLayout = True
.Position = xlLegendPositionRight
.AutoScaleFont = False
.Font.Size = 8
.Top = 5
.Left = 272
.Width = 200
.Height = 400
End With

Set RngToCover3 = ActiveSheet.Range("B64:H79")
Set chtob3 = ActiveSheet.ChartObjects(3)
chtob3.Height = RngToCover3.Height ' resize
chtob3.Width = RngToCover3.Width ' resize
chtob3.Top = RngToCover3.Top ' reposition
chtob3.Left = RngToCover3.Left ' reposition

Set chtobt3 = ActiveSheet.ChartObjects(3)

'Reposition title
With chtobt3.Chart.ChartTitle
.Left = 2
.Top = 2
End With

End Sub
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Watch MrExcel Video

Forum statistics

Threads
1,122,355
Messages
5,595,678
Members
414,009
Latest member
SNesbyCarr

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top