Sorry to post again - is it possible with the blockarrow autoshapes to
set the direction based on a value;

eg: up would be from a positive value
down would be from a negative value
and a value say between 10 and -10 would be an across the way arrow.

Hope this makes sense !!?

Thanks
love Amy xx


--
AmyTaylor
------------------------------------------------------------------------
AmyTaylor's Profile: http://www.excelforum.com/member.php...o&userid=20970
View this thread: http://www.excelforum.com/showthread...hreadid=400773

Amy,

Right click the sheet tab of the sheet containing the arrow and
choose View Code. Assuming the shape is named 'Arrow1', use the
following code. It will rotate the arrow based on the value in
cell A1.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address < "$A$1" Then
Exit Sub
End If

If Target.Value < 0 Then
Me.Shapes("Arrow1").Rotation = 90
Else
Me.Shapes("Arrow1").Rotation = -90
End If
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"AmyTaylor"
wrote in
message
...

Sorry to post again - is it possible with the blockarrow
autoshapes to
set the direction based on a value;

eg: up would be from a positive value
down would be from a negative value
and a value say between 10 and -10 would be an across the way
arrow.

Hope this makes sense !!?

Thanks
love Amy xx


--
AmyTaylor
------------------------------------------------------------------------
AmyTaylor's Profile:
http://www.excelforum.com/member.php...o&userid=20970
View this thread:
http://www.excelforum.com/showthread...hreadid=400773

Try maually adding a block arrow where you want the shape. Then rename the
shape to something like "VariableArrow"

From VBA code then select that shape with
ActiveSheet.Shapes("VariableArrow").Select
If Range("C3").value < 0 then
Selection.ShapeRange.IncrementRotation 180
end if

Repeat this style of code to cover all the directions you want to have the
arrow point.

For the double headed arrow... it is a diferent name
"msoShapeLeftRightArrow" so if your arrow can change from 2heads to 1head,
you may have to code the addshape to draw the style of arrow you want.

ActiveSheet.Shapes.AddShape(msoShapeRightArrow, 100, 100, 20, 20).Select

Try playing with the macro recorder while drawing the arrow and setting its
attributes to customize.


--
Regards,
John


"AmyTaylor" wrote:


Sorry to post again - is it possible with the blockarrow autoshapes to
set the direction based on a value;

eg: up would be from a positive value
down would be from a negative value
and a value say between 10 and -10 would be an across the way arrow.

Hope this makes sense !!?

Thanks
love Amy xx


--
AmyTaylor
------------------------------------------------------------------------
AmyTaylor's Profile: http://www.excelforum.com/member.php...o&userid=20970
View this thread: http://www.excelforum.com/showthread...hreadid=400773

Thanks for the replies:
Chip -
I have tried the code you mentioned, but it doesnt seem to change the
shape.
Any ideas what has gone wrong?

The shape is called Autoshape 6.

I have tried to adapt your code as follows:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address < "$e$4" Then
Exit Sub
End If

If Target.Text = "red" Then
Me.Shapes("Autoshape6").Rotation = 90
Else
If Target.Text = "amber" Then
Me.Shapes("Autoshape6").Rotation = 0
Else
Me.Shapes("Autoshape6").Rotation = -90
End If
End If
End Sub

And I have put this into the workbook tab vba.

Many thanks
Amy xx


--
AmyTaylor
------------------------------------------------------------------------
AmyTaylor's Profile: http://www.excelforum.com/member.php...o&userid=20970
View this thread: http://www.excelforum.com/showthread...hreadid=400773

Use IncrementRotation, as in:

ActiveSheet.Shapes("Autoshape 6").IncrementRotation -90#

And don't use "Me" - that's for forms and classes.

----
Nick Hebb
BreezeTree Software
http://www.breezetree.com/article_ex...wcharting1.htm

If Target.Address < "$e$4" Then

It's case sensitive so try changing to
If Target.Address < "$E$4" Then

Is your shape definately named "Autoshape6" and not "Autoshape 6"

And I have put this into the workbook tab vba.

I'm sure you mean the Worksheet module, as you get if you right click the
sheet tab and View code.

regards,
Peter T

"AmyTaylor" wrote
in message ...

Thanks for the replies:
Chip -
I have tried the code you mentioned, but it doesnt seem to change the
shape.
Any ideas what has gone wrong?

The shape is called Autoshape 6.

I have tried to adapt your code as follows:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address < "$e$4" Then
Exit Sub
End If

If Target.Text = "red" Then
Me.Shapes("Autoshape6").Rotation = 90
Else
If Target.Text = "amber" Then
Me.Shapes("Autoshape6").Rotation = 0
Else
Me.Shapes("Autoshape6").Rotation = -90
End If
End If
End Sub

And I have put this into the workbook tab vba.

Many thanks
Amy xx


--
AmyTaylor
------------------------------------------------------------------------
AmyTaylor's Profile:
http://www.excelforum.com/member.php...o&userid=20970
View this thread: http://www.excelforum.com/showthread...hreadid=400773

And don't use "Me" - that's for forms and classes.

As the OP's code is in a sheet class I think "Me" would be appropriate. The
event could be triggered whilst not the activesheet and Me would qualify to
the correct sheet, assuming of course the shape is on that sheet.

Regards,
Peter T

"Nick Hebb" wrote in message
ups.com...
Use IncrementRotation, as in:

ActiveSheet.Shapes("Autoshape 6").IncrementRotation -90#

And don't use "Me" - that's for forms and classes.

----
Nick Hebb
BreezeTree Software
http://www.breezetree.com/article_ex...wcharting1.htm

And don't use "Me" - that's for forms and classes.

As the OP's code is in a sheet class I think "Me" would be appropriate.

Hah! There's me thinking I know everything again and getting caught
being stupid. Thanks for the tip.

----
Nick Hebb
BreezeTree Software
http://www.breezetree.com/arti cle_excelflowcharting1.htm