length of square
 

i have
Shapes("izensquare")
is there a way to have the length of "izensquare" in cell value A1
and length to be in B1?

thanks in advance

length of square
 

Add the following custom VBA functions into a module.

Function shapeh(ByVal shape_name As String)
shapeh = ActiveSheet.Shapes(shape_name).Height
End Function

Function shapew(ByVal shape_name As String)
shapew = ActiveSheet.Shapes(shape_name).Width
End Function


Then, in the cell which you want to return the height of the shape, type the
formula:
=shapeh("Rectangle 1")

For the width, type:
=shapew("Rectangle 1")


Regards,
Edwin Tam

http://www.vonixx.com


"choice" wrote:

i have
Shapes("izensquare")
is there a way to have the length of "izensquare" in cell value A1
and length to be in B1?

thanks in advance

length of square
 

do i need to run the function?
whenevery i run the sub, it doesnt change when the size of the object changes

"Edwin Tam" wrote:

Add the following custom VBA functions into a module.

Function shapeh(ByVal shape_name As String)
shapeh = ActiveSheet.Shapes(shape_name).Height
End Function

Function shapew(ByVal shape_name As String)
shapew = ActiveSheet.Shapes(shape_name).Width
End Function


Then, in the cell which you want to return the height of the shape, type the
formula:
=shapeh("Rectangle 1")

For the width, type:
=shapew("Rectangle 1")


Regards,
Edwin Tam

http://www.vonixx.com


"choice" wrote:

i have
Shapes("izensquare")
is there a way to have the length of "izensquare" in cell value A1
and length to be in B1?

thanks in advance

length of square
 

You can change the macros to:

Function shapeh(ByVal shape_name As String)
Application.Volatile
shapeh = ActiveSheet.Shapes(shape_name).Height
End Function

Function shapew(ByVal shape_name As String)
Application.Volatile
shapew = ActiveSheet.Shapes(shape_name).Width
End Function


When you adjust the shape, then press F9 to refresh the calculation of the
formulas.

Regads,
Edwin Tam

http://www.vonixx.com

"choice" wrote:

do i need to run the function?
whenevery i run the sub, it doesnt change when the size of the object changes

"Edwin Tam" wrote:

Add the following custom VBA functions into a module.

Function shapeh(ByVal shape_name As String)
shapeh = ActiveSheet.Shapes(shape_name).Height
End Function

Function shapew(ByVal shape_name As String)
shapew = ActiveSheet.Shapes(shape_name).Width
End Function


Then, in the cell which you want to return the height of the shape, type the
formula:
=shapeh("Rectangle 1")

For the width, type:
=shapew("Rectangle 1")


Regards,
Edwin Tam

http://www.vonixx.com


"choice" wrote:

i have
Shapes("izensquare")
is there a way to have the length of "izensquare" in cell value A1
and length to be in B1?

thanks in advance

length of square
 

Try something like


With ActiveSheet.Shapes("izensquare")
.Height = Range("A1").Value
.Width = Range("B1").Value
End With


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



"choice" wrote in message
...
i have
Shapes("izensquare")
is there a way to have the length of "izensquare" in cell value
A1
and length to be in B1?

thanks in advance