Posts by stanhilliard

Thanks Justin, that is working, but I don't understand what With..End With does. For example, the following requires With..End Within order to work:

With samplingplan
      .Cells(R0 + Fixedn + 1, 5).Value = CumAverage 

End With

But the following works without With..End With:

Worksheets("samplingplan").Cells(11, 12) = MeanNow

What explains this, and what factors should I look for to recognize when I should use each method?

I would like to accomplish this:

#1) Set DataColumn = Range("D24:D41")

with something like this:

#2) Set DataColumn = Range(Cells(R0 + 1), Cells(R0 + Fixedn))

#1 works, #2 does not work.

where R0 and Fixedn contain integer values. I want to make this change is because the range of cells differs from time to time.

I tried the following, but it doesn't work either.

With Worksheets("samplingplan")
Set DataColumn = .Range(.Cells(R0 + 1), .Cells(R0 + Fixedn))
End With
DataColumn.ClearContents

How can I fix this?

Using VBA to add data to a worksheet, how do you decide between using Calls(row,column) vs [cell]?

What are the pros and cons?

My scatter plot's original legend text changed to "series" after manually dragging it.? How can I get it back to what it was?

Quote from S O

Thanks stanhilliard,

Can I ask how you arrived at that page? The issue should now be resolved but just curios as to what sent you to that URL?

I don't know but I think I was trying to get to the forum on the day that it was down or partially down. I might have been trying to log in while it was closed.

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

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.

I didn't see a place to post this so I post it here.

On https://www.ozgrid.com/forum/r…ee838267deb16eec6d97be103

I get the message from Kaspersky antivirus:

"malicious web page link https://www.ozgrid.com... threatens the security of your computer.

... is blocked"

I pasted a picture of a Kaspersky message below.

I think this xlsm file will upload now.

change textbox on chart.xlsm

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?

It is a complex simulation. I will make a trimmed down version and attach it. It is past my bedtime so it will be tomorrow.

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?

Thanks. I did that and got the error "subscript out of range"

I'm not sure, but I created the textboxes from the insert tab. I went to [text], [textbox] -- then drew it on the chart.

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