Automate chart titles with macros

estatistics · August 28, 2021, 2:07pm

From
stackoverflow
i have this code. I want to loop through all chart, and give main title to charts from specific cells.
e.g. A1, b3, c4, z2, x7.

How can i change that code in order to do that?
( I did some changes )

Const SColumns = “L8, T8, AA8”

’ Set the title of the first Chart to the contents of C1
Sub SetTitle
’ Get active sheet
oSheet = ThisComponent.CurrentController.ActiveSheet

aColumns = Split(SColumns,",")

for i = uBound(aColumns) to 0 step 3
    
' Get the cell containing the chart title, in this case C1
oCell = oSheet.getCellRangeByNamee(aColumns(i))

oCharts = oSheet.getCharts() 

' Get the chart with index 0, which is the first chart created
' to get the second you would use 1, the third 2 and so on...
oChart = oCharts.getByIndex(i)

oChartDoc = oChart.getEmbeddedObject()

'Change title
oChartDoc.getTitle().String = oCell.getString() 

next i

End Sub

Sincerely,
Elias Tsolis
Statistician

KamilLanda · August 29, 2021, 5:01pm

You have some bugs in your macro:
const SColumns=“L8,T8” - use only commas (L8,T8,AA8) and no comma&space (L8, T8, …)
if you want to use comma&space then you must use Trim(aColumns(i))

for i = uBound(aColumns) to 0 step 3 → correctly is: … to 0 step -1

oCell = oSheet.getCellRangeByNamee → getCellRangeByName

Example
graf.ods (20.5 kB)