VBA code to insert image into cell comment

[Phil Smith]

Hi everyone.

Rather than insert images manually into 116 different cells, I would like to, if possible, have it done by VBA.

I used the macro recorder to see if it was possible and came up with the following:

Range("B10").AddComment
    Range("B10").Comment.Visible = False
    Range("B10").Comment.Text Text:="Owner:" & Chr(10) & ""
    Selection.ShapeRange.ScaleWidth 13.9, msoFalse,msoScaleFromTopLeft
    Selection.ShapeRange.ScaleHeight 28.2, msoFalse, msoScaleFromTopLeft
    Selection.ShapeRange.Fill.Transparency = 0#
    Selection.ShapeRange.Line.Weight = 0.75
    Selection.ShapeRange.Line.DashStyle = msoLineSolid
    Selection.ShapeRange.Line.Style = msoLineSingle
    Selection.ShapeRange.Line.Transparency = 0#
    Selection.ShapeRange.Line.Visible = msoTrue
    Selection.ShapeRange.Line.ForeColor.RGB = RGB(0, 0, 0)
    Selection.ShapeRange.Line.BackColor.RGB = RGB(255, 255, 255)
    Selection.ShapeRange.Fill.Visible = msoTrue
    Selection.ShapeRange.Fill.ForeColor.RGB = RGB(255, 255, 255)
    Selection.ShapeRange.Fill.BackColor.RGB = RGB(255, 255, 225)
    Selection.ShapeRange.Fill.UserPicture "C:\Users\Owner\Pictures\IRUS Diagrams\401.jpg"

I know certain parts of this will have to be changed for it to run automatically within my existing code.

For example:

Range("B10").

will have to become

Sheets("Data").Cells(diagramrow,2).

where diagramrow increases each time the loop runs.

To get the filename for the picture to be chosen

"C:\Users\Owner\Pictures\IRUS Diagrams\401.jpg"

will have to become

"C:\Users\Owner\Pictures\IRUS Diagrams\" & Range("SundayDiagrams")(arrayrow, 1).Value & ".jpg"

where arrayrow increases each time the loop runs.

However, the code that has

Selection.

at the start of it, I am not sure how this needs to be changed.

Any help greatly appreciated.

Thanks.

Phil

 

[VoG]

A lot of that code is redundant (just setting the defaults). So maybe something like

Sub yetanothe()
Dim i As Long
For i = 10 To 100
    With Range("B" & i)
        .AddComment
        .Comment.Text Text:="Owner:" & Chr(10) & ""
        .Comment.ShapeRange.Fill.UserPicture "C:\Users\Owner\Pictures\IRUS Diagrams\40" & i - 9 & ".jpg"
    End With
Next i
End Sub

 

[Phil Smith]

Thanks!

Will have a go with that tomorrow and see how it goes!

Phil

 

[Phil Smith]

Looking at your reply this morning, I don't think it answers my original question...

I will definitely need the following adapted:

Selection.ShapeRange.ScaleWidth 13.9, msoFalse,msoScaleFromTopLeft
Selection.ShapeRange.ScaleHeight 28.2, msoFalse, msoScaleFromTopLeft

as that resizes the comments box. But as for the rest of the code, I think it may be redundant.

Is it possible to reword the above code to fit in with what I need?

Cheers.

 

[Phil Smith]

Any help with this one people?

 

[Phil Smith]

Re: VBA code to insert image into cell comment (Resize Comments box using VBA)

Still had no further help with this one people.

I would like to know how resize the comments box using VBA.

The code I have from the macro recorder is

Selection.ShapeRange.ScaleWidth 13.9, msoFalse,msoScaleFromTopLeft
Selection.ShapeRange.ScaleHeight 28.2, msoFalse, msoScaleFromTopLeft

but need it use with different cells using variables for the row and column as the cells have the comments added.

Any help at all?

 

[Phil Smith]

No further help on this either?

Phil

 

[RoryA]

You haven't said how you want them scaled. If you want those values every time then you just need to add that at the end using the .Comment.Shaperange rather than Selection.Shaperange

 

[Phil Smith]

Thanks!

I will need those values each time as each image is the same size.

I will try that and see how it goes.