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?

Here is a trick. I ran the code below and added a watch for shp. Then
steped through the code looked inthe watch window to see the properties that
were available. Then I added the code to verify that i could read the
parameters.


Sub getdim()

For Each shp In Sheets("sheet1").Shapes

MyHeight = shp.Height
Mytop = shp.Top
Mywidth = shp.Width

Next shp

End Sub


"fishbyname" wrote:

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?

that will get the size of the shape but i need the size of the image
in the file to be able to set the dimensions of the shape

here is the code i have at the moment:

Sub pic()

Dim rng As Range
Dim shp As Comment

findlastrow

For i = 12 To first_blank - 1

Set rng = Range("H" & i)

If Not rng.Comment Is Nothing Then
rng.Comment.Delete
End If

If rng.Text < "" Then

Set shp = rng.AddComment("")
shp.Shape.Fill.UserPicture "c:\Screenshots\" &
Range("H" & i).Value & ".gif"

shp.Shape.width = 111
shp.Shape.Height = 92

End If

Next i

End Sub

i want to be change the values of 111 and 92 to the width and height
of the picture it is inserting in the previous line

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?

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