Changing chart title with vba

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:

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

At the worksheet level:

Code:

---

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)

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

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?

HTH. Dave

Code:

---


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

---

That was akward. This should be better. Dave

Code:

---


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

---