autoshapes

[AmyTaylor]

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

[Chip Pearson]

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"
<[email protected]> wrote in
message
news:[email protected]...
>
> 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
>

[John Keith]

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
>
>

[AmyTaylor]

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

[Nick Hebb]

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

[Peter T]

> 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" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

[Peter T]

> 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" <[email protected]> wrote in message
news:[email protected]...
> 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
>

[Nick Hebb]

>> 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