Object Size Linked to Cell Imput 1

[BDNH]

Hello everyone!

Is there a way to make the size (height and width) of a autoshape - - namely a circle dependent on the imput of 2 cells?


I would like to be able to visualize how many pipes (of different diameters) could be loaded into an ocean container. I would like to draw a square representing the back of the open container 7' x 7' (drawn to 1/10 scale)then be able to have a macro button draw a circle (representing the end of the pipe - drawn to 1/10 scale) the diameters of the circles would be determined by the input into the cells.

I could drag the circles over the square to determine how many pipes I could get into the container and what the best way to load them would be.

Thanks in advance

 

[BDNH]

Sorry - I had height and width on my mind when I said 2 cells. I guess what I really need is to be able to specify the diameter of the pipe i.e. 12.750" and then have the macro button create a circle 1.275" (1/10 scale)

 

[SkipVought]

Hi,

Every shape object has Top, Left, Width, Height properties in points.

How are you manipulating this/these object(s)?

What's the process?

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[BDNH]

Thanks for the quick reply.

Not sure that this answers your qustion but here goes. I just want to be able to specify the diameter of the circle to be generated. the placement on the sheet is not that critical. I can easily drag it to the location i need it. For arguments sake lets say that cell B1 is is where I specify the diameter of the pipe i.e. 12.750" I would like the button to generate a 1.275" circle and place the center of it anywhere near cell D10.

I dont nead the placement of the cirlces into the square representing the container to be automatic. I just need them nearby to drag them into place and experiment with different placements.

Hope this helps

 

[SkipVought]

Both Width & Height are identical values for a circle.

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[BDNH]

Yes I know - You cant just specify the diameter 12.750"
(same as height 12.750" and width 12.750") and have the button generate a 1.275" circle?

 

[SkipVought]

BTW,

To convert inches to points use

With Selection
  .Width = Application.InchesToPoints(12.750)
  .Height = Application.InchesToPoints(12.750)
End With

But do you REALLY want to use 12+ inches????

Here's the code for your button

    nDiam = 12.76
    Set MyObject = ActiveSheet.Shapes.AddShape( _
        msoShapeOval, [D10].Left, [D10].Top, Application.InchesToPoints(nDiam ), Application.InchesToPoints(nDiam ))
    
    Set MyObject = Nothing

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[BDNH]

Thanks for your help.

Actually I would like the dimensions of the circle to be dependent on value that is imput into cell B1 then generate a circle at 1/10 scale. Therefore if I imput 12.750" into cell B1 - the button will generate a 1.275" circle (same as height and width 1.275)

The value in cell B1 could vary anywhere between 2.500" and 24.000". I would like the button to always produce a 1/10 scale circle of the value imput into B1

thanks again for your help

 

[SkipVought]

    nDiam = [B1]/10
    Set MyObject = ActiveSheet.Shapes.AddShape( _
        msoShapeOval, [D10].Left, [D10].Top, Application.InchesToPoints(nDiam ), Application.InchesToPoints(nDiam ))
    
    Set MyObject = Nothing

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[SkipVought]

What am I thinking!!!

    nDiam = Application.InchesToPoints([B1]/10)
    Set MyObject = ActiveSheet.Shapes.AddShape( _
        msoShapeOval, [D10].Left, [D10].Top, nDiam, nDiam)
    
    Set MyObject = Nothing

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[BDNH]

K - Im back from lunch

Thanks for your patience - I no nothing about VBA - can I just copy and paste your code into a module then asign it to a button to get the desired results? or do I need to do something else?

 

[SkipVought]

You nailed it, pard'!

It depends of you use a Form button or a Control Toolbox command button.

The Command button has event structure in the Sheet object (right click the sheet tab and select View Code) -- you can paste the code into directly -- like the CommandButton1_Click event.

The Form button has an assign macro that when you right click, and you can assign the code that you copy into a Sub in a Module.



Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[BDNH]

NM - I got It

Thank you so much - works great!

 

[BDNH]

Sorry - didnt see your last post b4 posting mine - How do I give you a star?