asked on

Resizing the comments box to fit the embedded image

Hi all

I have been making a spreadsheet to collect images from a location on the computer and input them as a image in a comments box. Im looking for a way to lock the aspect ratio for the picture within the macro, and have the image be a certain size. This is the code I have been using (which someone on here previous helped me with):

Sub insert_pic_in_comment()
Range("D14").AddComment
Range("D14").Comment.Visible = True
Range("D14").Comment.Text Text:="" & Chr(10) & ""
Range("D14").Comment.Shape.Fill.UserPicture _
"C:\Patient\FFS.jpg"
Range("D14").Comment.Shape.Select True
Selection.ShapeRange.ScaleWidth 5#, msoFalse, msoScaleFromTopLeft
Selection.ShapeRange.ScaleHeight 6#, msoFalse, msoScaleFromTopLeft
Selection.ShapeRange.IncrementTop -125#
End Sub

This works fine as long as the image ratio is 4x6, but when someone needs to use a portrait picture, it gets stretched.

Any and all solutions would be fantastic

Cheers

Ian

patrickab

Try:

Sub insert_pic_in_comment()
Range("D14").AddComment
Range("D14").Comment.Visible = True
Range("D14").Comment.Text Text:="" & Chr(10) & ""
Range("D14").Comment.Shape.Fill.UserPicture _
"C:\Patient\FFS.jpg"
Range("D14").Comment.Shape.Select True
Selection.ShapeRange.Width 'try a number here
Selection.ShapeRange.Height 'try a number here
End Sub

Patrick

patrickab

This woks on my machine:

Sub insert_pic_in_comment()
Range("A1").AddComment
Range("A1").Comment.Visible = True
Range("A1").Comment.Text Text:="Sunset" & Chr(10) & ""
Range("A1").Comment.Shape.Fill.UserPicture _
"C:\Documents and Settings\All Users\Documents\My Pictures\Sample Pictures\Sunset.jpg"
Range("A1").Comment.Shape.Width = 200 'try a number here
Range("A1").Comment.Shape.Height = 120 'try a number here
End Sub

Patrick

Rogit85

ASKER

Hi Patrickab

Thank you for the tips!

I need a way for it to adjust depending on the picture used. It needs to automatically change depending if its a portrait or landscape. The people who will be using this spreadsheet have no VB knowledge and the picture's cant be distorted in any way.

patrickab

Try this instead:

Sub insert_pic_in_comment()
Range("A1").AddComment
Range("A1").Comment.Visible = True
Range("A1").Comment.Text Text:="Sunset" & Chr(10) & ""
Range("A1").Comment.Shape.Fill.UserPicture _
"C:\Documents and Settings\All Users\Documents\My Pictures\Sample Pictures\Sunset.jpg"
Range("A1").Comment.Shape.TextFrame.AutoSize = True
End Sub

Patrick

Rogit85

ASKER

That only wraps it to the text inside the comment, and I wont be using text in mine

patrickab

>That only wraps it to the text inside the comment, and I wont be using text in mine

As pictures do not have a predetermined size there is a problem making anything fit an indeterminately sized object. In other words I don't believe you can do what you want.

Patrick

Rogit85

ASKER

Damn

Well thank you very much for your help, its a shame that it cant be done

ASKER CERTIFIED SOLUTION

Rogit85

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Tracy

This question has been classified as abandoned and is being closed as part of the Cleanup Program. See my comment at the end of the question for more details.