Excel VBA adding axis title to bubble chart error 424

[lochlan]

I wrote a macro to create a hybrid of a scatter chart and a bubble chart using a set of arbitrarily generated data as below:
data label x-axis y-axis bubble size
1 5 0.19 20
2 7 0.21 100
3 10 0.38 150

The "scatter bubble" chart was successfully generated. Then I wanted to add axis titles to the chart, but I hit an error. The following code was generated by recording the macro

Sub addtitle()
Workbooks("book").Worksheets("sheet").ChartObjects("SB").Activate
'note I added the above line just to make sure everything is well defined
ActiveChart.SetElement (msoElementPrimaryCategoryAxisTitleAdjacentToAxis)
ActiveSheet.ChartObjects("SB").Activate
ActiveChart.Axes(xlValue, xlPrimary).AxisTitle.Text = "x title"
End Sub

When running the final line (in red) an error message popped up:

Run-time error '424':
Object required

This is extremely confusing because the code came straight from a recorded macro. I have already googled the problem and instances with this error needed to be dealt with separately and I did not find a solution that worked for me. Sometimes the problem was just not solved. One suggested solution was to "go to make the project, select Options... and under the Compile tab, change it from: Compile to Native Code to Compile to P-Code". But I simply can't figure out how to do this. Firstly I can't find a "make project" option in my drop down menus, and I can't find how to change compilation options anywhere.

Note also that the same error occurs whenever I try to call the AxisTitle object. Even ...AxisTitle.Select fails.

I have also pasted the vba code I used to produce the "scatter bubble" chart for reference:
Sub ScatterBubble()

Workbooks("book").Worksheets("sheet").Activate

Dim n As Integer
n = ActiveSheet.UsedRange.Rows.Count - 1
MsgBox n

Dim chtObjs As ChartObjects
Set chtObjs = ActiveSheet.ChartObjects

Dim chtObj As ChartObject
For Each chtObj In chtObjs
If chtObj.Name = "SB" Then
chtObj.Delete
MsgBox "Chart deleted"
End If
Next

ActiveSheet.Shapes.AddChart.Name = "SB"
ActiveSheet.ChartObjects("SB").Select
ActiveChart.ChartType = xlBubble

For i = 1 To n
ActiveChart.SeriesCollection.NewSeries
With ActiveChart.SeriesCollection(i)
.Name = "='Bubble'!$A$" & (i + 1)
.XValues = "='Bubble'!$B$" & (i + 1)
.Values = "='Bubble'!$C$" & (i + 1)
.BubbleSizes = "='Bubble'!$D$" & (i + 1)
End With
Next i

'MsgBox "checkpoint"
End Sub

Thanks in advance to anyone who can solve the problem!
Bryan

 

[Andy Pope]

try either of the following

        .SetElement msoElementPrimaryValueAxisTitleRotated
        .Axes(xlValue, xlPrimary).AxisTitle.Text = "x title"
        
        
        With .Axes(xlValue, xlPrimary)
            .HasTitle = True
            .AxisTitle.Text = "x title"
        End With

the constant you had was for the category axis.