On Mar 15, 7:05 pm, "Peter T" <peter_t@discussions wrote:
This adds an image control which has an autosize property. The control could
be on any sheet, shoose to keep/delete or hide it

Function Pic2Image(ws As Worksheet, _
wd As Single, ht As Single, _
sPic As String, _
Optional bVis As Boolean = True, _
Optional bDelete = False) As Boolean
Dim ole As OLEObject
Dim r As Range
On Error Resume Next
Set ole = ws.OLEObjects("myImage1")
On Error GoTo 0
If ole Is Nothing Then
Set ole = ws.OLEObjects.Add("Forms.Image.1")
Set r = Range("A1")
With ole
.Name = "myImage1"
.Left = r.Left
.Top = r.Top
.Visible = bVis
End With
End If

ole.Object.Picture = LoadPicture(sPic)
ole.Object.AutoSize = True
With ole
wd = .Width
ht = .Height
.Visible = bVis
End With

If bDelete Then ole.Delete

End Function

Sub Pic2Comment()
Dim w As Single, h As Single
Dim sPicFile As String
Dim cm As Comment

sPicFile = "C:\myPicture.gif"

Pic2Image ActiveSheet, w, h, sPicFile, True

With Range("D9")
On Error Resume Next
Set cm = .Comment
On Error GoTo 0
' might prefer to delete existing comment

If cm Is Nothing Then
Set cm = .AddComment
End If
End With

cm.Shape.Width = w
cm.Shape.Height = h
cm.Shape.Shadow.Visible = msoFalse
cm.Shape.Fill.UserPicture sPicFile

End Sub

For your needs adapt the range & file name into a loop

Regards,
Peter T

"fishbyname" wrote in message

oups.com...

I have a piece of code that sets a cells comment to be an image

i can set the width and height of this comment but i am trying to find
a way of setting this dynamically

what i need to do is go to an image and find out its width and height
then i can use these values to set the dimensions of the comment

is there a way to do this?

superb, changed the range,.added in a loop and hid the image control
and it works perfectly

cheers, been trying to do this for ages