VBA Marco to Change Display Units Not Working

[smithan04]

I am creating a vba macro to change a charts units based on a word change in a cell $C$2. Although the macro look simple it is not working. I am getting Run-time error '424' object required on the 3rd line of the script, and Run-time error 91 Object variable or With block variable not set on the 4th line. Any help is greatly appreciated.

Private Sub Worksheet_Calculate()
Range("c2").Select
ActiveCell.Text = "Million Barrels per Day"
ActiveChart.ChartObjects("Chart 2").Chart.Axes(xlValue).DisplayUnit = xlThousands
End Sub

 

[JohnS]

ActiveCell.Text is for getting the cell value only. If you want to set it, use ActiveCell.FormulaR1C1 = "Million Barrels per Day"
Cheers

 

[goodfellow]

Hello,
ActiveCell.Text property is for getting the value only. If you want to set it change this line to:

ActiveCell.FormulaR1C1 = "Million Barrels per Day"