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!

 

[craig.penny]

About all I can say is - Wow! That works perfectly and now that I see the solution I can be sure I would have never figured it out.

Thank you all so much for taking the time to respond. I truly appreciate it!

 

[Jaafar Tribak]

Out of curiosity, can we do this the other way around ? ie: Adding a comment picture to a shape .

 

[craig.penny]

Is there a library I need to set a reference to? This worked great on one machine but not on another.

 

[Greg Truby]

This is NOT my strong suit and I'm sure Jaafar will figure out the problem lickety split. But in the mean time, I'd find out if both machines are running the same versions of the same Operating System. (By same version I mean 32-bit versus 64-bit.)

 

[Jaafar Tribak]

Hi Greg.

I have been testing a similar code to the one I posted which I though would work for copying a comment picture into a shape but for some reason I get the error "Out of memory" when trying to save the image to disk.

here is the code I am using which I thought would work :

Option Explicit
 
'Declare a UDT to store a GUID for the IPicture OLE Interface
Private Type GUID
    Data1 As Long
    Data2 As Integer
    Data3 As Integer
    Data4(0 To 7) As Byte
End Type
 
'Declare a UDT to store the bitmap information
Private Type uPicDesc
    Size As Long
    Type As Long
    hPic As Long
    hPal As Long
End Type
 
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
 
Const CF_BITMAP = 2
Const CF_PALETTE = 9
Const PICTYPE_BITMAP = 1
 
Private Sub CommentToShape(CommentSrc As Comment, ShapeDest As Shape)
 
    Dim IID_IDispatch As GUID
    Dim uPicinfo As uPicDesc
    Dim IPic As IPicture
    Dim hPtr As Long
    Dim sTempFileName As String
 
    sTempFileName = ThisWorkbook.Path & "\temp.bmp"
 
    'Copy and retrieve the handle to the comment Image
    CommentSrc.Shape.OLEFormat.Object.CopyPicture xlScreen, xlBitmap
   
    OpenClipboard 0
    hPtr = GetClipboardData(CF_BITMAP)
    CloseClipboard
 
    'Create the interface GUID for the picture
    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
 
    ' Fill uPicInfo with necessary parts.
    With uPicinfo
        .Size = Len(uPicinfo) ' Length of structure.
        .Type = PICTYPE_BITMAP ' Type of Picture
        .hPic = hPtr ' Handle to image.
        .hPal = 0 ' Handle to palette (if bitmap).
    End With
 
   'Create the Picture Object
   OleCreatePictureIndirect uPicinfo, IID_IDispatch, True, IPic
 
    'Save Picture Object to disk.
    stdole.SavePicture IPic, sTempFileName
    
    'copy the pic to the dest range comment. ( errors out here !!!! )
    ShapeDest.Fill.UserPicture sTempFileName
    
    'delete temp pic file.
    Kill sTempFileName
 
End Sub

Usage :

The code below is supposed to copy the picture in comment of Range("a1") into the shape called "Oval 1"

Sub Test()
 
    CommentToShape Range("A1").Comment, ActiveSheet.Shapes("Oval 1")
    
End Sub

Do you get the similar error ?

 

[Jaafar Tribak]

On further testing, the line :

CommentSrc.Shape.OLEFormat.Object.CopyPicture xlScreen, xlBitmap

doesn't copy the comment picture as expected and therefore the GetClipboardData returns a null handle.

 

[craig.penny]

The machine it works on is 32 bit, Vista, Excel 2007

The machine it doesn't work on is 64 bit, Vista, Excel 2007.

The problem only happens when I'm on a worksheet, change a value, and press TAB or ENTER and then I get "User-defined type not defined". If I do the same thing but click into another cell it's fine. I've tried commenting out large chunks of code but I can't seem to make it stop.

 

[Greg Truby]

Again - with me the ice under my Win API call feet is so thin it cracks if I cough; but I think you need different libraries to pull 64-bit versions of those functions for it to run on 64-bit Vista. I.e. whereever a DECLARE statement says "user32" it needs to say something else - whatever is the correct library name for the 64-bit versions.

I don't know if there's a built-in conditional compiling constant for 64-bitness, but you could certainly define one so that you could quickly code up dual versions and then just set the value of the conditional compiling constant to flip your 32-bit workbook to work on 64-bit OS's.

EDIT - looks like I'm wrong. Perhaps the user32 library works for both? http://stackoverflow.com/questions/1540741/c-pinvoking-user32-dll-on-a-64-bit-system

However maybe a slight difference in the DECLARE syntax? See the section "Which Longs should become LongPtrs?" in Jan Karel's web page here:
http://www.jkp-ads.com/articles/apideclarations.asp

Again, I could be wrong here. Jan Karel's mostly talking about 32-bit EXCEL versus 64-bit EXCEL and not about differing Operating Systems.

 

[craig.penny]

Oh I see what you're saying. That didn't even occur to me. Thanks again!

 

[Greg Truby]

Yeah, I should probably just sit down and shut up until Jaafar or Rory or someone else that knows Win API schtuff better then I shows up.