Automate Cell comments with Images 1

[Dashley]

I want to add a comment (a picture) in Col D (Book Title). The image file name is composed of the ISBN number in Col B with the extension " .jpg " .
The files are located at "C:\Users\Dash\Pictures\wordsworth\MSTRCovers05122016Fixed".
So "C:\Users\Dash\Pictures\wordsworth\MSTRCovers05122016Fixed\9781853262418.jpg" would be the first comment image in B4.

The images are 200px wide and height around 312px.

Tried using a MACRO but that's not happening

I can get the image comments manually but I have just under 1k rows to do and would really like to let some code perform the task. Haven't used VBA in many years.

Thanks

-dan


Below- 1st Row contains column headers

CAT ISBN AUTHOR BOOK TITLE
CL 9781853262418 Henry, O. 100 Selected Stories
CL 9781853261923 Eliot, G. Adam Bede
CL 9781853260339 Doyle, A.C. Adventures & Memoirs of Sherlock Holmes
CL 9781853262630 Virgil Aeneid

 

[SkipVought]

Hi,

A "picture" and a "Comment" are two different animals. A "Comment" is a particular feature associated with a cell. You can Show/Hide Comments. Comments contain text.

A "picture" is a graphic object that is associated with the sheet, but can be sized and positioned to appear to be associated with a cell.

So which are we talkin'bout? Maybe both?

Turn on your macro recorder and record doing what you've figgured out works. Then post your recorded code.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Dashley]

Hi Skip,

The Macro isn't really showing all the steps I took but I've pasted it below. The problem with the Macro is that it wasn't capturing all the events from the opened dialog windows.

Here are the steps to inset an Image into a comment instead of just text. You can have an image pop up like a mouse over event in the comment field instead of text.


Right Click Cell where you want the comment
Select Insert Comment
Delete the text showing in in the comment block
Rigth Click on comment outer edge
Select Format Comment
Select Colors and Lines Tab
In the Fill area clock the color down arrow
Select Fill Effects
Select Picture Tab
Select Select Picture
Select from a file
Pick a file name from you chosen path
Select Insert, Ok and your done.

Sub ImageComment()
'
' ImageComment Macro
'

'
    Range("C7").Select
    Selection.Copy
    Application.CutCopyMode = False
    Selection.Copy
    Application.CutCopyMode = False
    Selection.Copy
    Range("E7").Select
    Application.CutCopyMode = False
    Range("E7").AddComment
    Range("E7").Comment.Visible = False
    Range("E7").Comment.Text Text:=""
    With Selection.Font
        .Name = "Tahoma"
        .FontStyle = "Bold"
        .Size = 9
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With
    Selection.ShapeRange.ScaleWidth 1.72, msoFalse, msoScaleFromTopLeft
    Selection.ShapeRange.ScaleHeight 4.71, msoFalse, msoScaleFromTopLeft
End Sub

 

[SkipVought]

I get your right down to
[tt]
Select Picture Tab
Select Select Picture
Select from a file
Pick a file name from you chosen path
Select Insert, Ok and your done.
[/tt]

Your code adds a Comment to E7, but then... PICTURE TAB??? Where's that?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Dashley]

The steps I posted are the actual manual steps I had taken to inset a comment Picture. The Macro wouldn't pick all the steps up.

I posted them in case you wanted to try and insert an image into a comment to try it.

 

[strongm]

>PICTURE TAB??? Where's that?

Skip, if you right-click the BORDER of the comment rather than the comment itself, you get the extra tabs shown in Dashley's post.

 

[strongm]

Dashley, the basic VBA is:

[tt]ActiveCell.Comment.Shape.Fill.UserPicture "<filename>"[/tt]

 

[Gavona]

This question rang bells..... Sounds as if faq707-7583 will help?

Gavin

 

[Dashley]

Ok I'm going to try the VBA Code Strongm along with bits of the Procedure code Gavin supplied and see if I can't get something going.

I'l let you know how it goes.


-dan

 

[Dashley]

Got it.


Thank you Now all I have to do is Loop it through the sheet and woolah it's finished.
Really appreciate the help.


-dan

Sub test()

Dim FileNameIs As String
Dim commentBox As Comment
Dim myImg As Variant
          
FileNameIs = "C:\Users\Dashley\Pictures\wordsworth\MSTRCovers05122016Fixed\9781840220780.jpg"
 
Range("F16").Select

ActiveCell.Value = FileNameIs  

Set myImg = Nothing

Set myImg = LoadPicture(FileNameIs)

ActiveCell.ClearComments

Set commentBox = ActiveCell.AddComment

With commentBox
    .Text Text:=""
    With .Shape
       .Fill.UserPicture (FileNameIs)
       .Width = 200
       .Height = 310
    End With
    .Visible = False
End With

End Sub