Ok, so I have a worksheet that is essentially a register of files, users upload files to a server location using this tool, the workbook keeps a log of the files and provides a hyperlink to them.
When a user uploads an image file I have the code then adding a comment box to that cell, set to hidden, and insert the image as the background of the comment so on hovering their mouse they get a preview of the picture.
.autosize doesn't work of course on the image, so I use code I get from a post by Peter Thornton on Microsoft forums, that places an image control on the sheet, then uses the height and width of that to resize the comment box, before then removing the image from the worksheet before anyone sees it ;-)
This all works great, until someone comes along with a high resolution picture and my preview doesn't even fit on the monitor.....
How do I set the maximum size of Pict1 below (Line 15) while keeping the aspect ratio locked?
If sFileExt = ".jpeg" Or sFileExt = ".jpg" Or sFileExt = ".bmp" Or sFileExt = ".gif" Or sFileExt = ".jpeg" Or sFileExt = ".jpeg" Or sFileExt = ".tiff" Or sFileExt = ".png" Or sFileExt = ".JPG" Or sFileExt = ".BMP" Or sFileExt = ".GIF" Or sFileExt = ".JPEG" Or sFileExt = ".TIFF" Or sFileExt = ".PNG" Then
Range("A" & lRow).Select
ActiveCell.Comment.Visible = False
ActiveCell.Comment.Shape.ScaleHeight 3#, msoFalse, msoscaleformtopleft
ActiveCell.Comment.Shape.ScaleWidth 2.4, msoFalse, msoscaleformtopleft
' Insert image control Pic to set Picture height and width
' Then remove the picture, but leave the control so it's dimensions
' can be used to size the image
Set Pict1 = ActiveSheet.Pictures.Insert(sLink)
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlTop
.ReadingOrder = xlContext
.Orientation = xlHorizontal
.Height = Pict1.Height
.Width = Pict1.Width
Set Pict1 = Nothing