Replace the existing value in a textbox in a scatter chart - using VBA?

    I am not good at Excel but I know Visual Basic v6. I want to write text to an existing textbox on an Excel scatter chart. Thr word I write depends on the result of a calculation in a sub in VBA module Macro1. One new word will replace the existing word.


    There is only one worksheet and one chart. How should I write to the existing textbox on the chart on the worksheet? I would appreciate knowing the Dim, Set, and assignment statements.


    There are two textboxes to change - "Textbox 1" and "Textbox 2". The chart name is "Chart 2". The worksheet tab name is "SamplingPlan"


    Using - MS Office Professional Plus 2016

    Hi,


    Have a try with Shapes ...


    Code
    Sub InsertText()
    Sheets("Sheet1").Shapes("TextBox 1").TextFrame.Characters.Text = "Insert the Text you want ..."
End Sub


    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

    Hello,


    Message you got only means you have to double-check all the names ...i.e. Sheet1 and TextBox 1....

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

    The name of the sheet on the tab is "SamplingPlans". When I replace "Sheet1" with "SamplingPlans", the error message changes from "subscript out of range" to "the item with the specified name wasn't found".


    Is there another name for the worksheet other than the name on the tab?

    Do I need to refer to the name of the chart that "TextBox 1" is on?

    Do I need to use Dim and/or Set?

    Hello,


    Is there a chance you could attach your file ...?

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

    Here is a bare bones version that can generate those errors.


    "change textbox on chart.xlsm"


    But when I try to upload it a message says "The file extension is invalid". What should I do?

    Quote from stanhilliard

    Here is a bare bones version that can generate those errors.


    "change textbox on chart.xlsm"


    But when I try to upload it a message says "The file extension is invalid". What should I do?


    Probably something to be fixed with the Brand NEW Forum :huh:


    Will report it to the administrator ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

    It looks like the List of Allowed extensions DOES NOT INCLUDE .xlsx nor .xlsm ...


    Agree with you ... rather strange ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

    Hello,


    Thanks for your file


    As indicated earlier ...you do need to adjust both your names ...


    Code
    Sub InsertText()
    Sheets("SamplingPlan").Shapes("Text6").TextFrame.Characters.Text = "Insert the Text you want ..."
End Sub


    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

    You could also link the textbox to a cell and then just update the cell.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

    Hello,


    Do not know if macro posted in message #14 is producing the expected result ...


    Could not make any test .. since the file you posted does crash my Excel ...:(

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

    I have succeeded in finding VBA code to change the textbox's text.


    Code
    Sub ChangeText()
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.Shapes.Range(Array("TextBox 1")).Select
Selection.ShapeRange(1).TextFrame2.TextRange.Characters.Text = "Accept"
Range("I19").Select
End Sub


    This replaced the text in the textbox with the word "Accept". I used Excel's macro recording feature followed by deleting unneeded code like setting the font properties. I added "Range("I19").Select" at the end because otherwise the textbox remained selected.

    All you should really need is:


    Code
    Sub ChangeText()
   ActiveSheet.ChartObjects("Chart 2").Chart.Shapes("TextBox 1").TextFrame2.TextRange.Characters.Text = "Accept"
End Sub

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login