Automatic Scaling of EXCEL Drawings

[alan54]

I'm using EXCEL 2000. Can you use the built in drawing feature and have objects scaled based on values in cells.

For instance, for a rectangle, you can right click / Format Autoshape / and change the height, width, etc under the Size tab.

But to set those equal to a cell value in VBA, I can't find the rectangle's object name (i.e. rectanglename.Width = value of cell)

Many Thanks,

alan54

 

[pmover]

response to first question - unfortunately not or not that i'm aware of.

2nd question: try the selection.

with regards to a scaling factor, please note the code below used to change an object's size. instead of the textbox1.value, change code for a cell input. the code below is not in its entirity as i appreviated what the sub is actually used for. be certain to have object selected.

Private sub CommandButton2_Click()
on error goto no_selection
wid=selection.width
hei=selection.height
sf=1+textbox1.value/100' change to sf=range("sf&quot.value or ?
selection.height=sf*hei
selection.width=sf*wid
exit sub
no_selection:
end sub

interesting, but ...

-pmover

 

[joerd]

You can refer to any shape on a worksheet by its name, through the Shapes collection.

ActiveSheet.Shapes("Rectangle 1&quot.Height

You can read/write all relevant properties in this manner, so in your case ActiveSheet.Shapes("Rectangle 1&quot.width = Activesheet.Range("A1&quot.Value

The width/height etc. are specified in points = 1/72 inch. Regards,

Joerd