Adding worksheet picture to a comment

[craig.penny]

Hello all you Excel nerds!

I'm trying to insert a picture into a cell comment. I've found plenty of threads but they are all for situations where the picture is not already within the worksheet or workbook. This is what I've been trying with all different things in place of the "???????" but I haven't figured it out.

Sub NamePic()
      Dim Pic As Object
      Dim Nm As String: Nm = "Picture 2"
      Dim s As Worksheet: Set s = ActiveSheet
      
      On Error Resume Next
      
      s.Cells(1, 2).Select
      Set Pic = s.Pictures(Nm)
      
      s.Cells(1, 5).AddComment
      s.Cells(1, 5).Comment.Shape.Height = Pic.Height
      s.Cells(1, 5).Comment.Shape.Width = Pic.Width
      s.Cells(1, 5).Comment.Shape.Fill.UserPicture   ????????
      
End Sub

"Picture 2" is definitely the right name but now what?

If you read this and you don't know the answer but just have an idea I'd love to hear it!

Thanks in advance!

 

[Greg Truby]

Oh, nicely done, Vladimir. Just for anyone reading this in the future - if you get all excited and just drop this into a new workbook you might try the version below which will remind you that you need to save a workbook before you can read its path. Not, ahem, that I woulda done sumpin' like dat.

Sub Pic2Comment()
    Dim Pic As Picture, PicName As String, p As String
 
    Set Pic = ActiveSheet.Pictures(1) ' <-- Set index or name of the picture here
    p = ThisWorkbook.Path
 
    If Len(p) = 0 Then
        MsgBox "Yo, Lumpy! Getting a bit excited about this nifty code are you?" _
               & vbCr & vbCr & "You need to save this file before you can run this macro.", _
               vbExclamation, "Operation Aborted"
        Exit Sub
    End If
    Application.ScreenUpdating = False
    With Workbooks.Add
        PicName = p & "\_" & Replace(Pic.Name, " ", "_") & ".jpg"
        Pic.Copy
        With .Sheets(1).ChartObjects.Add(0, 0, Pic.Width, Pic.Height).Chart
            .Paste
            .Export Filename:=PicName, FilterName:="JPG"
        End With
        .Close False
    End With
    With ActiveSheet.Cells(1, 1)
        If Not .Comment Is Nothing Then .Comment.Delete
        .AddComment
        With .Comment.Shape
            .Height = Pic.Height
            .Width = Pic.Width
            .Fill.UserPicture PicName
        End With
    End With
    Kill PicName
    Application.ScreenUpdating = True
 
End Sub

 

[ZVI]

Thanks Greg - well improved & humanized one!
A bit more silent modification:

  p = ThisWorkbook.Path
  ' --> new part
  If Len(p) = 0 Then p = Environ("temp")
  If Len(p) = 0 Then p = Environ("tmp")
  ' <-- end of the new part

Vlad

 

[ZVI]

This suits as well:

  'p = ThisWorkbook.Path
  p = CreateObject("Scripting.FileSystemObject").GetSpecialFolder(2)

 

[craig.penny]

That is brilliant! Thank you very much, it works perfectly

I'm curious what this does

  p = ThisWorkbook.Path
  If Len(p) = 0 Then p = Environ("temp")
  If Len(p) = 0 Then p = Environ("tmp")

Why the second "If Len(p) = 0"?

 

[ZVI]

Why the second "If Len(p) = 0"?

It's for compatibility with old Operating Systems or with manual settings when "tmp" environment variable is used instead of the "temp" one.
BTW, CreateObject("Scripting.FileSystemObject").GetSpecialFolder(2) gives valid temp path irrespective of any environment variable.

 

[craig.penny]

Thanks for responding ZVI.
I've asked this question before and I'm afraid there's no easy answer but I'll try again anyways. Without exception, everyone who has responded to this thread knows orders of magnitude more about VBA than I do. How could I go about a semi-structured way of learning about these kinds of things? I have quite a few books and I've spent a fair amount of time at it but my ability and your ability are vastly separated. It's not that I have a problem with the logical aspect of programming, it's that I'm just unaware of so many of the possible constructions, calls, and references. Some examples of code that make me turn my head sideways like a puppy are

  [COLOR=darkblue]If[/COLOR] Len(p) = 0 [COLOR=darkblue]Then[/COLOR] p = Environ("temp")
  [COLOR=darkblue]If[/COLOR] Len(p) = 0 [COLOR=darkblue]Then[/COLOR] p = Environ("tmp")

p = CreateObject("Scripting.FileSystemObject").GetSpecialFolder(2)

Everything after the "=" in the following

    With IID_IDispatch
        .Data1 = &H7BF80980
        .Data2 = &HBF32
        .Data3 = &H101A
        .Data4(0) = &H8B
        .Data4(1) = &HBB
        .Data4(2) = &H0
        .Data4(3) = &HAA
        .Data4(4) = &H0
        .Data4(5) = &H30
        .Data4(6) = &HC
        .Data4(7) = &HAB
    End With

Private Declare Function OpenClipboard Lib "user32" (ByVal hwnd As Long) As Long
Private Declare Function GetClipboardData Lib "user32" (ByVal wFormat As Integer) As Long
Private Declare Function CloseClipboard Lib "user32" () As Long
Private Declare Function OleCreatePictureIndirect Lib "olepro32.dll" (PicDesc As uPicDesc, RefIID As GUID, ByVal fPictureOwnsHandle As Long, IPic As IPicture) As Long

Etc...

I'm not asking for a line by line lesson here because I know that would take any of you too long, but could you suggest a reference guide with sufficient examples? Or maybe more to the point is how did you all get so proficient?

At any rate, if you don't respond to this let me just once more say thank you all for your fantastic help!

 

[RoryA]

The first two and the last are very different. For the latter two, you need a good reference guide to the Windows API (If you want a book that makes sense to non-C programmers, search Amazon for Dan Appleman - for example this one.)
For the former, a good pure VBA book may assist. To my mind there isn't a better one than this one. (looking at the US prices, I'd go for a used one!)

 

[craig.penny]

I just ordered them both

Thanks a ton, Rorya!

 

[Greg Truby]

...Without exception, everyone who has responded to this thread knows orders of magnitude more about VBA than I do. How could I go about a semi-structured way of learning about these kinds of things? I have quite a few books and I've spent a fair amount of time at it but my ability and your ability are vastly separated. It's not that I have a problem with the logical aspect of programming, it's that I'm just unaware of so many of the possible constructions, calls, and references.

Craig - reading the right books definitely gets you miles and miles ahead of where you'd be without them. But it's also just a simple function of experience. Answering questions at this board can help with that as well.

Vladimir's bit

It's for compatibility with old Operating Systems or with manual settings when "tmp" environment variable is used instead of the "temp" one.
BTW, CreateObject("Scripting.FileSystemObject").GetSpecialFolder(2) gives valid temp path irrespective of any environment variable.

That's most likely just a function of experience. It's unlikely that he read and remembered that from some book or something online. I mean yeah, I know about CreateObject(), yeah, I know about the FSO object, and yeah, I know FSO has a GetSpecialFolder method. But would I have thought to employ it here as a backup to locating a folder to which the user should always have write-access rights? Probably not. Why? Because in the few times I've ever had to solve the problem of coming up with a temp folder to which the user has write-access rights I've solved it differently (and probably not for the better).

 

[craig.penny]

There was a time when I would try to answer one question a day from the "Unanswered Threads" link. I think I helped some people but then sometimes I think I may have just caused more confusion so I stopped. I mean I always picked a question that I thought I stood a good chance of answering but it didn't always turn out that way. Trying to figure out my place, so to speak, on this message board has been an ongoing thing for me. I would gladly go back to trying to answer some of the unanswered threads if that's not poor etiquette considering my average is ~60-70%