• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 214
  • Last Modified:

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
0
Rogit85
Asked:
Rogit85
  • 4
  • 4
1 Solution
 
patrickabCommented:
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
0
 
patrickabCommented:
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
0
 
Rogit85Author Commented:
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.
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
patrickabCommented:
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
0
 
Rogit85Author Commented:
That only wraps it to the text inside the comment, and I wont be using text in mine
0
 
patrickabCommented:
>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
0
 
Rogit85Author Commented:
Damn

Well thank you very much for your help, its a shame that it cant be done
0
 
Rogit85Author Commented:
Hi Patricklab

I managed to solve this myself in the end, It opens the image in the spreadsheet, measures it, deletes it then adds it back as a comment image :)

Dim rngTarget As Range
Dim lHeight As Long, lWidth As Long

ActiveSheet.Pictures.Insert("C:\Patient\mao.jpg").Select
With Selection
    lHeight = .Height
    lWidth = .Width
    .Delete
End With
Set rngTarget = ActiveSheet.Range("C7")
With rngTarget
    .AddComment
With .Comment.Shape
    .Width = lWidth
    .Height = lHeight
    .IncrementTop -90#
    .Fill.UserPicture ("C:\Patient\mao.jpg")
End With
End With
    Range("C7").Comment.Visible = False
0
 
TracyVBA DeveloperCommented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now