Keeping the aspect ratio of images in comments

[spcl]

Dear EF-Members,

maybe one of you guys can help me with my problem - I do not have any knowledge of VBA, which keeps me stuck in an infinite loop of trial and error.
What I do have right now is pretty much all that I am asking for, the following code inserts comments with pictures depending on an article number.
All I'm asking for is, that the aspect ratio of the image should be kept, while the height stays at 100 pixel.

Please Login or Register  to view this content.

I would greatly appreciate any answers!

Thank you and kind regards,
Andreas

[OllieB]

Andreas,

Have you tried setting the following property for objCom?

Please Login or Register  to view this content.

[spcl]

I tried that, but the result is not okay - it creates comments that are all (approx.) 2 times as wide as high, all of them, and the images are distorted.

Regards,
Andreas

[OllieB]

Andreas,

I declared the objCom object as a shape object , and set the .LockAspectRatio = msoFalse (before specifying the size).

Worked for me

Please Login or Register  to view this content.

[spcl]

I am sorry if this question may sound kind of stupid - it's the first time that I have to work with a macro like this - do I have to replace your code with mine or do I put your code somewhere in mine? I tried to change just the declaration and the .LockAspectRatio but that didn't work.

Thanks for your patience!

[OllieB]

Andreas,

no problem

replace your existing code with the code shown below

Please Login or Register  to view this content.

Note that I have only added the picture to the commend object in my test. I have not tested the rest of your code

[spcl]

It seems, that the problem lies somewhere else - using your code I got the same result as with mine
I have also tried to run the code without the lower parts (I used them to align the comments) and got the same result.

Unfortunately I do not really understand what the code does ...

[OllieB]

Andreas,

I have ran the entire code, including the lower part. I face no problems whatsoever. I can only assume that it MAY be related to the jpg you are trying to insert (just guessing). Would it be possible to post one such a picture, so I can test with that picture?

[spcl]

It puzzles me, that it seems to run on your machine - here everything works fine, except the aspect ratio. All I get are comments sized 100 x 100 pixel and images that fit them perfectly.
Unfortunately I am not allowed to post any images due to business regulations. I tried to run the code with other pictures and got the same results. I tried to change the size of my cells but that also didn't affect the results. Did you run it in Excel 2010?

I do understand if you don't want to bother yourself anymore with that matter, I really appreciate your help!

[RHCPgergo]

"All I'm asking for is, that the aspect ratio of the image should be kept, while the height stays at 100 pixel."

What do you mean by that? You either
1. keep the aspect ratio (In this case the size of the picture is changed without changing the ratio of the borders. For example 100 width 200 height becomes 150 width 300 height.)

OR

2. the height stays at 100. (In this case you fix one of the dimensions/sides and the other side changes. This way the picture will be distorted.

[spcl]

For example: I have two pictures, one is 200 px wide and 300 pixel high, the other one is 350 px wide and 350 pixel high.

I want the two images to be 100 px high, that means:
Picture 1 is 66 px wide (200/3) and 100 pixel high (300/3),
Picture 2 is 100 px wide (350/3,5) and 100 pixel high (350/3,5).

I want to tell Excel that my comments should be exactly 100 px high, the image should be resized to that height and the width should be scaled down exactly by the same ratio.

[OllieB]

Ok. Set keep aspect ratio to true and height to 100. Do not specify the width!

[OllieB]

I actually thought you wanted 100x100. Sorry

[spcl]

Hello OllieB,

if I set the AspectRatio to msoTrue and the height to 100, the comments are 100 high, but much wider - and the images get squeezed again.
If I remove all the lower code and do not set any height or width arguments, I do get a similar result but with smaller comments.
I have attached screenshots for both codes so you can see what happens

Regards,
Andreas

Screenshot_01.JPGScreenshot_02.jpg

[OllieB]

Andreas, interesting case. Let me experiment a bit. I will get back to you shortly.

[OllieB]

Andreas, interesting case. Let me experiment a bit. I will get back to you shortly.

[OllieB]

Andreas,

There is a property on the FillFormat object which allows the picture to retain it's original dimensions called 'Lock Picture Aspect Ratio'. This parameter is not available from VBA. When the picture is inserted into the comment it looks like the height and width dimensions are lost and swapped, and as a result the Lock Aspect Ratio does not work properly anymore. The only solution that I could see was to insert the picture into a dummy shape, get the dimensions and delete the picture. Using the "actual" dimensions the picture can be sized properly when inserted into the comment object.

Replace your code with the appended code and give it a try

Please Login or Register  to view this content.

[JosephP]

I don't think the 'lock picture aspect ratio' option is exposed in vba

[OllieB]

I don't think the 'lock picture aspect ratio' option is exposed in vba

Joseph, you are correct, that why I had to write a somewhat convoluted solution.

[JosephP]

I hadn't seen that when I posted (forum problems mean I don't refresh any more than I must!). if you are not concerned with the shapes being adjusted later-and thereby distorted-then you could also probably read the dimensions of the image file and scale accordingly

[spcl]

Hello OllieB, sorry for my late reply - I have just tried your new code and get an error on "Set objPicture = .Pictures.Insert (strPfadDatei) - "Runtime Error '1004' - The Insert-Attributes for the Picture-Object can not be assigned. I do feel really bad that I can not contribute to a solution due to my lack of knowledge, I hope you are satisfied with lots of good karma...

[spcl]

Hello OllieB, now it works, I replaced "Set objPicture = .Pictures.Insert (strPfadDatei)" with "Set objPicture = .Pictures.Insert ("V:\01_Pictures\01_Single Shots\02_Low Resolution\" & strPfadDatei & ".jpg")

Thanks a lot, really really great work!

[OllieB]

Don't worrry, my bad

replace

Please Login or Register  to view this content.

with

Please Login or Register  to view this content.

I had to change this tot test on my pc and forgot to change it back.

It will work :-)

[OllieB]

Ah, you figured that one out for yourself. Finally we have managed to get it to work. I am happy.

Please mark the thread as SOLVED, and if you are happy with my contribution, please remember to click on the star icon to ad to my reputation!

[spcl]

Yes, at least that tiny part I figured out by myself, thanks again for your help, perfect, really!
I also added to your reputation

[OllieB]

We are all learning, every day again