Formating textbox on a chart

  • Thread starter Thread starter Roger B.
  • Start date Start date
R

Roger B.

I have a dollar amount created with the formula
=IF(NewProfit<0,"$"&FIXED(-NewProfit,-2),"$"&FIXED(NewProfit,-2)) in an
Excel spreadsheet which is linked to a textbox on a chart.

I would like to format the numbers in the chart 1) Bold and 2) Blue when
positive and Red when negative.



Any suggestion would be greatly appreciated,



Roger
 
You can just select the textbox and select Bold.

You don't need the IF to get conditional formatting or your dollar format.
The cell just needs this formula

=ROUND(NewProfit,-2)

(FIXED results in text, not a number) and a custom number format of

[blue]$#,##0;[red]$-#,##0;$0;@

Link the textbox to the cell, and apply the same number format to the
textbox.

More on number formats:

http://peltiertech.com/Excel/NumberFormats.html

- Jon
 
Jon, thanks so much for your update. It was very helpful. There is only
one problem: I can't get the "Format Cells, Number" when working in the
TextBox. Instead I get "Format TextBox" but can't find the numbers. Also
looked at your website but couldn't find the cure.
Any suggestions would be very helpful.
Thanks,
Roger


Jon Peltier said:
You can just select the textbox and select Bold.

You don't need the IF to get conditional formatting or your dollar format.
The cell just needs this formula

=ROUND(NewProfit,-2)

(FIXED results in text, not a number) and a custom number format of

[blue]$#,##0;[red]$-#,##0;$0;@

Link the textbox to the cell, and apply the same number format to the
textbox.

More on number formats:

http://peltiertech.com/Excel/NumberFormats.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


Roger B. said:
I have a dollar amount created with the formula
=IF(NewProfit<0,"$"&FIXED(-NewProfit,-2),"$"&FIXED(NewProfit,-2)) in an
Excel spreadsheet which is linked to a textbox on a chart.

I would like to format the numbers in the chart 1) Bold and 2) Blue when
positive and Red when negative.



Any suggestion would be greatly appreciated,



Roger
Click to expand...
Click to expand...
 
Hi,

I think maybe Jon was think of a data label, which does have a number
format.

For textboxes I think you will need to use 2 of everything.
2 formula to display the result
positive
=IF(ROUND(NewProfit,-2)>=0,ROUND(NewProfit,-2),"")

negative
=IF(ROUND(NewProfit,-2)<0,ROUND(NewProfit,-2),"")

and 2 textboxes with the correct formatting applied.

Cheers
Andy
Jon, thanks so much for your update. It was very helpful. There is only
one problem: I can't get the "Format Cells, Number" when working in the
TextBox. Instead I get "Format TextBox" but can't find the numbers. Also
looked at your website but couldn't find the cure.
Any suggestions would be very helpful.
Thanks,
Roger


You can just select the textbox and select Bold.

You don't need the IF to get conditional formatting or your dollar format.
The cell just needs this formula

=ROUND(NewProfit,-2)

(FIXED results in text, not a number) and a custom number format of

[blue]$#,##0;[red]$-#,##0;$0;@

Link the textbox to the cell, and apply the same number format to the
textbox.

More on number formats:

http://peltiertech.com/Excel/NumberFormats.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


I have a dollar amount created with the formula
=IF(NewProfit<0,"$"&FIXED(-NewProfit,-2),"$"&FIXED(NewProfit,-2)) in an
Excel spreadsheet which is linked to a textbox on a chart.

I would like to format the numbers in the chart 1) Bold and 2) Blue when
positive and Red when negative.



Any suggestion would be greatly appreciated,



Roger
Click to expand...
Click to expand...
Click to expand...
 
Sorry. I was thinking of textboxes, but I wasn't testing, just typing.
Otherwise I'd have noticed the lack of number formatting in a text box. My
Bad.

Jon Peltier, Microsoft Excel MVP
http://PeltierTech.com


Andy Pope said:
Hi,

I think maybe Jon was think of a data label, which does have a number
format.

For textboxes I think you will need to use 2 of everything.
2 formula to display the result
positive
=IF(ROUND(NewProfit,-2)>=0,ROUND(NewProfit,-2),"")

negative
=IF(ROUND(NewProfit,-2)<0,ROUND(NewProfit,-2),"")

and 2 textboxes with the correct formatting applied.

Cheers
Andy
Jon, thanks so much for your update. It was very helpful. There is only
one problem: I can't get the "Format Cells, Number" when working in the
TextBox. Instead I get "Format TextBox" but can't find the numbers.
Also looked at your website but couldn't find the cure.
Any suggestions would be very helpful.
Thanks,
Roger


You can just select the textbox and select Bold.

You don't need the IF to get conditional formatting or your dollar
format. The cell just needs this formula

=ROUND(NewProfit,-2)

(FIXED results in text, not a number) and a custom number format of

[blue]$#,##0;[red]$-#,##0;$0;@

Link the textbox to the cell, and apply the same number format to the
textbox.

More on number formats:

http://peltiertech.com/Excel/NumberFormats.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______



I have a dollar amount created with the formula
=IF(NewProfit<0,"$"&FIXED(-NewProfit,-2),"$"&FIXED(NewProfit,-2)) in an
Excel spreadsheet which is linked to a textbox on a chart.

I would like to format the numbers in the chart 1) Bold and 2) Blue when
positive and Red when negative.



Any suggestion would be greatly appreciated,



Roger
Click to expand...
Click to expand...
Click to expand...
 
Jon, am not sure of your conclusion. Is there any way of connecting the
Textbox to the spreadsheet with the colours showing in the Textbox?
Thanks,
Roger


Jon Peltier said:
Sorry. I was thinking of textboxes, but I wasn't testing, just typing.
Otherwise I'd have noticed the lack of number formatting in a text box.
My Bad.

Jon Peltier, Microsoft Excel MVP
http://PeltierTech.com


Andy Pope said:
Hi,

I think maybe Jon was think of a data label, which does have a number
format.

For textboxes I think you will need to use 2 of everything.
2 formula to display the result
positive
=IF(ROUND(NewProfit,-2)>=0,ROUND(NewProfit,-2),"")

negative
=IF(ROUND(NewProfit,-2)<0,ROUND(NewProfit,-2),"")

and 2 textboxes with the correct formatting applied.

Cheers
Andy
Jon, thanks so much for your update. It was very helpful. There is
only one problem: I can't get the "Format Cells, Number" when working
in the TextBox. Instead I get "Format TextBox" but can't find the
numbers. Also looked at your website but couldn't find the cure.
Any suggestions would be very helpful.
Thanks,
Roger



You can just select the textbox and select Bold.

You don't need the IF to get conditional formatting or your dollar
format. The cell just needs this formula

=ROUND(NewProfit,-2)

(FIXED results in text, not a number) and a custom number format of

[blue]$#,##0;[red]$-#,##0;$0;@

Link the textbox to the cell, and apply the same number format to the
textbox.

More on number formats:

http://peltiertech.com/Excel/NumberFormats.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______



I have a dollar amount created with the formula
=IF(NewProfit<0,"$"&FIXED(-NewProfit,-2),"$"&FIXED(NewProfit,-2)) in an
Excel spreadsheet which is linked to a textbox on a chart.

I would like to format the numbers in the chart 1) Bold and 2) Blue
when positive and Red when negative.



Any suggestion would be greatly appreciated,



Roger
Click to expand...
Click to expand...
Click to expand...
 
Here's what I did. I formatted the cell with the custom number format. I
copied the cell (C8 on Sheet1), selected the chart, held Shift while
selecting the Edit menu, and chose Paste Picture. I selected the picture,
clicked in the Formula bar, and typed =Sheet1!$C$8, a link to the cell. Now
the picture of the cell is dynamic, including the value and the format. I
don't know how stable this is. I seem to recall crashes using this kind of
technique in earlier versions of Excel (I'm using 2003).

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


Roger B. said:
Jon, am not sure of your conclusion. Is there any way of connecting the
Textbox to the spreadsheet with the colours showing in the Textbox?
Thanks,
Roger


Jon Peltier said:
Sorry. I was thinking of textboxes, but I wasn't testing, just typing.
Otherwise I'd have noticed the lack of number formatting in a text box.
My Bad.

Jon Peltier, Microsoft Excel MVP
http://PeltierTech.com


Andy Pope said:
Hi,

I think maybe Jon was think of a data label, which does have a number
format.

For textboxes I think you will need to use 2 of everything.
2 formula to display the result
positive
=IF(ROUND(NewProfit,-2)>=0,ROUND(NewProfit,-2),"")

negative
=IF(ROUND(NewProfit,-2)<0,ROUND(NewProfit,-2),"")

and 2 textboxes with the correct formatting applied.

Cheers
Andy

Roger B. wrote:
Jon, thanks so much for your update. It was very helpful. There is
only one problem: I can't get the "Format Cells, Number" when working
in the TextBox. Instead I get "Format TextBox" but can't find the
numbers. Also looked at your website but couldn't find the cure.
Any suggestions would be very helpful.
Thanks,
Roger



You can just select the textbox and select Bold.

You don't need the IF to get conditional formatting or your dollar
format. The cell just needs this formula

=ROUND(NewProfit,-2)

(FIXED results in text, not a number) and a custom number format of

[blue]$#,##0;[red]$-#,##0;$0;@

Link the textbox to the cell, and apply the same number format to the
textbox.

More on number formats:

http://peltiertech.com/Excel/NumberFormats.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______



I have a dollar amount created with the formula
=IF(NewProfit<0,"$"&FIXED(-NewProfit,-2),"$"&FIXED(NewProfit,-2)) in
an Excel spreadsheet which is linked to a textbox on a chart.

I would like to format the numbers in the chart 1) Bold and 2) Blue
when positive and Red when negative.



Any suggestion would be greatly appreciated,



Roger
Click to expand...
Click to expand...
Click to expand...
 
Jon, thanks for the great instructions. It all works except that I can't
get the size of the picture to adjust to the size I want. I have also seen
a crash but that was caused by moving things around so hopefully it will
stay OK when not moved about.

I really appreciate your detailed reply,

Roger



Jon Peltier said:
Here's what I did. I formatted the cell with the custom number format. I
copied the cell (C8 on Sheet1), selected the chart, held Shift while
selecting the Edit menu, and chose Paste Picture. I selected the picture,
clicked in the Formula bar, and typed =Sheet1!$C$8, a link to the cell.
Now the picture of the cell is dynamic, including the value and the
format. I don't know how stable this is. I seem to recall crashes using
this kind of technique in earlier versions of Excel (I'm using 2003).

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


Roger B. said:
Jon, am not sure of your conclusion. Is there any way of connecting the
Textbox to the spreadsheet with the colours showing in the Textbox?
Thanks,
Roger


Jon Peltier said:
Sorry. I was thinking of textboxes, but I wasn't testing, just typing.
Otherwise I'd have noticed the lack of number formatting in a text box.
My Bad.

Jon Peltier, Microsoft Excel MVP
http://PeltierTech.com


Hi,

I think maybe Jon was think of a data label, which does have a number
format.

For textboxes I think you will need to use 2 of everything.
2 formula to display the result
positive
=IF(ROUND(NewProfit,-2)>=0,ROUND(NewProfit,-2),"")

negative
=IF(ROUND(NewProfit,-2)<0,ROUND(NewProfit,-2),"")

and 2 textboxes with the correct formatting applied.

Cheers
Andy

Roger B. wrote:
Jon, thanks so much for your update. It was very helpful. There is
only one problem: I can't get the "Format Cells, Number" when working
in the TextBox. Instead I get "Format TextBox" but can't find the
numbers. Also looked at your website but couldn't find the cure.
Any suggestions would be very helpful.
Thanks,
Roger



You can just select the textbox and select Bold.

You don't need the IF to get conditional formatting or your dollar
format. The cell just needs this formula

=ROUND(NewProfit,-2)

(FIXED results in text, not a number) and a custom number format of

[blue]$#,##0;[red]$-#,##0;$0;@

Link the textbox to the cell, and apply the same number format to the
textbox.

More on number formats:

http://peltiertech.com/Excel/NumberFormats.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______



I have a dollar amount created with the formula
=IF(NewProfit<0,"$"&FIXED(-NewProfit,-2),"$"&FIXED(NewProfit,-2)) in
an Excel spreadsheet which is linked to a textbox on a chart.

I would like to format the numbers in the chart 1) Bold and 2) Blue
when positive and Red when negative.



Any suggestion would be greatly appreciated,



Roger
Click to expand...
Click to expand...
Click to expand...
 
Roger -

Email me in a month and let me know if it still seems sufficiently stable.
It does seem less shaky in 2003 than in 2000, when I recall having some nice
crashes using this technique.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


Roger B. said:
Jon, thanks for the great instructions. It all works except that I can't
get the size of the picture to adjust to the size I want. I have also
seen a crash but that was caused by moving things around so hopefully it
will stay OK when not moved about.

I really appreciate your detailed reply,

Roger



Jon Peltier said:
Here's what I did. I formatted the cell with the custom number format. I
copied the cell (C8 on Sheet1), selected the chart, held Shift while
selecting the Edit menu, and chose Paste Picture. I selected the picture,
clicked in the Formula bar, and typed =Sheet1!$C$8, a link to the cell.
Now the picture of the cell is dynamic, including the value and the
format. I don't know how stable this is. I seem to recall crashes using
this kind of technique in earlier versions of Excel (I'm using 2003).

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


Roger B. said:
Jon, am not sure of your conclusion. Is there any way of connecting the
Textbox to the spreadsheet with the colours showing in the Textbox?
Thanks,
Roger


Sorry. I was thinking of textboxes, but I wasn't testing, just typing.
Otherwise I'd have noticed the lack of number formatting in a text box.
My Bad.

Jon Peltier, Microsoft Excel MVP
http://PeltierTech.com


Hi,

I think maybe Jon was think of a data label, which does have a number
format.

For textboxes I think you will need to use 2 of everything.
2 formula to display the result
positive
=IF(ROUND(NewProfit,-2)>=0,ROUND(NewProfit,-2),"")

negative
=IF(ROUND(NewProfit,-2)<0,ROUND(NewProfit,-2),"")

and 2 textboxes with the correct formatting applied.

Cheers
Andy

Roger B. wrote:
Jon, thanks so much for your update. It was very helpful. There is
only one problem: I can't get the "Format Cells, Number" when
working in the TextBox. Instead I get "Format TextBox" but can't
find the numbers. Also looked at your website but couldn't find the
cure.
Any suggestions would be very helpful.
Thanks,
Roger



You can just select the textbox and select Bold.

You don't need the IF to get conditional formatting or your dollar
format. The cell just needs this formula

=ROUND(NewProfit,-2)

(FIXED results in text, not a number) and a custom number format of

[blue]$#,##0;[red]$-#,##0;$0;@

Link the textbox to the cell, and apply the same number format to the
textbox.

More on number formats:

http://peltiertech.com/Excel/NumberFormats.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______



I have a dollar amount created with the formula
=IF(NewProfit<0,"$"&FIXED(-NewProfit,-2),"$"&FIXED(NewProfit,-2)) in
an Excel spreadsheet which is linked to a textbox on a chart.

I would like to format the numbers in the chart 1) Bold and 2) Blue
when positive and Red when negative.



Any suggestion would be greatly appreciated,



Roger
Click to expand...
Click to expand...
Click to expand...
 
Will do,

Roger


Jon Peltier said:
Roger -

Email me in a month and let me know if it still seems sufficiently stable.
It does seem less shaky in 2003 than in 2000, when I recall having some
nice crashes using this technique.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


Roger B. said:
Jon, thanks for the great instructions. It all works except that I can't
get the size of the picture to adjust to the size I want. I have also
seen a crash but that was caused by moving things around so hopefully it
will stay OK when not moved about.

I really appreciate your detailed reply,

Roger



Jon Peltier said:
Here's what I did. I formatted the cell with the custom number format. I
copied the cell (C8 on Sheet1), selected the chart, held Shift while
selecting the Edit menu, and chose Paste Picture. I selected the
picture, clicked in the Formula bar, and typed =Sheet1!$C$8, a link to
the cell. Now the picture of the cell is dynamic, including the value
and the format. I don't know how stable this is. I seem to recall
crashes using this kind of technique in earlier versions of Excel (I'm
using 2003).

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


Jon, am not sure of your conclusion. Is there any way of connecting
the Textbox to the spreadsheet with the colours showing in the Textbox?
Thanks,
Roger


Sorry. I was thinking of textboxes, but I wasn't testing, just typing.
Otherwise I'd have noticed the lack of number formatting in a text
box. My Bad.

Jon Peltier, Microsoft Excel MVP
http://PeltierTech.com


Hi,

I think maybe Jon was think of a data label, which does have a number
format.

For textboxes I think you will need to use 2 of everything.
2 formula to display the result
positive
=IF(ROUND(NewProfit,-2)>=0,ROUND(NewProfit,-2),"")

negative
=IF(ROUND(NewProfit,-2)<0,ROUND(NewProfit,-2),"")

and 2 textboxes with the correct formatting applied.

Cheers
Andy

Roger B. wrote:
Jon, thanks so much for your update. It was very helpful. There is
only one problem: I can't get the "Format Cells, Number" when
working in the TextBox. Instead I get "Format TextBox" but can't
find the numbers. Also looked at your website but couldn't find the
cure.
Any suggestions would be very helpful.
Thanks,
Roger



You can just select the textbox and select Bold.

You don't need the IF to get conditional formatting or your dollar
format. The cell just needs this formula

=ROUND(NewProfit,-2)

(FIXED results in text, not a number) and a custom number format of

[blue]$#,##0;[red]$-#,##0;$0;@

Link the textbox to the cell, and apply the same number format to
the textbox.

More on number formats:

http://peltiertech.com/Excel/NumberFormats.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______



I have a dollar amount created with the formula
=IF(NewProfit<0,"$"&FIXED(-NewProfit,-2),"$"&FIXED(NewProfit,-2))
in an Excel spreadsheet which is linked to a textbox on a chart.

I would like to format the numbers in the chart 1) Bold and 2) Blue
when positive and Red when negative.



Any suggestion would be greatly appreciated,



Roger
Click to expand...
Click to expand...
Click to expand...
 
Hi Jon, I lost my msnews for several months so could not answer your
question sooner. I got the "Formating textbox on a chart" to work initially
and it has worked really well since then.
Many thanks for all your kind help.
I'll send this to you personally as well as on the newsgroup we were using.
Roger



Roger B. said:
Will do,

Roger


Jon Peltier said:
Roger -

Email me in a month and let me know if it still seems sufficiently
stable. It does seem less shaky in 2003 than in 2000, when I recall
having some nice crashes using this technique.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


Roger B. said:
Jon, thanks for the great instructions. It all works except that I
can't get the size of the picture to adjust to the size I want. I have
also seen a crash but that was caused by moving things around so
hopefully it will stay OK when not moved about.

I really appreciate your detailed reply,

Roger



Here's what I did. I formatted the cell with the custom number format.
I copied the cell (C8 on Sheet1), selected the chart, held Shift while
selecting the Edit menu, and chose Paste Picture. I selected the
picture, clicked in the Formula bar, and typed =Sheet1!$C$8, a link to
the cell. Now the picture of the cell is dynamic, including the value
and the format. I don't know how stable this is. I seem to recall
crashes using this kind of technique in earlier versions of Excel (I'm
using 2003).

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


Jon, am not sure of your conclusion. Is there any way of connecting
the Textbox to the spreadsheet with the colours showing in the
Textbox?
Thanks,
Roger


Sorry. I was thinking of textboxes, but I wasn't testing, just
typing. Otherwise I'd have noticed the lack of number formatting in a
text box. My Bad.

Jon Peltier, Microsoft Excel MVP
http://PeltierTech.com


Hi,

I think maybe Jon was think of a data label, which does have a
number format.

For textboxes I think you will need to use 2 of everything.
2 formula to display the result
positive
=IF(ROUND(NewProfit,-2)>=0,ROUND(NewProfit,-2),"")

negative
=IF(ROUND(NewProfit,-2)<0,ROUND(NewProfit,-2),"")

and 2 textboxes with the correct formatting applied.

Cheers
Andy

Roger B. wrote:
Jon, thanks so much for your update. It was very helpful. There
is only one problem: I can't get the "Format Cells, Number" when
working in the TextBox. Instead I get "Format TextBox" but can't
find the numbers. Also looked at your website but couldn't find the
cure.
Any suggestions would be very helpful.
Thanks,
Roger



You can just select the textbox and select Bold.

You don't need the IF to get conditional formatting or your dollar
format. The cell just needs this formula

=ROUND(NewProfit,-2)

(FIXED results in text, not a number) and a custom number format of

[blue]$#,##0;[red]$-#,##0;$0;@

Link the textbox to the cell, and apply the same number format to
the textbox.

More on number formats:

http://peltiertech.com/Excel/NumberFormats.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______



I have a dollar amount created with the formula
=IF(NewProfit<0,"$"&FIXED(-NewProfit,-2),"$"&FIXED(NewProfit,-2))
in an Excel spreadsheet which is linked to a textbox on a chart.

I would like to format the numbers in the chart 1) Bold and 2)
Blue when positive and Red when negative.



Any suggestion would be greatly appreciated,



Roger
Click to expand...
Click to expand...
Click to expand...
 
Back
Top