Solved

Add textboxes to charts

Posted on 2011-09-13
4
218 Views
Last Modified: 2012-05-12
Dear Experts:

below macro adds a text box to the first chart of the active worksheet. The contents for  the text box is taken from a cell that has been named Blue_Range_TextBox_01.

There are seven charts on the active worksheet and seven text boxes to be added.
The contents for these textboxes have to be taken from cells named ...
Blue_Range_TextBox_01
Blue_Range_TextBox_02
Blue_Range_TextBox_03
...
Blue_Range_TextBox_07

So, my requirements for the macro is as follows:

The code is to loop thru all the names (Blue_Range_TextBox_xx) and add a text box to each and every chart with the Chart number (Chart.Objects(1 or 2 or 3 etc.)) corresponding with the number at the end of the named ranges (Blue_Range_Text_Box_01 or _02 or _03 etc.).

I hope I could make myself clear.

Help is much appreciated. Thank you very  much in advance.

I have attached a sample file (with below code included) for your convenience.

Again, thank you very much for your professional help.

Regards, Andreas

 SampleFileTextBoxes.xlsm
Sub TextBox_Add()

Dim chtTemp As Chart
Dim objTemp As Object

Set chtTemp = ActiveSheet.ChartObjects(1).Chart
Set objTemp = chtTemp.Shapes.AddTextBox(msoTextOrientationHorizontal, 250, 174, 140, 20)

objTemp.Select
Selection.Formula = "='" & chtTemp.Parent.Parent.Name & "'!Blue_Range_TextBox_01"

ActiveCell.Select

End Sub

Open in new window

0
Comment
Question by:AndreasHermle
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 
LVL 1

Accepted Solution

by:
aszabo1 earned 500 total points
ID: 36532659
Hi Andreas,
I'm not sure I fully understand your question, but if the code you provide is doing what you want it to do, but you want to make it repeat 7 times, it would only take a few small changes:
First, define a variable to store your counter. Next, set up a loop using your counter. Finally, modify your code to use the counter instead of hardcoded numbers.
Sub TextBox_Add()

Dim chtTemp As Chart
Dim objTemp As Object
Dim x As Integer

x = 1
Do While x<=7
Set chtTemp = ActiveSheet.ChartObjects(x).Chart
Set objTemp = chtTemp.Shapes.AddTextBox(msoTextOrientationHorizontal, 250, 174, 140, 20)

objTemp.Select
Selection.Formula = "='" & chtTemp.Parent.Parent.Name & "'!Blue_Range_TextBox_0"&x
x = x+1
Loop

ActiveCell.Select

End Sub

Open in new window

If I've misunderstood your question, please clarify.
Thanks,
ASz
0
 

Author Comment

by:AndreasHermle
ID: 36534438
Dear asZ,

thank you very much for your swift response.  I guess, we are almost there

Thank you very much, It WORKS as desired, BUT as a matter of fact, the macro is to repeat any times relative to the number of charts and the corresponding numbers of named ranges. The sample file with 7 charts and named ranges is just an example.

ie. there could be 20 named ranges and corresponding 20 charts or fewer or more (any number).

IN CASE of say 20 named ranges, the ranges would be named as follows:
Blue_Range_TextBox_02
Blue_Range_TextBox_03
etc
Blue_Range_TextBox_20

The 20 charts are named as well (mychart01 till mychart20)
If you select the charts in the sample file the chart names (mychart01 etc.) appear in the name box.

With english not being my mother tongue, I hope I could make myself clear.

Help is much appreciated. Thank you very much in advance.

Regards, Andreas
0
 

Author Comment

by:AndreasHermle
ID: 36535870
Hi asZ,

I managed to tweak your code so that it suits my needs. Please have a look at the below code.

There is one thing I would like to know.

As I told you my charts have been named 'mychart01, mychart02, and so forth by running another macro  (code line in the other macro is: myChtObj.Name = "mychart" & Format(i + 1, "00")).

Hence my new requirement for below code is as follows:

Only charts named 'mychart01, mychart02 ... mychartxx are to be affected by this macro. Other charts (unnamed ones or charts that have been named differently) are to be left untouched by this macro.

So is it possible for you to rewrite below macro to comply with the above (new) requirement.

Help is much appreciated. Thank you very much in advance.

Regards, Andreas
Sub TextBox_Add_2()
'your code tweaked/slightly altered by me

Dim chtTemp As Chart
Dim objTemp As Object
Dim x As Integer

x = 1
Do While x <= ActiveSheet.ChartObjects.Count 'added by me
Set chtTemp = ActiveSheet.ChartObjects(x).Chart
Set objTemp = chtTemp.Shapes.AddTextBox(msoTextOrientationHorizontal, 250, 174, 140, 20)

objTemp.Select
Selection.Formula = "='" & chtTemp.Parent.Parent.Name & "'!Blue_Range_TextBox_" & Format(x, "00") 'tweaked by me
x = x + 1
Loop

ActiveCell.Select

End Sub

Open in new window

0
 

Author Closing Comment

by:AndreasHermle
ID: 36546466
Hey ASz,

thank you very much for your professional help. I really appreciate it.

Regards, Andreas
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question