Is there a way to add a second line to the chart? For example, one line shows the headcount for a company, and the other shows the headcount for a specific team within that company? To compare trends?
Yes, there is. You can use SeriesCollection.Add instead of SetSourceData, and then you can add as many series as you need. For example, to add 2 series of data in columns A and B you would have something like this: With chartobj.Chart .ChartType = xlLine .SeriesCollection.Add Source:=Sheets("data").Range("A1:A10") .SeriesCollection.Add Source:=Sheets("data").Range("B1:B10") End With
Sorry for all the questions! 1) Is there a way to change the X-Axis labels from 1, 2, 3, etc. to the top row? In your example, to change it to January, February, March, etc.? 2) Additionally, how/where would we add into the VBA a manual min and max for the Y-Axis? I'm building a module for percentage, and it is defaulting to just a few percentage points above and below rather than 0-100%.
No problem, I always try to find time to answer, keep learning! 1. You can either modify the range including the first row (separated by comma) .SetSourceData Source:=ActiveSheet.Range("B1:M1,B" & r & ":M" & r") or change it later .Axes(xlCategory).CategoryNames = Range("B1:M1") 2. This is covered in this other video: th-cam.com/video/YxIYz-6GzQE/w-d-xo.htmlsi=sGePJsEzMGK2_FJd .Axes(xlValue).MinimumScale = 0 .Axes(xlValue).MaximumScale = 100 Note that when using Axes, xlCategory is for horizontal axis and xlValues for vertical axis
Thank you thank you!!@@ExcelMacroMania Those items worked. However...this may be a little niche...the x axis should show dates once per week (for example, 9/21, then 9/28, etc.). With this fix, for some reason, the x axis now shows 9/21, 9/23, 9/25, etc. with data being entered only once per week. This isn't a huge problem, but is there a way to code it to *only* include the dates I have entered in the first row?
@@longlivemusic0805 It looks like it gets the date automatically, instead of showing exactly your labels in row 1. Not sure how to avoid that, maybe you can try to set units auto to false: .Axes(xlCategory).MajorUnitIsAuto = False (or .MinorUnitIsAuto = False)
This worked for me, but every time I click on a row/hyperlink, I first get a "reference not valid" pop-up error. When I click okay, it generates the chart as it is supposed to. Help?
That's probably because you've changed the sheet name? If you look at the macros, you'll see they all refer to Sheet1, as that was the name of the worksheet in the example. If you change the name, you need to change that in the subaddress when adding the hyperlinks, as that refers to the tab name (not the sheet module name).
Is there a way to add a second line to the chart? For example, one line shows the headcount for a company, and the other shows the headcount for a specific team within that company? To compare trends?
Yes, there is. You can use SeriesCollection.Add instead of SetSourceData, and then you can add as many series as you need. For example, to add 2 series of data in columns A and B you would have something like this:
With chartobj.Chart
.ChartType = xlLine
.SeriesCollection.Add Source:=Sheets("data").Range("A1:A10")
.SeriesCollection.Add Source:=Sheets("data").Range("B1:B10")
End With
Sorry for all the questions! 1) Is there a way to change the X-Axis labels from 1, 2, 3, etc. to the top row? In your example, to change it to January, February, March, etc.? 2) Additionally, how/where would we add into the VBA a manual min and max for the Y-Axis? I'm building a module for percentage, and it is defaulting to just a few percentage points above and below rather than 0-100%.
No problem, I always try to find time to answer, keep learning!
1. You can either modify the range including the first row (separated by comma)
.SetSourceData Source:=ActiveSheet.Range("B1:M1,B" & r & ":M" & r")
or change it later
.Axes(xlCategory).CategoryNames = Range("B1:M1")
2. This is covered in this other video: th-cam.com/video/YxIYz-6GzQE/w-d-xo.htmlsi=sGePJsEzMGK2_FJd
.Axes(xlValue).MinimumScale = 0
.Axes(xlValue).MaximumScale = 100
Note that when using Axes, xlCategory is for horizontal axis and xlValues for vertical axis
Thank you thank you!!@@ExcelMacroMania Those items worked. However...this may be a little niche...the x axis should show dates once per week (for example, 9/21, then 9/28, etc.). With this fix, for some reason, the x axis now shows 9/21, 9/23, 9/25, etc. with data being entered only once per week. This isn't a huge problem, but is there a way to code it to *only* include the dates I have entered in the first row?
@@longlivemusic0805 It looks like it gets the date automatically, instead of showing exactly your labels in row 1. Not sure how to avoid that, maybe you can try to set units auto to false:
.Axes(xlCategory).MajorUnitIsAuto = False (or .MinorUnitIsAuto = False)
This worked for me, but every time I click on a row/hyperlink, I first get a "reference not valid" pop-up error. When I click okay, it generates the chart as it is supposed to. Help?
That's probably because you've changed the sheet name? If you look at the macros, you'll see they all refer to Sheet1, as that was the name of the worksheet in the example. If you change the name, you need to change that in the subaddress when adding the hyperlinks, as that refers to the tab name (not the sheet module name).