Adding in Axis Titles using VBA

[Kawule]

I'm trying to figure out how to add in Axis titles in my code below

Sub CreateChart()Dim rng As Range
Dim cht As ChartObject
Dim ws As Worksheet, ws2 As Worksheet


Set ws = Worksheets("Daily Data Transfer")
Set ws2 = Worksheets("Daily Report")
Set rng = ws.Range("B1:C31,G1:G31,Q1:R31")
Set cht = ws2.ChartObjects.Add(Left:=50, Width:=283.5, Top:=50, Height:=170)
With cht.Chart
    .HasTitle = True
    .ChartTitle.Text = "pH/Temp°C vs Dosage mg/L"
    .SetSourceData Source:=rng
    .ChartType = xlLine
    .SetElement (msoElementPrimaryValueAxisShow)
    .SetElement (msoElementPrimaryValueAxisTitleAdjacentToAxis)
End With




End Sub

Most of this was trial and error via Macro Recorder but I'm stumped after the .SetElement part as I don't think there is an easy way to do it when I'm using Chart as a variable type? I know in the Macro recorder it will do something like this:

ActiveChart.Axes(xlValue, xlPrimary).AxisTitle.Text = "pH"

 

[RedBeard]

Try this:

'X axis name
.Axes(xlCategory, xlPrimary).HasTitle = True 
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "X-Axis" 
'y-axis name
.Axes(xlValue, xlPrimary).HasTitle = True 
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Y-Axis"

 

[Kawule]

Well that worked! I wonder why the Macro Recorder version doesn't work unless its using the Charts2 object in VBA?

My next step is to plot the data onto a secondary axis is that also done with .setelement?

 

[RedBeard]

The macro recorder does its own thing, no idea.

Secondary axis should be

.HasAxis(xlCategory, xlSecondary) = True
.Axes(xlCategory, xlSecondary).MinimumScale = Range("A").Value
.Axes(xlCategory, xlSecondary).MaximumScale = Range("B").Value

unless my memory fails me.