Chart template-Modify Cell references using VBA

[ak_excel]

Hi,
I am a familiar with VBA and have created several tools based on it. I am working on creation of a report right now and find myself stuck. My problem is as follows:
The report consists of several (20+) sheets. Each sheet contains 3 charts.
I have successfully created all the charts, and formatted them as per the requirement, however each chart needs to have several text boxes in specific areas of the charts.Each text box should refers to a cell in the sheet it is located in.
The way I approached this part was to create a chart template. I used vba code to import the same template for all the charts. (the cell addresses in the text box are same for each sheet.. but a chart text box formula refers to a cell like this ='Sheet1!'$A$1 as opposed to a simple text box which uses =$A$1)
The problem I face is that since I created the chart template for sheet1.. all the text boxes in Sheet 2,3,4,5,..n refer to the same cell like this ='Sheet1!'$A$1'
I had assumed that the template would automatically take the sheet name and since the cell address is same , I would get all my charts created.
Would appreciate your help in this

 

[pgc01]

Hi
Welcome to the board

One way is, instead of a fixed address, using a worksheet named range.

You create a named range in the worksheet where you create the template referring to A1 and use it in the TextBox formula. Then export the template.

Now for each sheet where you want to use the template you first create a worksheet named range referring to the cell in that worksheet.

Then when you import the chart template. Since the textbox uses the named range, it will automatically point to the cell in the current sheet.

 

[ak_excel]

Hi PGC01!
Thanks a lot for the solution, it works well. However I have a followup query as follows:

Is it possible to loop through all the text boxes in a chart and modify its formula using code?
I was able to loop through the text boxes but can't seem to identify what property to change

Once again truly appreciate your help!

Hi
Welcome to the board

One way is, instead of a fixed address, using a worksheet named range.

You create a named range in the worksheet where you create the template referring to A1 and use it in the TextBox formula. Then export the template.

Now for each sheet where you want to use the template you first create a worksheet named range referring to the cell in that worksheet.

Then when you import the chart template. Since the textbox uses the named range, it will automatically point to the cell in the current sheet.

 

[pgc01]

Hi

You use the .Formula property

Ex., changing the formula in all the textboxes in chart MyChart in Sheet1 to "=Sheet1!A1":

Sub ChtTxtB()
Dim chtO As ChartObject
Dim txtBox As TextBox
 
Set chtO = Worksheets("Sheet1").ChartObjects("MyChart")
 
With chtO.Chart
    For Each txtBox In .TextBoxes
        txtBox.Formula = "=Sheet1!A1"
    Next txtBox
End With
End Sub

 

[ak_excel]

Hi,
I am not able to dimension:
Dim Txtbox as Textbox

Do I need to add references ? The way i looped earlier was using the Shapes collection.. but it doesn't have a .formula property

Once again thanks for your input!

 

[pgc01]

I never had to add any reference to work with textboxes. Are you on the Mac?

You can use also the shape:

Sub ChtTxtB()
Dim chtO As ChartObject
Dim shp As Shape
 
Set chtO = Worksheets("Sheet1").ChartObjects("MyChart")
 
With chtO.Chart
    For Each shp In .Shapes
        shp.OLEFormat.Object.Formula = "=Sheet1!A1"
    Next shp
End With
End Sub

 

[ak_excel]

Hi ,
Thanks a lot! Seems to work just fine! Not using Mac, the management needs too much dynamic text on the chart, and the guy who did it previously used this method, compelling me to replicate his thing. Once again thanks for your time