macro to make object fit the size of the cell

[Willardio]

I have this macro that inserts a word document object and a page break above every 44th unhidden row. I need it to size the word document to fit the cell, which is actually a merged range. Anyone have any ideas? I would also like to have it set the fill color to no fill.

Please Login or Register  to view this content.

[RHCPgergo]

Hi,

although I couldn't get your code to work, I think this might work for you:

Please Login or Register  to view this content.

I took the info from here: http://msdn.microsoft.com/en-us/libr.../ff195728.aspx

[Willardio]

thanks for RHCPgergo, it worked but as with every other method I have tried, only the width is adjustable never the height. I think it may have to do with "lock aspect ratio" and "relative to original picture size" being the default format? do you know a way to change those parameters when the object is created?

[RHCPgergo]

How about this one?

Please Login or Register  to view this content.

[Willardio]

very good RHCPgergo! I modified as follows and it works perfectly. Funny thing is, the way you wrote the height adjusts but the width appears to be zero, whereas when both peramaters were in the OLEObjects.add command, the width adjusted but not the height. So I put width in the OLEObject.add and height with your "rng" code. Strange, but it works.

The other issue is that --and I neglected to mention this earlier, sorry-- the very last word object always ends up under the pagebreak, not above it. any notion as to why that is happening? If not don't worry, you solved this and I owe you much gratitude!

Here's my modification to your code:

Please Login or Register  to view this content.

Thanks again,

Willardio

[RHCPgergo]

Glad I could help, although when I try your code (after removing these lines, because they were duplicated for some reason)

Please Login or Register  to view this content.

it makes huge shapes for me, much larger than a single cell. Also, the last object is not under the pagebreak for me. Maybe because we have different versions of excel? I'm using excel 2007 right now. Sorry, but no idea.