Results 1 to 3 of 3

Thread: Format Comment using VBA
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Oct 2018
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Format Comment using VBA

    I currently am able to use vba to comment a picture I have rooted in a specific cell, but am having trouble sizing it properly. My current code is below:

    Sub Comment()

    Set rng = ActiveSheet.Cells(9, 1)
    rng.AddComment
    rng.Comment.Text Text:=""
    rng.Comment.Shape.Fill.UserPicture (Sheets("Metrics").Range("B31"))
    rng.Comment.ShapeRange.ScaleWidth = Sheets("Metrics").Range("I44")
    rng.Comment.ShapeRange.ScaleHeight = Sheets("Metrics").Range("K44")


    End Sub

    As stated prior, the image with root in B31 is inserted without any issues, but I can't get the comment to scale by the values in cells I44 and K44.
    Any help would be appreciated, as I am not really sure what I am doing wrong. Thanks in advance,

    Eric

  2. #2
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    18,855
    Post Thanks / Like
    Mentioned
    35 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Format Comment using VBA

    Try...

    Code:
    Rng.Comment.Shape.ScaleWidth Factor:=Sheets("Metrics").Range("I44"), RelativeToOriginalSize:=msoFalse, Scale:=msoScaleFromTopLeft
    Rng.Comment.Shape.ScaleHeight Factor:=Sheets("Metrics").Range("K44"), RelativeToOriginalSize:=msoFalse, Scale:=msoScaleFromTopLeft
    Hope this helps!
    Last edited by Domenic; Nov 7th, 2018 at 04:25 PM.

  3. #3
    New Member
    Join Date
    Oct 2018
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Format Comment using VBA

    Works perfect, thank you very much!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •