Secondary & primary axes scaling !

[amrane]

Dear Forum,

I am looking for your advice, how to set scal to my chart,

so far I test mnay line code, but I am getting error every time ( see my chart)

thanks in advance,

Br,
Amrane

[June7]

Review https://stackoverflow.com/questions/14302560/using-vba-to-put-a-chart-series-on-the-secondary-x-axis

.SeriesCollection(2).AxisGroup = xlSecondary
.HasAxis(xlCategory, xlPrimary) = True
.HasAxis(xlCategory, xlSecondary) = True
.Axes(xlCategory, xlSecondary).MaximumScale = -30
.Axes(xlValue, xlSecondary).MaximumScale = -30

Runs without error. Does it produce desired result?

[amrane]

Dear Mr June7

thanks you sir for your support,

Looks, I pasted the above code in wrong place, please correct me:


30547
best regards/ Amrane

[Aussiebear]

Please submit your code in print rather than images.

[June7]

I pasted it into your workbook module and it worked originally but now that I reopened workbook, getting same error. Odd.

I have done this with Access charts. Wouldn't think Excel would be so different.

xlCategory is the x-axis which is date values so why would you set its maximum scale to -30?

You want to build chart completely from scratch or just change some properties?

Try this:

With ct
.HasLegend = True
.HasTitle = True
.ChartTitle.Text = "record Srl"
.Axes(xlCategory).CategoryType = xlCategoryScale

Set sc1 = .SeriesCollection


...

.Axes(xlValue, xlSecondary).MaximumScale = -30


End With
End Sub

[amrane]

Dear Mr June7


thanks sir for your help, looks I solve it by adding below block in the end of the code:

30553
thanks again/ Amrane

[amrane]

dear Forum,

I am sorry, looks it can not accept different min/max for both primary& secondary axes !!

so problem persist, !!!

Br/ Amrane

[Dave]

Hi amrane. You seem to be missing some code...

With ct.SeriesCollection(2).AxisGroup = 2
.HasAxis(xlCategory, xlPrimary) = True
.HasAxis(xlCategory, xlSecondary) = True
.HasAxis(xlValue, xlPrimary) = True
.HasAxis(xlValue, xlSecondary) = True
.Axes(xlCategory, xlPrimary).CategoryType = xlAutomatic
.Axes(xlCategory, xlSecondary).CategoryType = xlAutomatic
End With
You need to include the "crosses" before you set the min/max...

With ct
.Crosses = xlMaximum
.MinimumScale = min_Rx_chart - 10
.MaximumScale = -30
End With
The "crosses" can be xlMaximum, xlMinimum or xl custom (which you have to set a .crossesAt value). HTH. Dave
ps. Please post code not pictures of code

[amrane]

Dear Mr June7

thanks you sir for your continuous support,

looks I didn't find the correct place to past the piece of code you gave me, because of the the error code,

I am attaching all my tentative & result:

Chart01: original version (no max/min setting),
Chart02: modif-1 version by adding (2ed axes Min/max setup), but the 1st axes is impacted,
Chart03: modif-2 version even after adding 1st axe setting no improvement,
Chart04: modif-3 I have below error code :
30560
BR/ Amrane

[June7]

I did not give you that new code. Dave did.

If you followed my suggestion as shown in post 5 for the first version you posted for placement of code, it works. Be sure to eliminate the first code I originally suggested in post 2.


.Axes(xlValue, xlSecondary).MinimumScale = min_RX_chart - 10
.Axes(xlValue, xlSecondary).MaximumScale = -30


End With
End Sub

However, not all data is within that range and does not display in chart.

[amrane]

Dear Mr Dave, Mr June7,

finaly the problem is solved,:yes
I rebuilt the code, unfortunately the root cause is not yet recognized,
I am sharing the corrected code & its result as below



Option Explicit
Sub Mbb()
Dim datasheet As Worksheet
Dim cobject As ChartObject
Dim chrt As Chart
Dim s_co1lect As SeriesCollection
Dim ser_1, ser_2, ser_3 As Series
Dim ser_4, ser_5, ser_6 As Series
'Set cobject = ActiveSheet.ChartObjects("figure record")
Set datasheet = ActiveWorkbook.Sheets(1)
Set cobject = datasheet.ChartObjects.Add(Range("A2").Left, Range("A2").Top, 1100, 350)
cobject.Name = "figure record"
Set chrt = cobject.Chart
Set s_co1lect = chrt.SeriesCollection
Set ser_1 = s_co1lect.NewSeries
Set ser_2 = s_co1lect.NewSeries
Set ser_3 = s_co1lect.NewSeries
Set ser_4 = s_co1lect.NewSeries
Set ser_5 = s_co1lect.NewSeries
Set ser_6 = s_co1lect.NewSeries
With chrt
.HasLegend = True
.HasTitle = True
.ChartTitle.Text = "record Srl"
.Axes(xlCategory).CategoryType = xlCategoryScale
End With
With chrt
With ser_4
.Name = "=Sheet1!$d$28"
.XValues = Range("$b$29:$b$123")
.Values = "=Sheet1!$D$29:$D$123"
.ChartType = xlColumnClustered
.AxisGroup = 1
End With
With ser_5
.Name = "=Sheet1!$f$28"
.XValues = Range("$b$29:$b$123")
.Values = "=Sheet1!$f$29:$f$123"
.ChartType = xlColumnClustered
.AxisGroup = 1
End With
With ser_6
.Name = "=Sheet1!$g$28"
.XValues = Range("$b$29:$b$123")
.Values = "=Sheet1!$g$29:$g$123"
.ChartType = xlColumnClustered
.AxisGroup = 1
End With
With ser_1
.Name = "=Sheet1!$J$28"
.XValues = Range("$b$29:$b$123")
.Values = "=Sheet1!$J$29:$J$123"
.ChartType = xlLine
.Format.Line.ForeColor.RGB = RGB(0, 0, 255)
.Format.Line.Weight = 0.25
.AxisGroup = 2
End With
With ser_2
.Name = "=Sheet1!$N$28"
.XValues = Range("$b$29:$b$123")
.Values = "=Sheet1!$N$29:$N$123"
.ChartType = xlLine
.Format.Line.ForeColor.RGB = RGB(0, 0, 255)
.Format.Line.Weight = 0.25
.AxisGroup = 2
End With
With ser_3
.Name = "=Sheet1!$P$28"
.XValues = Range("$b$29:$b$123")
.Values = "=Sheet1!$P$29:$P$123"
.ChartType = xlLine
.Format.Line.ForeColor.RGB = RGB(0, 255, 255)
.Format.Line.Weight = 0.25
.AxisGroup = 2
End With
.Axes(xlValue, xlSecondary).MaximumScale = -30
.Axes(xlValue, xlPrimary).MinimumScale = 0
End With

End Sub



30562