Free Microsoft Excel 2013 Quick Reference

VBA Chart properties, text box, axis labels

I can't get the first two lines of code to work. They do nothing. They should put the category label on the category axis just as the last two lines put the value axis label on the chart
Sub ChartText()

 ActiveChart.SetElement (msoElementPrimaryCategoryAxisTitleAdjacentToAxis)
 ActiveChart.Axes(xlValue, xlCategory).AxisTitle.Text = "Angle, Degrees"

  ActiveChart.Axes(xlValue).HasMinorGridlines = 1
    ActiveChart.Shapes.AddTextbox(msoTextOrientationHorizontal, 467, 9, 180, 140).TextFrame.Characters.Text = "Date    Job#:     P/N:      S/N:      Pattern:         Cut:     Polarization:      Gain:     Engr: EPA "

ActiveChart.SetElement (msoElementPrimaryValueAxisTitleRotated)
 ActiveChart.Axes(xlValue, xlPrimary).AxisTitle.Text = "Relative Power, dB"
 End Sub
Also, I can't figure the code to put a black border around the text box. I can find no information on a listing of chart properties.

Post your answer or comment

comments powered by Disqus

I want to add text-boxes and Label through VBA coding. Can Anyone help me and let me know how can i do this.

Manish R.

Hi guys,I'm trying to insert a text box using vba, but the text box needs to be the same size as the cells that a user has selected. It's essentially for a time-line so that a user can highlight the cells (months) they want and click a button to insert the textbox to cover all those months. The selection would only ever be on the same row. I'm using Excel 2002 (long story why). I can figure out how to insert and size the text box based on an active cells size, but not multiple cells. What i have so far is shown below:


If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
If anyone can help, i'd be forever grateful!

Is it possible to link text in charts and text boxes to a range without
making a macro? If 'yes', then how can this be done?

e18's Profile:
View this thread:

Is it possible to link text in charts and text boxes to a range without making a macro? If 'yes', then how can this be done?

If I have a text box or label of web URLs, Is it possible to turn these
URLs into links the user can then click on?


I have been trying to figure out how to display this data in a simple chart with the Y axis labels as Length of Time, X axis lables as Age Groups, and series 1 = Black Males and series 2 = White Males.

******** ******************** ************************************************************************>Microsoft Excel - WR_Recidivism_Summary Update 103007.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutC170D170E170F170G170C171D171E171F171G171C172D172E172F172G172C173D173E173F173G173C174D174E174F174G174C175D175E175F175G175C176D176E176F176G176C177D177E177F177G177C178D178E178F178G178C183D183E183F183G183C184D184E184F184G184C185D185E185F185G185C186D186E186F186G186C187D187E187F187G187C188D188E188F188G188C189D189E189F189G189C190D190E190F190G190C191D191E191F191G191=
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box

Any help and/or suggestions would be great!!!

Couple of questions: 1st, I have a chart comparing returning traffic for
jan-sep for 2006 and 2007. I would like the month columns to be right next
to each other. For example, Jan 2006 & 2007 right next to each other and
then a space and then Feb 2006 & 2007 right next to each other. Right now
there is a space between every single column.
2nd, I have used data labels to identify the # of returning traffic for each
month for both 2006 & 2007. I have included a text box at the bottom of the
chart to label the months Jan - Sep. however they don't line up with their
respective column. They may look perfect on print preview, but when I print
they are out of line and if I move it then someone choosing to forgo printing
and just look at the computer screen, will see it all messed up. Any

I tried to use "SAT-M" as y-axis label, but "M" was cut off (actually went to
second line that could not be seen). I tried every trick I know to lengthen
the text box, to fit label on one line.

I know it is impossible to use the chart tips in scatter graph to
display label unless we use add-in manager to import a VB module. How
much text box? For example, click on the point to pop up a text box
with label instead of using mouse over event to bring up chart tips. I
need VBA solution since security is an issue when I distribute Excel
files within the company. No add-in module is allowed.

I am attempting to merge a scroll bar and an activeX text box. I currently have a horizontal scroll bar that links to a cell with values from 1-20, A dynamic label in the adjacent cell changes when the scroll bar moves. I need the label to be inside the scroll bar track/tray. I was told an activeX text box would do the trick. However, i am new to VBA and activeX text boxes any code or solution to this problem is appreciated.

The slider and the track should overlay the text box label
The track should be transparent but on top of the label
The label should be a perfect fit to the slider's tray

Here is a reference to the scroll bar labels

I have posted an example file of what i am attempting to achieve.


-The Dude

Hi All:

A problem I keep encountering with charts is that when I put in text boxes with data in it and then try to copy it over to another spreadsheet I lose the text boxes but keep the graphs.

This means that I have to copy over the text boxes separately from the graph.

Is there anyway I can copy the graph with all of its text boxes in one shot as oppose to losing them when I hit copy, paste.

Thanks again for your great support,



I have some code (at the end of this message) that worked with Excel 2003 to assign a formula to a text box on a chart sheet. The code does not work with Excel 2007 and from the new Excel Object Model...I cannot find references to formulas with shape objects.

The overall purpose of my code is to allow the user to automatically generate a number of chart sheets with scatter plots and formatted title blocks. The title blocks contain information on the figure number, figure title, project, date, etc that is ready to be included in a report. The text boxes on the chart sheets are linked to cells on a "Figure Information" worksheet allowing the user to enter and update information for all charts from one worksheet.

linkform(1) = "'" & "Figure Information" & "'" & "!$" 
linkform(2) = NumbertoLetter(figinfo + 1) 
linkform(3) = "$5" 
ActiveChart.Shapes("Text Box 2").Select 
tbzorder = ActiveChart.Shapes("Text Box 2").ZOrderPosition 
finallink = Join(linkform, "") 
ActiveChart.TextBoxes(tbzorder).Formula = finallink

Any suggestions? Thanks!

Is it possible to display a cell value in a text box or a label (forms toolbar)?

When I change a chart from single level axis labels, to multiple level
(hierarchical) data labels, the data on my chart reverses direction.
(eg. originally the newest data is on the right, after I switch to multiple
level data labels, the newer data is on the left)

Is there any way to fix this?

I have created a chart in Excel and added a number of text boxes on top of
the chart.

I need to paste the entire chart & text boxes into Word and/or Powerpoint;
however by selecting the chart and copying, the text boxes are excluded.

MS help suggests entering the text boxes outside the chart then moving them
into the chart and grouping the chart and text boxes; however there does not
seem to be the ability to group the chart with the text boxes.

I have created a chart with a text box embedded in it successfully. Whan new
data is added, I need to move the text box further out (left) on the chart,
but after a certain point in the chart, the text box disappears. I have cut
& pasted while the chart is selected but still have the same issue.

With Excel 2003 I'm trying to print a Chart with text boxes on it (some are
text comments and others are data values).
I'm getting an unusual result. If I select the chart, the text boxes
with data values in them disappear and will not print, but the text boxes
with text stay visible and do print.
If I click in a worksheet cell off the chart, the data values in the
chart reappear...if I select cells behind and around the chart, I can print
the chart and the data values also print (but the resolution is lower). It's
only when I select the chart that the data values disappear.
I can't understand why when I select the chart, text boxes with text
remain visible but text boxes with data values disappear.
Does anyone have any ideas what I can do?
Dave Gowan

I used code

Sub AddHTMLCharts()
Dim chtobj As ChartObject
Dim strChartName As String
Dim vaDate As Variant
vaDate = Format(Date - 1, "yyyy-mm-dd")
Application.DisplayAlerts = False
For Each chtobj In ActiveSheet.ChartObjects
strChartName = chtobj.Name
ActiveWorkbook.PublishObjects.Add(xlSourceChart, _
"c:" _
& vaDate & "ABC" & strChartName & ".htm", _
"Charts", strChartName, xlHtmlStatic, strChartName, "").Publish
Next chtobj
Application.DisplayAlerts = True

to publish multiple charts in worksheet Charts in XL2000. Each chart
contains text boxes modified daily. When I upgraded to XL2003, the

Program was developed in Excel 2000 to publish charts to htm files. Each of
21 charts have text boxes that are updated with VBA code. When I upgraded to
Excel 2003, the code would publish each basic chart, but would generally omit
the text boxes. For example, if 21 charts were on a worksheet, one of the
htm files would have the text boxes but the other 20 would not.

The help files for publishobjects and publishobjects.add are the same for
2000 and 2003. Why doesn't the VBA code used in Excel 2000 work in Excel

I currently have a workbook with text boxes that are on several charts. The
text boxes contain a formula that references them to cell on a worksheet
named "Titles". This is where the user inputs text that is added to the
charts. I am trying to create a userform that will serve the same purpose.
The user will input text in the text box and this will then be input on the

So far I have created a user form that is called up by a menu on the
toolbar. There are 3 text boxes on the menu and I would like to link them
to the textboxes (shapes) on a chart. I don't know how to link them.



I am very new to VBA and macros but I understand the concept and I am using Excel 2007.

I am trying to do some conditional formatting for work but I am struggling as you cannot do that for text boxes.

I have a spreadsheet where I will input data. This is then translated to percentages which is then put into a one page report using text boxes.

What I want to be able to do is make a macro for a range of percentages which will in turn fill the background of the text box with a colour. depending on the percentage in a given cell. For instance:

100% - Green
80% to 99% - Amber
79% or less - Red

I have the developer tabs open and Active X with allowed access.

Any help would be much appreciated.

Kind Regards

In my macro, I display a form where the user enters data.
When they push the "submit" button, the data will be
displayed in a label or a text box on the form they are
using to enter data. This is not a problem.

The user has the ability to enter additional lines. The
problem is I don't know how many lines they will enter.
It may be 1 or 10 or 20. I was thinking about displaying
10 rows of data. The problem occurs if they enter more
than 10. How can I display all of the rows that they

Can I user scroll bars on a text box or label. I don't
necessarily have to have every row they enter visible but
they should have the ability to review all of the data
they entered.

Is a better way to build a combo box?

Any suggestions or ideas on the way to go would be great.
Thanks for your help.

Hi guys,

I have data like

Date C1 C2 C3 C4
4/20/2010 23 44 56 333
4/21/2010 23 45 57 334
4/22/2010 23 46 58 335
4/23/2010 23 47 59 336
4/26/2010 23 48 60 337
4/27/2010 23 49 61 338
4/28/2010 23 50 62 339
4/29/2010 23 51 63 340
4/30/2010 23 52 64 341

But the problem is when i make charts..Even when i select the x-axis labels as the "Date" field above,its showing Blanks on Dates 24,25 because as you can see from above data tat there is no data for 24,25 and only for 26 after 23..However,i want to show the above data as is without any blanks for 24,25 ...

Please help

Excel 2007
For Access 2007 Excel automation, tried to record a text box or label object.
The Macro Recorder just ignores the standard text box
and ignores most of the properties for an OLEObject

Have a wonderful report that needs to merge four columns.
If the Merge Cells are used, then the Advanced Filter won't work.
Trying to just past a Text Box or something over four cells.

If the Record Macro worked, I would be home free.

No luck finding an answer? You could always try Google.