none
function to add a textbox in Excel 2007 VBA macro?

    Question

  • I need to add textboxes to charts, with the contents of the box = the text in a cell

     

    In Excel 2003 you could use something like

    ActiveChart.Shapes.AddTextbox

    Selection.Formula = "Main!$A$1"

    But Excel 2007 crashes on the second line, and won't even record the keystrokes if you try to record the macro from scratch.

     

    Deep in TechNet there's a note that

    "Office Excel 2007 no longer saves VBA code that includes nothing but comments and declaration statements. Excel VBA code that is attached to a workbook and that contains nothing but comments and declaration statements is neither loaded nor saved with the file. Very few workbooks are affected by this change. Users can work around this issue by adding a subroutine or function to the Excel VBA code."

    (http://technet.microsoft.com/en-us/library/cc179167.aspx#whatschanged18)

     

    I've been told that textboxes are affected by this.

     

    Anyone know how I can "work around this issue by adding a subroutine or function to the Excel VBA code"?

     

    Thx

    Monday, October 06, 2008 4:31 PM

Answers

  • Hi,

    Try this code, which will need to be stored in a macro enabled workbook (.xlsm)


    Code Snippet

    Sub Macro1()
    '
    ' Macro1 Macro
    '
    Dim chtTemp As Chart
    Dim objTemp As Object

    Set chtTemp = ActiveSheet.ChartObjects(1).Chart
    Set objTemp = chtTemp.Shapes.AddTextbox(msoTextOrientationHorizontal, 1, 1, 150, 20)

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

    ActiveCell.Select

    End Sub



    Tuesday, October 07, 2008 12:31 PM

All replies

  • Hi,

    Try this code, which will need to be stored in a macro enabled workbook (.xlsm)


    Code Snippet

    Sub Macro1()
    '
    ' Macro1 Macro
    '
    Dim chtTemp As Chart
    Dim objTemp As Object

    Set chtTemp = ActiveSheet.ChartObjects(1).Chart
    Set objTemp = chtTemp.Shapes.AddTextbox(msoTextOrientationHorizontal, 1, 1, 150, 20)

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

    ActiveCell.Select

    End Sub



    Tuesday, October 07, 2008 12:31 PM
  •  

    EXCELLENT! Thanks very much, it worked perfectly! I'm sure this is exactly what I need.

     

    My charts are all separate sheets, so I changed the code to read

    Set chtTemp = ActiveChart

     

    Thanks again.

    Wednesday, October 08, 2008 6:52 PM
  • Saw this code and it helped me a lot. **** MANY THANKS ****

    Now I have an additional problem. TextBox text changes a lot, and I need to know how to change text for the textbox I already have in my chart, and also how to delete some specific texbox.

    Nowadays, I'm updating textbox text manually :(

    Hope someone could help me.

    Thursday, April 14, 2011 5:23 PM
  • If the textbox text is actually a formula that refers to a particular cell, it should update automatically whenever the cell content changes.

    Saw this code and it helped me a lot. **** MANY THANKS ****

    Now I have an additional problem. TextBox text changes a lot, and I need to know how to change text for the textbox I already have in my chart, and also how to delete some specific texbox.

    Nowadays, I'm updating textbox text manually :(

    Hope someone could help me.


    Tushar Mehta (Technology and Operations Consulting)
    www.tushar-mehta.com (Excel and PowerPoint add-ins and tutorials)
    Microsoft MVP Excel 2000-Present
    Friday, April 15, 2011 1:57 AM
  • All,

    I have a slightly different problem. I have numerous text boxes on a chart which are all linked via formulas to a sheet. I find that while some do indeed update when the data changes, many seem to "lose" their connection and require manually entering and refreshing the text box by highlighing the formula and pressing enter which, while it does update the text box, it also then resets all the formatting to orginal fonts, colors, ,etc. Very frustrating.

    I'm using the text boxes to display ancillary data on a side-by-side stacked bar (thanks Dave Peltier), and since it is that type of a chart, the data table cannot be used (the data is offest to allow for the special formatting of the side-by-side stacked bar).

    1) does anyone know about the text box "losing" it's connection (I can't find anything on it anywhere)

    2) does anyone have a suggestion for a better way to embed updatable data on a chart other thatn the method I'm using above?

    Thanks in advance...Skip

    Thursday, June 09, 2011 3:04 PM
  • All,

    I have a slightly different problem. I have numerous text boxes on a chart which are all linked via formulas to a sheet. I find that while some do indeed update when the data changes, many seem to "lose" their connection and require manually entering and refreshing the text box by highlighing the formula and pressing enter which, while it does update the text box, it also then resets all the formatting to orginal fonts, colors, ,etc. Very frustrating.

    I'm using the text boxes to display ancillary data on a side-by-side stacked bar (thanks Dave Peltier), and since it is that type of a chart, the data table cannot be used (the data is offest to allow for the special formatting of the side-by-side stacked bar).

    1) does anyone know about the text box "losing" it's connection (I can't find anything on it anywhere)

    2) does anyone have a suggestion for a better way to embed updatable data on a chart other thatn the method I'm using above?

    Thanks in advance...Skip


    If you are looking for an alternative to a data table, consider simulating one. See

    Custom Chart Table

    http://www.tushar-mehta.com/excel/newsgroups/data_table/

     


    Tushar Mehta (Technology and Operations Consulting)
    www.tushar-mehta.com (Excel and PowerPoint add-ins and tutorials)
    Microsoft MVP Excel 2000-Present
    Friday, June 10, 2011 4:50 AM