PDA

View Full Version : changing chart title with vba



BaxterStockm
03-01-2011, 05:46 PM
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
03-01-2011, 08:22 PM
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
03-02-2011, 10:36 AM
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
03-02-2011, 12:11 PM
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
03-02-2011, 08:22 PM
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