Chart created with VBA and placed in range of cells

JaKro01

New Member
Joined
Jul 27, 2020
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Hi Excel Experts,

I am able to generate a chart using VBA with the data that I want to be used.

But i can not make something in VBA that places the chart in a certain range of cells.

Can somebody help me with this?
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Trevor G

Well-known Member
Joined
Jul 17, 2008
Messages
6,701
Office Version
  1. 2016
Platform
  1. Windows
Welcome to the Forum,

Something like this would work, if you can't get it to work then please copy your code into your thread you'll get a better response/solution.

expression.Add (Left, Top, Width, Height) deals with the positioning in the worksheet.

VBA Code:
 Set co = Sheets("Sheet1").ChartObjects.Add(50, 40, 200, 100)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,203
Office Version
  1. 365
Platform
  1. Windows
Cross posted Chart created with VBA and placed in range of cells

While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.
 

JaKro01

New Member
Joined
Jul 27, 2020
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Welcome to the Forum,

Something like this would work, if you can't get it to work then please copy your code into your thread you'll get a better response/solution.

expression.Add (Left, Top, Width, Height) deals with the positioning in the worksheet.

VBA Code:
 Set co = Sheets("Sheet1").ChartObjects.Add(50, 40, 200, 100)

Thank you for youre respons.

I use the next code:
Sub Macro2()
Dim GrafiekNaam As String
Dim GrafiekNummer As String
Dim GrafiekDataSource As String
Dim GrafiekTitel As String

GrafiekNummer = Range("grafieknummer").Value
GrafiekNaam = "Grafiek " & GrafiekNummer
GrafiekDataSource = Range("GrafiekDataSource").Value
GrafiekTitel = Range("grafiektitel").Value

ActiveSheet.Shapes.AddChart2(332, xlLineMarkers).Select
ActiveChart.SetSourceData Source:=Range(GrafiekDataSource)
ActiveChart.ChartArea.Select
ActiveChart.SetElement (msoElementLegendBottom)
ActiveChart.SetElement (msoElementChartTitleAboveChart)
ActiveChart.ChartTitle.Text = "Andere titel"
Selection.Format.TextFrame2.TextRange.Characters.Text = GrafiekTitel
With Selection.Format.TextFrame2.TextRange.Characters(1, 12).ParagraphFormat
.TextDirection = msoTextDirectionLeftToRight
.Alignment = msoAlignCenter
End With
With Selection.Format.TextFrame2.TextRange.Characters(1, 12).Font
.BaselineOffset = 0
.Bold = msoFalse
.NameComplexScript = "+mn-cs"
.NameFarEast = "+mn-ea"
.Fill.Visible = msoTrue
.Fill.ForeColor.RGB = RGB(89, 89, 89)
.Fill.Transparency = 0
.Fill.Solid
.Size = 14
.Italic = msoFalse
.Kerning = 12
.Name = "+mn-lt"
.UnderlineStyle = msoNoUnderline
.Spacing = 0
.Strike = msoNoStrike
End With
Set co = Sheets("Marktprijs benadering").ChartObjects.Add(380, 15, 600, 400)
Set co = Sheets("Marktprijs benadering").ActiveChart.Add(380, 15, 600, 400)

Range("GrafiekNummer").Value = Range("GrafiekNummer").Value + 1
GrafiekNummer = Range("grafieknummer").Value
Range("V36").Select
End Sub

How do I use your solution in this code?

I tried it 2 ways, but they don't work well.
 

Trevor G

Well-known Member
Joined
Jul 17, 2008
Messages
6,701
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

You need to reference the co at the top of your code

VBA Code:
Dim co As ChartObject

An example but I have not edited your code

VBA Code:
Sub CreateChart!A()
   Dim cht As ChartObject
   Dim ser As Series
   Set cht = ActiveSheet.ChartObjects.Add(20, 20, 400, 150)
   With cht.Chart
    .ChartType = xlLineMarkers
    .HasTitle = True
    .ChartTitle.Text = Worksheets("Chart").Range("B4")
    .SetSourceData Source:=Worksheets("Chart").Range("$A$1:$L$2")
   End With
   Set ser = cht.Chart.SeriesCollection(1)
   With ser.Format
    .Line.Visible = msoTrue
    .Line.ForeColor.RGB = RGB(26, 46, 74)
    .Fill.ForeColor.RGB = RGB(26, 46, 74)
  End With
End Sub
 

JaKro01

New Member
Joined
Jul 27, 2020
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Dear people,

I have got an answer through anonther forum.

Thank you for helping me.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,203
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

@JaKro01

Please acknowledge post#3 which you appear to have ignored & also post a link to thread where you have got a solution.
Thanks
 

JaKro01

New Member
Joined
Jul 27, 2020
Messages
5
Office Version
  1. 365
Platform
  1. Windows
I did not find a solution in post #3 on this forum.

I found the solution here:
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,203
Office Version
  1. 365
Platform
  1. Windows
I did not find a solution in post #3 on this forum.
No, it was not a solution, it was pointing out that you posted your question on another site without supplying a link, that is known as Cross posting.
Please take the time to read the rules & abide by them in future.

Thanks for the other link.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,140
Messages
5,599,964
Members
414,352
Latest member
macquarie_jchan58

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