Automatically changing arrows
 

I have a report for executives where I have a big "up" arrow if there was an
increase and a big "down" arrow if the amount decreased. Currently I have to
change these by hand each month. How do you automate the arrow to change
based on whether a referenced cell is pos. or neg.?

Thanks!

Automatically changing arrows
 

How have you created the big arrow and where is it located?

I am sure your request could be accommodated with some more detail.

One example.............in an adjacent cell enter this formula.

=IF(H5<0,CHAR(234),IF(H50,CHAR(233),CHAR(232)))

Format the cell to Wingdings font.

Neg will show down arrow(234), positive an up arrow(233), no change a
right arrow(232)


Gord Dibben MS Excel MVP

On Mon, 8 Dec 2008 12:52:10 -0800, Nelson B.
wrote:

I have a report for executives where I have a big "up" arrow if there was an
increase and a big "down" arrow if the amount decreased. Currently I have to
change these by hand each month. How do you automate the arrow to change
based on whether a referenced cell is pos. or neg.?

Thanks!

Automatically changing arrows
 

I created the arrow simply by inserting it as a shape. I have since changed
the colors and size for both the pos. arrow and the neg. If I have an arrow
inserted in cell b2 and a different one in c2, is it possible to have one
display for a positive result and the other for a negative?

Thanks!

"Gord Dibben" wrote:

How have you created the big arrow and where is it located?

I am sure your request could be accommodated with some more detail.

One example.............in an adjacent cell enter this formula.

=IF(H5<0,CHAR(234),IF(H50,CHAR(233),CHAR(232)))

Format the cell to Wingdings font.

Neg will show down arrow(234), positive an up arrow(233), no change a
right arrow(232)


Gord Dibben MS Excel MVP

On Mon, 8 Dec 2008 12:52:10 -0800, Nelson B.
wrote:

I have a report for executives where I have a big "up" arrow if there was an
increase and a big "down" arrow if the amount decreased. Currently I have to
change these by hand each month. How do you automate the arrow to change
based on whether a referenced cell is pos. or neg.?

Thanks!

Automatically changing arrows
 

You cannot "insert" an object into a cell..........only lay it on top of a
cell.

Assuming you have two arrows from the Drawing Toolbar AutoshapesBlock
Arrows sitting on your sheet on top of B2 and C2 or wherever you wish.

Right-click on the sheet tab and "View Code". Copy/paste this code into
that module.

You may have to edit the shapes(no.). My up arrow is shape1 and down arrow
is shape2. Select a shape and look in NameBox for shape name/number in
your sheet.

Code assumes a formula in A1 with positive or negative value. No arrow for
0 value. Edit the Case is to give 0 a pos or neg value if you want.

Private Sub Worksheet_Calculate()
Dim shp As Object
On Error GoTo stoppit
Application.EnableEvents = False
For Each shp In Me.Shapes
shp.Visible = False
Next shp
With Me.Range("A1")
Select Case .Value
Case Is 0: Me.Shapes(1).Visible = msoCTrue
Case Is < 0: Me.Shapes(2).Visible = msoCTrue
End Select
End With
stoppit:
Application.EnableEvents = True
End Sub


Gord

On Wed, 10 Dec 2008 12:20:01 -0800, Nelson B.
wrote:

I created the arrow simply by inserting it as a shape. I have since changed
the colors and size for both the pos. arrow and the neg. If I have an arrow
inserted in cell b2 and a different one in c2, is it possible to have one
display for a positive result and the other for a negative?

Thanks!

"Gord Dibben" wrote:

How have you created the big arrow and where is it located?

I am sure your request could be accommodated with some more detail.

One example.............in an adjacent cell enter this formula.

=IF(H5<0,CHAR(234),IF(H50,CHAR(233),CHAR(232)))

Format the cell to Wingdings font.

Neg will show down arrow(234), positive an up arrow(233), no change a
right arrow(232)


Gord Dibben MS Excel MVP

On Mon, 8 Dec 2008 12:52:10 -0800, Nelson B.
wrote:

I have a report for executives where I have a big "up" arrow if there was an
increase and a big "down" arrow if the amount decreased. Currently I have to
change these by hand each month. How do you automate the arrow to change
based on whether a referenced cell is pos. or neg.?

Thanks!

Automatically changing arrows
 

Thanks for your help! Much appreciated.

"Gord Dibben" wrote:

You cannot "insert" an object into a cell..........only lay it on top of a
cell.

Assuming you have two arrows from the Drawing Toolbar AutoshapesBlock
Arrows sitting on your sheet on top of B2 and C2 or wherever you wish.

Right-click on the sheet tab and "View Code". Copy/paste this code into
that module.

You may have to edit the shapes(no.). My up arrow is shape1 and down arrow
is shape2. Select a shape and look in NameBox for shape name/number in
your sheet.

Code assumes a formula in A1 with positive or negative value. No arrow for
0 value. Edit the Case is to give 0 a pos or neg value if you want.

Private Sub Worksheet_Calculate()
Dim shp As Object
On Error GoTo stoppit
Application.EnableEvents = False
For Each shp In Me.Shapes
shp.Visible = False
Next shp
With Me.Range("A1")
Select Case .Value
Case Is 0: Me.Shapes(1).Visible = msoCTrue
Case Is < 0: Me.Shapes(2).Visible = msoCTrue
End Select
End With
stoppit:
Application.EnableEvents = True
End Sub


Gord

On Wed, 10 Dec 2008 12:20:01 -0800, Nelson B.
wrote:

I created the arrow simply by inserting it as a shape. I have since changed
the colors and size for both the pos. arrow and the neg. If I have an arrow
inserted in cell b2 and a different one in c2, is it possible to have one
display for a positive result and the other for a negative?

Thanks!

"Gord Dibben" wrote:

How have you created the big arrow and where is it located?

I am sure your request could be accommodated with some more detail.

One example.............in an adjacent cell enter this formula.

=IF(H5<0,CHAR(234),IF(H50,CHAR(233),CHAR(232)))

Format the cell to Wingdings font.

Neg will show down arrow(234), positive an up arrow(233), no change a
right arrow(232)


Gord Dibben MS Excel MVP

On Mon, 8 Dec 2008 12:52:10 -0800, Nelson B.
wrote:

I have a report for executives where I have a big "up" arrow if there was an
increase and a big "down" arrow if the amount decreased. Currently I have to
change these by hand each month. How do you automate the arrow to change
based on whether a referenced cell is pos. or neg.?

Thanks!

Automatically changing arrows
 

Hi again. Is there anyway I could send you an example of what I want? I
can't attach anything to these responses. I'm not sure I follow your last
reply. I tried what you said, but it dumped me into visual basic- something
I'm not familiar with.

"Gord Dibben" wrote:

You cannot "insert" an object into a cell..........only lay it on top of a
cell.

Assuming you have two arrows from the Drawing Toolbar AutoshapesBlock
Arrows sitting on your sheet on top of B2 and C2 or wherever you wish.

Right-click on the sheet tab and "View Code". Copy/paste this code into
that module.

You may have to edit the shapes(no.). My up arrow is shape1 and down arrow
is shape2. Select a shape and look in NameBox for shape name/number in
your sheet.

Code assumes a formula in A1 with positive or negative value. No arrow for
0 value. Edit the Case is to give 0 a pos or neg value if you want.

Private Sub Worksheet_Calculate()
Dim shp As Object
On Error GoTo stoppit
Application.EnableEvents = False
For Each shp In Me.Shapes
shp.Visible = False
Next shp
With Me.Range("A1")
Select Case .Value
Case Is 0: Me.Shapes(1).Visible = msoCTrue
Case Is < 0: Me.Shapes(2).Visible = msoCTrue
End Select
End With
stoppit:
Application.EnableEvents = True
End Sub


Gord

On Wed, 10 Dec 2008 12:20:01 -0800, Nelson B.
wrote:

I created the arrow simply by inserting it as a shape. I have since changed
the colors and size for both the pos. arrow and the neg. If I have an arrow
inserted in cell b2 and a different one in c2, is it possible to have one
display for a positive result and the other for a negative?

Thanks!

"Gord Dibben" wrote:

How have you created the big arrow and where is it located?

I am sure your request could be accommodated with some more detail.

One example.............in an adjacent cell enter this formula.

=IF(H5<0,CHAR(234),IF(H50,CHAR(233),CHAR(232)))

Format the cell to Wingdings font.

Neg will show down arrow(234), positive an up arrow(233), no change a
right arrow(232)


Gord Dibben MS Excel MVP

On Mon, 8 Dec 2008 12:52:10 -0800, Nelson B.
wrote:

I have a report for executives where I have a big "up" arrow if there was an
increase and a big "down" arrow if the amount decreased. Currently I have to
change these by hand each month. How do you automate the arrow to change
based on whether a referenced cell is pos. or neg.?

Thanks!

Automatically changing arrows
 

Send the workbook to me personally.

Change the AT and DOT in my posting email to send to me.


Gord

On Thu, 11 Dec 2008 05:53:01 -0800, Nelson B.
wrote:

Hi again. Is there anyway I could send you an example of what I want? I
can't attach anything to these responses. I'm not sure I follow your last
reply. I tried what you said, but it dumped me into visual basic- something
I'm not familiar with.

"Gord Dibben" wrote:

You cannot "insert" an object into a cell..........only lay it on top of a
cell.

Assuming you have two arrows from the Drawing Toolbar AutoshapesBlock
Arrows sitting on your sheet on top of B2 and C2 or wherever you wish.

Right-click on the sheet tab and "View Code". Copy/paste this code into
that module.

You may have to edit the shapes(no.). My up arrow is shape1 and down arrow
is shape2. Select a shape and look in NameBox for shape name/number in
your sheet.

Code assumes a formula in A1 with positive or negative value. No arrow for
0 value. Edit the Case is to give 0 a pos or neg value if you want.

Private Sub Worksheet_Calculate()
Dim shp As Object
On Error GoTo stoppit
Application.EnableEvents = False
For Each shp In Me.Shapes
shp.Visible = False
Next shp
With Me.Range("A1")
Select Case .Value
Case Is 0: Me.Shapes(1).Visible = msoCTrue
Case Is < 0: Me.Shapes(2).Visible = msoCTrue
End Select
End With
stoppit:
Application.EnableEvents = True
End Sub


Gord

On Wed, 10 Dec 2008 12:20:01 -0800, Nelson B.
wrote:

I created the arrow simply by inserting it as a shape. I have since changed
the colors and size for both the pos. arrow and the neg. If I have an arrow
inserted in cell b2 and a different one in c2, is it possible to have one
display for a positive result and the other for a negative?

Thanks!

"Gord Dibben" wrote:

How have you created the big arrow and where is it located?

I am sure your request could be accommodated with some more detail.

One example.............in an adjacent cell enter this formula.

=IF(H5<0,CHAR(234),IF(H50,CHAR(233),CHAR(232)))

Format the cell to Wingdings font.

Neg will show down arrow(234), positive an up arrow(233), no change a
right arrow(232)


Gord Dibben MS Excel MVP

On Mon, 8 Dec 2008 12:52:10 -0800, Nelson B.
wrote:

I have a report for executives where I have a big "up" arrow if there was an
increase and a big "down" arrow if the amount decreased. Currently I have to
change these by hand each month. How do you automate the arrow to change
based on whether a referenced cell is pos. or neg.?

Thanks!