[VBA] Image from clipboard to comment UserPicture

[Medium]

Hi experts!

I'm currently trying to get a picture from the clipboard into a comment's UserPicture via VBA.

Reason being: I want to add pictures to cell comments that are made with an external camera program, which stores a picture in the clipboard. For this I run the camera program through VBA, wait for it's return, and then want to use the clipboard image as a comment background in the cell that had originally been clicked.
This is for a sheet a colleague will use on a tablet PC to document several things at our customers' production sites. (Technical data in the table with the picture as additional info.)

I'm well versed in C# and Delphi, but haven't done too much with Excel VBA. So you can talk dev lingo with me, but anything that is deeper VBA or Excel knowledge could need a longer explanation.

I have managed this so far that I can double-click a cell (Buttons don't work because I need to know what row was clicked) and run my camera app and wait for it. At this point I'm stuck with the issue, that the UserPicture member of .Comment.Shape.Fill requires a file name as string. But I don't have a file. I don't WANT a file. I'd like to get my clipboard picture into that.

This is what I have so far:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Target.Column = 30 Then
        Dim wsh As Object
        Set wsh = VBA.CreateObject("WScript.Shell")
        Dim waitOnReturn As Boolean: waitOnReturn = True
        Dim windowStyle As Integer: windowStyle = 1


        wsh.Run ThisWorkbook.Path & "\CamApp\Cam.exe", windowStyle, waitOnReturn
    
        Dim clpbrd As MSForms.DataObject
        
        Range(Target.Address).AddComment
        Range(Target.Address).Comment.Visible = True
        Range(Target.Address).Comment.Shape.Fill.UserPicture clpbrd.GetFromClipboard()  ' <-- this doesn't work, obviously
    End If
End Sub

How could I do this? (And as a side-question: How can I detect if there even is a picture in the clipboard? If the cam app is canceled, it won't store an image there.)

Many thanks in advance!

 

[NdNoviceHlp]

Click on the "spreadsheet" link here. HTH. Dave
http://mathscinotes.com/2013/11/inserting-images-into-excel-comments-using-vba/

 

[Medium]

That sounds way more fitting than anything i have found during this day. Thanks! Excel isn't happy with the script from there being tied to Win32, so I'd have to adapt it a little.

In the meantime, I actually just had another possible way working almost the minute I got the notification from your answer, but it certainly isn't pretty. It involves (ab)using a Chart to export the clipboard image to a jpeg file, but I haven't yet tested if I need a new file for every different image I'm going to load into a comment, or if Excel stores an instance of the picture within the xlsm. If it is the latter, I'd almost consider keeping the cumbersome solution for the time being, as this is a one-off project so far. But if these jobs get frequent, I'll definitely turn to you script, especially because it doesn't require to manifest sheet names and column numbers as hardcoded data in the script. (And getting the chart name right for the access as shape is nasty, too, because spaces in a sheet's name will break the method I used, as will different language settings. Probably.)

My current frankenscript:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Target.Column = 30 Then
        Dim wsh As Object
        Set wsh = VBA.CreateObject("WScript.Shell")
        Dim waitOnReturn As Boolean: waitOnReturn = True
        Dim windowStyle As Integer: windowStyle = 1


        wsh.Run ThisWorkbook.Path & "\CamApp\Cam.exe", windowStyle, waitOnReturn
        
        Dim MyChart As String
        Application.ScreenUpdating = False
        
        Charts.Add
        ActiveChart.Location Where:=xlLocationAsObject, Name:="Motordaten"
        MyChart = Split(ActiveChart.Name, " ")(1) & " " & Split(ActiveChart.Name, " ")(2) ' Because my generated chart name is "Motordaten Diagramm 1" and the Shapes() array has it as "Diagramm 1"
        
        ActiveChart.ChartArea.Select
        ActiveChart.ChartArea.ClearContents ' There was some seemingly random stuff in there, no idea where Excel got the idea to auto-fill this data. It was garbage from all across the worksheet.
        ActiveChart.Paste
        
        ActiveSheet.Shapes(MyChart).Width = 1920 * 0.75  ' I have NO idea why Excel scales my absolute values by 1 1/3, 
        ActiveSheet.Shapes(MyChart).Height = 1080 * 0.75 ' but this here results in a jpeg of the desired size of 1920x1080
        
        ActiveChart.Export Filename:=ThisWorkbook.Path & "\tmp.jpg", FilterName:="jpg"
        ActiveSheet.Shapes(MyChart).Cut
        
        Range(Target.Address).AddComment
        Range(Target.Address).Comment.Shape.Fill.UserPicture ThisWorkbook.Path & "\tmp.jpg"
        Range(Target.Address).Comment.Shape.Width = 1920
        Range(Target.Address).Comment.Shape.Height = 1080
            
        Application.ScreenUpdating = True
    End If
End Sub

Not pretty nor fast, creates a file (yuck), but seems to do the job so far. The solution in the link seems MUCH cleaner, but would require some work to get it going.

Cheers!

 

[RoryA]

creates a file (yuck)

There's no way around that, as far as I know, whichever approach you adopt.