Text boxs

[ryan.fitzpatrick3]

Two questions:

1) Is there a way to convert a number into text while keeping the
comma's? I know I can simply select cell/range and go to cell format
and select text to convert it, but it loses the commas. This leads
into my 2nd question.

2) I have a text box on a chart that imports a certain cell data. I
would like the text box to be permanently placed within a chart, so if
I change the size of the chart it stays in its relative position.

Or if you want to take a crack at it I have a chart example workbook I
can send you. I think I have too much information in this one chart so
this is why I have to add a text box into the chart.

ryan

 

[JLGWhiz]

You can use CStr function to coerce a number to a string:

x = CStr(Range("A1"))

It still looks like an integer but it takes on string characteristics.

 

[JLGWhiz]

I believe that the left and top properties apply to the form that a textbox
is on, or to the sheet it is on, if not on a form. That would mean that you
could place it in a position on the chart sheet but would have to consider
its location when adjusting the chart area size or configuration. I don't
know if it can be embedded into the chart itself. Maybe someone smarter than
me can tell you that.

 

[Rick Rothstein \(MVP - VB\)]

1) Is there a way to convert a number into text while keeping the

comma's? I know I can simply select cell/range and go to cell format
and select text to convert it, but it loses the commas. This leads
into my 2nd question.

You can use the Format function to do that (it is similar to the TEXT
function in a spreadsheet formula). There are several ways to set up the
Format function's pattern string, so you should probably check the help
files. However, here are some examples...

Integers
===========
Value = Format$(YourNumber, "#,##0")

Three decimal places
=====================
Value = Format$(YourNumber, "#,##0.000")

Currency
===========
Value = Format$(YourNumber, "Currency")

Be aware, however, that the Value variable should be declared as a String;
if you use the $ sign on the Format function like I did, you should be able
to get away with a Variant; but under no circumstances should Value be
declared as a number type... numbers are numbers and they cannot hold a
format.

Rick