Changing chart title with vba

[BaxterStockm]

hi

i want to change the chart title of a chart title by using vba if the name of a certain cell gets a certain value thats my code:

Dim outputType As String
Dim oCT As ChartTitle
If (Worksheets("Energy Report").Range("B3") = "Cat") Then
     Charts(1).HasTitle = True
     Set oCT = Charts(1).ChartTitle
     With oCT
          .Caption = Sheets("Test").Range("B3").Value
     End With
End If

so what i want is i want that if someone types "Cat" in cell b3 the Charttitle should change into "Test", how do i do that or what did i do wrong,

thanks for your advise

[Blade Hunter]

At the worksheet level:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$B$3" And UCase(Target.Text) = "CAT" Then
        ActiveSheet.Shapes(0).Select
        ActiveChart.HasTitle = True
        'ActiveChart.ChartTitle.Text = "Test"
        'ActiveChart.ChartTitle.Text = Target.text
    End If
End Sub

It is unclear if you wanted the title to be the contents of Cell B3 or if you wanted it to be hard coded "Test", Uncomment the line you want in the code above.

Also change the shape reference, I only had one chart on the sheet so object reference is obviously zero (option base is 0)

[BaxterStockm]

hi, thank you, i'm total new to this stuff so this is my code

Private Sub btnGo_Click()
    Dim startDate As String
    Dim endDate As String
    Dim outputType As String
    Dim accumulation As Integer
    ' validate cells
    startDate = Format(Worksheets("Energy Report").Range("B1"), "YYYY-MM-DD")
    endDate = Format(Worksheets("Energy Report").Range("B2"), "YYYY-MM-DD")
    outputType = Worksheets("Energy Report").Range("B3")
    If Target.Address = "$B$3" = "dollars" Then
        ActiveSheet.Shapes(0).Select
        ActiveChart.HasTitle = True
        ActiveChart.ChartTitle.Text = "this is the chart for dollars"
    Else
    ActiveChart.ChartTitle.Text = "this is the chart for kWhs"
    End If
    'If (Worksheets("Energy Report").Range("B3") = "dollars") Then
        'Worksheets("Energy Report").Range("C3") = "this is the chart for dollars"
    'Else
        'Worksheets("Energy Report").Range("C3") = "this is the chart for dollars"
    'End If
    pricePerkWh = Worksheets("Energy Report").Range("B4")
    If (Worksheets("Energy Report").Range("B5") = "yes") Then
        accumulation = 1
    Else
        accumulation = 0
    End If

but it's a runtime error, what do i do wrong?

[Dave]

HTH. Dave

 
Sheets("Energy Report").ChartObjects(1).Chart.Select
If (Target.Address = "$B$3") And ("$B$3" = "dollars") Then
    With ActiveChart
        .HasTitle = True
        .ChartTitle.Characters.Text = "this is the chart for dollars"
    End With
Else
    With ActiveChart
        .HasTitle = True
        .ChartTitle.Characters.Text = "this is the chart for kWhs"
    End With
End If

[Dave]

That was akward. This should be better. Dave

 
Sheets("Energy Report").ChartObjects(1).Chart.Select
With ActiveChart
    .HasTitle = True
    If (Target.Address = "$B$3") And ("$B$3" = "dollars") Then
        .ChartTitle.Characters.Text = "this is the chart for dollars"
    Else
        .ChartTitle.Characters.Text = "this is the chart for kWhs"
    End If
End With