Is it possible to automatically resize a shape (eg arrow) based on a value.
If I change a value the shape will automatically change to reflect this.

On Wed, 26 Mar 2008 09:08:02 -0700, MichaelSpy2008
wrote:

Is it possible to automatically resize a shape (eg arrow) based on a value.
If I change a value the shape will automatically change to reflect this.

Try this:
(change "A1" to the cell where your value is and "Right Arrow 1" to
the name of your shape)

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1")) Is Nothing Then
Shapes("Right Arrow 1").Width = Target.Value
End If
End Sub

Hope this helps / Lars-Åke

Thanks for that guys but I can't get it to work, any chance of making it
clearer for a dummy?!

"Lars-Ã…ke Aspelin" wrote:

On Wed, 26 Mar 2008 09:08:02 -0700, MichaelSpy2008
wrote:

Is it possible to automatically resize a shape (eg arrow) based on a value.
If I change a value the shape will automatically change to reflect this.

Try this:
(change "A1" to the cell where your value is and "Right Arrow 1" to
the name of your shape)

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1")) Is Nothing Then
Shapes("Right Arrow 1").Width = Target.Value
End If
End Sub

Hope this helps / Lars-Ã…ke

I have tried this and I get a argument not optional error message, Can you
help please. Thanks

"Lars-Ã…ke Aspelin" wrote:

On Wed, 26 Mar 2008 09:08:02 -0700, MichaelSpy2008
wrote:

Is it possible to automatically resize a shape (eg arrow) based on a value.
If I change a value the shape will automatically change to reflect this.

Try this:
(change "A1" to the cell where your value is and "Right Arrow 1" to
the name of your shape)

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1")) Is Nothing Then
Shapes("Right Arrow 1").Width = Target.Value
End If
End Sub

Hope this helps / Lars-Ã…ke

On Thu, 27 Mar 2008 02:41:05 -0700, MichaelSpy2008
wrote:

I have tried this and I get a argument not optional error message, Can you
help please. Thanks

"Lars-Åke Aspelin" wrote:

On Wed, 26 Mar 2008 09:08:02 -0700, MichaelSpy2008
wrote:

Is it possible to automatically resize a shape (eg arrow) based on a value.
If I change a value the shape will automatically change to reflect this.

Try this:
(change "A1" to the cell where your value is and "Right Arrow 1" to
the name of your shape)

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1")) Is Nothing Then
Shapes("Right Arrow 1").Width = Target.Value
End If
End Sub

Hope this helps / Lars-Åke



For which function do you get that error message?
Maybe you should use ; (semicolon) rather than , (comma)
as separator of arguments.

Otherwise I can not think of why it does not work.
Maybe someone else can suggest what might be wrong.

/ Lars-Åke

"Lars-Ã…ke Aspelin" wrote:

On Thu, 27 Mar 2008 02:41:05 -0700, MichaelSpy2008
wrote:

I have tried this and I get a argument not optional error message, Can you
help please. Thanks

"Lars-Ã…ke Aspelin" wrote:

On Wed, 26 Mar 2008 09:08:02 -0700, MichaelSpy2008
wrote:

Is it possible to automatically resize a shape (eg arrow) based on a value.
If I change a value the shape will automatically change to reflect this.

Try this:
(change "A1" to the cell where your value is and "Right Arrow 1" to
the name of your shape)

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1")) Is Nothing Then
Shapes("Right Arrow 1").Width = Target.Value
End If
End Sub

Hope this helps / Lars-Ã…ke



For which function do you get that error message?
Maybe you should use ; (semicolon) rather than , (comma)
as separator of arguments.

Otherwise I can not think of why it does not work.
Maybe someone else can suggest what might be wrong.

/ Lars-Ã…ke

It seems to not recognise the "shapes" bit of the code

On Thu, 27 Mar 2008 06:10:02 -0700, MichaelSpy2008
wrote:



"Lars-Åke Aspelin" wrote:

On Thu, 27 Mar 2008 02:41:05 -0700, MichaelSpy2008
wrote:

I have tried this and I get a argument not optional error message, Can you
help please. Thanks

"Lars-Åke Aspelin" wrote:

On Wed, 26 Mar 2008 09:08:02 -0700, MichaelSpy2008
wrote:

Is it possible to automatically resize a shape (eg arrow) based on a value.
If I change a value the shape will automatically change to reflect this.

Try this:
(change "A1" to the cell where your value is and "Right Arrow 1" to
the name of your shape)

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1")) Is Nothing Then
Shapes("Right Arrow 1").Width = Target.Value
End If
End Sub

Hope this helps / Lars-Åke



For which function do you get that error message?
Maybe you should use ; (semicolon) rather than , (comma)
as separator of arguments.

Otherwise I can not think of why it does not work.
Maybe someone else can suggest what might be wrong.

/ Lars-Åke

It seems to not recognise the "shapes" bit of the code

What do you mean by "not recognize"?
Is the error message still "argument not optional"?
Maybe you could copy you subroutine here.

/ Lars-Åke

You cna use a worksheet change function like the one below

Sub worksheet_change(ByVal target As Range)

If target.Address = "$F$2" Then

Shapes("Line 1").Height = target
End If

End Sub


"MichaelSpy2008" wrote:

Is it possible to automatically resize a shape (eg arrow) based on a value.
If I change a value the shape will automatically change to reflect this.

Does this functionality exist in Excel 2000? I don't see properties
for the line--I used the drawing toolbar, and I don't know how to
determine the line's name.

Thanks,

Dan