Pasting Pictures into a Comment

For more discussion, see https://www.thespreadsheetguru.com/the-code-vault/vba-insert-image-into-cell-comment

@AndrésnSee https://sites.google.com/view/myexceltoolbox/Comment for continued discussion.

Hello Woolley,nnI forgot to mention, the file is not blocked.nnKRnnAndrés

Hello Woolley,nnUnfortunatelly, I'm not able to see the "new" ribbon after the add-on is installed.nThe add-on is also active, but not present somehow. I'm using Office 365.nnAny ideas?.nnThanks.nnKRnA. López

For more on this subject, see https://excelribbon.tips.net/T013280_Placing_a_Picture_in_a_Comment.htmlnnMy Excel Toolbox includes the following function that will add a background image to a comment:n=ImageInComment(ImageFile,[Target],[ScaleFactor],[RotateAngle],[NoAuthor])nSee https://sites.google.com/view/MyExcelToolbox/

Hello There,nnHere is the code that I use, I'm not able to check if there is a picture in the clipboard yet. Maybe someone can help me. BTY, the code is not mine unfortunatelly, credits to the author:nnOption ExplicitnSub PictureExport()nDim TempChart As String, Picture2Export As StringnDim PicWidth As Long, PicHeight As LongnDim ActSheet As Stringn nActSheet = ActiveSheet.NamennActiveSheet.PastenSelection.Name = "PrintScreen"nPicture2Export = Selection.Namenn n'Store the picture's height and width in a variablenWith Selectionn PicHeight = .ShapeRange.Heightn PicWidth = .ShapeRange.WidthnEnd Withn n'Add a temporary chart in sheet1nCharts.AddnActiveChart.Location Where:=xlLocationAsObject, Name:=ActSheetnSelection.Border.LineStyle = 0nTempChart = Selection.Name & " " & Split(ActiveChart.Name, " ")(2)n nWith ActiveSheetn 'Change the dimensions of the chart to suit your needn With .Shapes(TempChart)n .Width = PicWidthn .Height = PicHeightn End Withn n 'Copy the picturen .Shapes(Picture2Export).Copyn n 'Paste the picture in the chartn With ActiveChartn .ChartArea.Selectn .Pasten End Withn n 'Finally export the chartn .ChartObjects(1).Chart.Export Filename:=ThisWorkbook.Path & "\tmp.jpg", FilterName:="jpg"n .Shapes(TempChart).Cutn End WithnnActiveCell.AddCommentnActiveCell.Comment.Shape.Fill.UserPicture ThisWorkbook.Path & "\tmp.jpg"nActiveCell.Comment.Shape.Width = PicWidthnActiveCell.Comment.Shape.Height = PicHeightnActiveSheet.Shapes("PrintScreen").DeletenKill ThisWorkbook.Path & "\tmp.jpg"nApplication.ScreenUpdating = TruenEnd Sub

@ J. WoolleynnOr you could put this formula in a worksheet cell:nnDoes not work fine (comma):nn=ImageInComment("D:/Test/Excel/0-Bilder/Erdbeere-10.jpg",H7)nnWorks fine (semicolon):nn=ImageInComment("D:/Test/Excel/0-Bilder/Erdbeere-10.jpg";H7)

@ J. WoolleynnThank you very much.nnWorks fine:nnSub Image_In_Comment()nImageInComment "D:/Test/Excel/0-Bilder/Erdbeere-10.jpg", Range("H7")nEnd SubnnDoes not work fine (Compile error: Variable not defined)nnSub Image_In_Comment()nResult = ImageInComment("D:/Test/Excel/0-Bilder/Erdbeere-10.jpg", Range("H7")) '** Compile error: Variable not definednEnd SubnnWhat am I doing wrong?nnWorks fine in the Immediate Window:nnResult = ImageInComment("D:/Test/Excel/0-Bilder/Erdbeere-10.jpg", Range("H7"))

@Kurt MüllernAs described in my earlier comments, ImageInComment is a Function, not a Sub, so it can be used in a cell formula. If you want to use a Function in VBA instead of a cell formula and ignore the returned result, your example with a Call statement will work. The Application.Evaluate shortcut [H7] is OK, but Range("H7") would be better. Here are two other ways to use the Function in VBA:nImageInComment "D:/Test/Excel/0-Bilder/Erdbeere-10.jpg", Range("H7")nresult = ImageInComment("D:/Test/Excel/0-Bilder/Erdbeere-10.jpg", Range("H7"))nOr you could put this formula in a worksheet cell:n=ImageInComment("D:/Test/Excel/0-Bilder/Erdbeere-10.jpg",H7)

@ J. WoolleynnI found the solution now:nnSub ImageInComment()nCall ImageInComment("D:/Test/Excel/0-Bilder/Erdbeere-10.jpg", [H7])nEnd SubnnI had to enter the above macro in a modul by myself.

@ J. WoolleynnI can find Register ImageInComment function (Private Sub ImageInComment_Register()), Image in Target's unthreaded Comment (Public Function ImageInComment(ImageFile, [Target}, [ScaleFactor], [RotateAngle], [NoAuthor])) but I can not find a "Sub ImageInComment".nnWhat am I probably doing wrong?

For similar discussion, including the ImageInComment(FilePath,[Target]) function, see:nhttps://excelribbon.tips.net/T013280_Placing_a_Picture_in_a_Comment.html#comment-form-hd

If a picture is pasted into a comment, the picture takes the size of the comment shape. After that the picture is already distorted.nnIf distortion of the picture shall be prevented, the following procedure is necessary:nn- After choosing the pic it is necessary to calculate the scale of the picturen- With the scale of the picture the size of the comment shape has to be changed to the scale of the picturen- If the picture is pasted into the comment shape - having the same scale as the picture - then the picture is not distortedn- Just now the aspect ratio of the comment shape has to be locked. It is not possible to lock the aspect ratio of the picture pasted into the comment shapennSub insert_userpicture_in_comments()nn'** Dimensionierung der Variablenn Dim rngZelle As Rangen Dim strFilename As Variantn Dim strFilter As Stringn Dim ScaleValue As Singlen Dim ScaleValue2 As Singlen Dim objPic As IPictureDispn Dim Source As String n Dim i As Integern n If ActiveSheet.Comments.Count = 0 Thenn MsgBox "No comments in entire sheet"n Exit Subn End Ifn n'Dateiauswahl filternn strFilter = "JPG Files (*.jpg), *.jpg" _n & ", GIF Files (*.gif), *.gif" _n & ", Bitmaps (*.bmp), *.bmp" _n & ", WMF Files (*.wmf), *.wmf"n n ' Dialogfenster zur Auswahl eines Bildes öffnenn strFilename = Application.GetOpenFilename(strFilter)n DoEventsnn' Wenn kein Bild ausgewählt wurde, Prozedur beendenn If strFilename = False Then GoTo LabelAnn Source = (CStr(strFilename))n DoEventsn n' Set objPic = LoadPicture(Bild)n Set objPic = LoadPicture(Source)n DoEventsn n With objPicn ScaleValue = .Width / .Heightn End Withn n If MsgBox(ScaleValue, vbOKCancel) = vbCancel Then GoTo LabelAnn Application.CutCopyMode = Falsen DoEventsnn Application.ScreenUpdating = Falsen Application.Calculation = xlCalculationManualnn'** Alle markierten rngZellen durchlaufennFor Each rngZelle In Selection.Cellsn With rngZellen If Not .Comment Is Nothing Thenn'Insert The Image and Resizen With .Comment.Shape n .LockAspectRatio = msoFalsen .Width = 150n' .Width = ScaleValue * .Heightn .Height = .Width / ScaleValuen .Fill.UserPicture strFilenamen DoEvents n .LockAspectRatio = msoTruen n ScaleValue2 = .Width / .Height n i = i + 1n Debug.Print i; rngZelle.Address; ScaleValue; ScaleValue2n n End Withn End Ifn End WithnNext rngZellennLabelA: n Application.Calculation = xlCalculationAutomaticn Application.ScreenUpdating = Truen On Error GoTo 0nEnd Sub

@RonmionFor example, to make the height of the comment shape 100 pixels, change the code after Else as follows:nn ActiveCell.Comment.Shape.Fill.UserPicture PicChoicen ActiveCell.Comment.Shape.LockAspectRatio = Truen ActiveCell.Comment.Shape.Height = 100nnWhen you use VBA to change the comment shape's height, its width will also change because aspect ratio is locked. In this case, the JPG image resizes to match the comment's shape because it was inserted as a background (Fill).nnNotice you can manually change the comment shape's height or width independently by dragging a side-handle, but dragging a corner-handle will change both height and width to maintain aspect ratio.

What might the VBA code that sizes the JPG look like?

It never occurred to me that an image could be pasted into a comment, or why I would want to, but I still found this fascinating. Thanks!