none
How to autosize the picture in cell.Comment by Excel VBA?

    Question

  • Hi all,
    I have been trying to get images into comments without having to record macros and use the created long-winded code.
    Im at the point now where i can use the following:

    ActiveCell.Comment.Shape.Fill.UserPicture _
    "C:\Users\Myles\Pictures\Quote Photos\DJT202.jpg"


    This works fine but the comment has a default size which cuts of parts of the picture. I know that i can manually code in a specified height but all the images i am using are different sizes. Is there a way to autosize the comment to match the original image dimensions?

    I have seen the TextFrame.AutoSize = True function work to autosize the comment to fit text but is it possibel to do this with images?

    I have also tried Comment.shape.ScaleHeight 1, msoCTrue, but still not work;

    Option Explicit
    Sub addpic()
      Dim pic_file As String
      pic_file = Application.GetOpenFilename("JPG (*.JPG), *.JPG", Title:="Pls open a picture file:")
      On Error Resume Next
     
      If ActiveCell.Comment Is Nothing Then ActiveCell.AddComment   
      With ActiveCell.Comment.Shape.
       .Fill.Visible = msoTrue
       .Fill.UserPicture (pic_file)
       .ScaleHeight 1, msoCTrue
       .ScaleWidth 1, msoCTrue
      End With
      
       ActiveCell.Comment.Visible = False
     End Sub

    Thursday, March 08, 2012 7:12 AM

Answers

  • Here is the code I use Peter's idea to auto size the comment size for showing the picture:

    Option Explicit
    Sub addpic()

      Dim pic_file As String
      Dim pict_name As String
      Dim pict1 As Picture
     
      If ActiveCell.Comment Is Nothing Then ActiveCell.AddComment
      pic_file = Application.GetOpenFilename("JPG (*.JPG), *.JPG", Title:="Pls open a picture file:")

      Set pict1 = ActiveSheet.Pictures.Insert(pic_file)
      pict_name = pict1.Name
     
     
      On Error Resume Next
     
      With ActiveCell.Comment.Shape
       .Fill.Visible = msoTrue
       .Fill.ForeColor.RGB = RGB(255, 255, 255)
       .Fill.BackColor.SchemeColor = 80
       .Fill.UserPicture (pic_file)
       .Height = pict1.Height
       .Width = pict1.Width
        
      End With  

      pict1.Delete
      Set pict1 = Nothing    
      ActiveCell.Comment.Visible = False
     
    End Sub

    Wednesday, March 14, 2012 5:31 AM
  • Pictures in Shapes can't be autosize'd as text in a textframe can, however an ActiveX Image control does have an Autosize property.

    If you have several images in comments to process over time, you could leave an Image control on a sheet somewhere (it doesn't have to be in the same sheet or workbook). Load the picture to the image, get the new size, then add the picture to the comment and size to the Image control's new dimensions. When done remove the image from the control but keep the control. You could do virtually same with an Image control on a userform, you wouldn't even need to show the form but be sure to unload the form when done.

    Alternatively, and perhaps even simpler, use the Insert picture method to add to a sheet, which will be inserted to correct size. Get is dimensions, delete it, and apply to the comment with the same picture.

    Only slight niggle with the second approach is the sheet's object counter will increment each time, it's not an issue at all except over time user might wonder why new objects get prefixed with a high number.

    Peter Thornton

    Thursday, March 08, 2012 10:22 AM
    Moderator

All replies

  • Hi Max Zou,

    This is the Visual Basic General forum, I don't think youll be able to find many answers here.

    I suggest you try Visual Basic for Applications (VBA) forum for more help!

    Good luck,
    Verraine

    Thursday, March 08, 2012 5:38 AM
  • I found some use WIA add-ins to get pic size and resolution, it is close to actual size but still looks bigger than those you  mannually resize cell by cell.

    Option Explicit
    Sub addpic()

      Dim pic_file As String
      Dim pic_resolution As Long
      Dim pict As Object
       
     
      If ActiveCell.Comment Is Nothing Then ActiveCell.AddComment

       pic_file = Application.GetOpenFilename("JPG (*.JPG), *.JPG", Title:="Pls open a picture file:")
      Set pict = CreateObject("WIA.ImageFile")
      pict.loadfile pic_file
      pic_resolution = pict.VerticalResolution
        
      With ActiveCell.Comment.Shape
         .Fill.Visible = msoTrue
         .Fill.UserPicture (pic_file)
         .Height = pict.Height / pic_resolution * 96
         .Width = pict.Width / pic_resolution * 96
       End With
     
      Set pict = Nothing
       
      ActiveCell.Comment.Visible = False
     


    End Sub


    http://chijanzen.net/wp/?p=347 has some information about this.
    • Edited by Max Zou Thursday, March 08, 2012 7:28 AM
    Thursday, March 08, 2012 7:15 AM
  • If you want to use from time to time solution

    I can recomend you a tool:

    Zarządzanie komentarzami w Excelu

    Interfejs programu

    Or method code haw imported exp to cell:

    Dodanie obrazka do komórki obok [EN]

    regards


    Oskar Shon, Office System MVP

    Press if Helpful; Answer when a problem solved


    Thursday, March 08, 2012 8:39 AM
    Answerer
  • Pictures in Shapes can't be autosize'd as text in a textframe can, however an ActiveX Image control does have an Autosize property.

    If you have several images in comments to process over time, you could leave an Image control on a sheet somewhere (it doesn't have to be in the same sheet or workbook). Load the picture to the image, get the new size, then add the picture to the comment and size to the Image control's new dimensions. When done remove the image from the control but keep the control. You could do virtually same with an Image control on a userform, you wouldn't even need to show the form but be sure to unload the form when done.

    Alternatively, and perhaps even simpler, use the Insert picture method to add to a sheet, which will be inserted to correct size. Get is dimensions, delete it, and apply to the comment with the same picture.

    Only slight niggle with the second approach is the sheet's object counter will increment each time, it's not an issue at all except over time user might wonder why new objects get prefixed with a high number.

    Peter Thornton

    Thursday, March 08, 2012 10:22 AM
    Moderator
  • Hi Max Zou,

    Then I will move it to VBA forum for better support.

    Thank you for your understanding.

    Best regards


    Shanks Zen
    MSDN Community Support | Feedback to us

    Monday, March 12, 2012 8:01 AM
  • Max Zou are you find solution in my propositions

    Oskar Shon, Office System MVP

    Press if Helpful; Answer when a problem solved

    Monday, March 12, 2012 8:03 AM
    Answerer
  • Hi,Oskar, Your method did not solves how to set the comment shape size relative to the picture's original size, I still can not resize it and it does not help! But thanks anyway!

    But I find Peter Thornton's advice is very useful. I am still working on his suggestions.

    Wednesday, March 14, 2012 4:44 AM
  • Here is the code I use Peter's idea to auto size the comment size for showing the picture:

    Option Explicit
    Sub addpic()

      Dim pic_file As String
      Dim pict_name As String
      Dim pict1 As Picture
     
      If ActiveCell.Comment Is Nothing Then ActiveCell.AddComment
      pic_file = Application.GetOpenFilename("JPG (*.JPG), *.JPG", Title:="Pls open a picture file:")

      Set pict1 = ActiveSheet.Pictures.Insert(pic_file)
      pict_name = pict1.Name
     
     
      On Error Resume Next
     
      With ActiveCell.Comment.Shape
       .Fill.Visible = msoTrue
       .Fill.ForeColor.RGB = RGB(255, 255, 255)
       .Fill.BackColor.SchemeColor = 80
       .Fill.UserPicture (pic_file)
       .Height = pict1.Height
       .Width = pict1.Width
        
      End With  

      pict1.Delete
      Set pict1 = Nothing    
      ActiveCell.Comment.Visible = False
     
    End Sub

    Wednesday, March 14, 2012 5:31 AM