Quick way of adding an image in a comment (popup picture)

[prephil]

I work regularly on a particular type of Excel document that requires me to insert images so that my colleagues can understand what I'm referring to (related to mechanical design and a picture speaks a 1000 words as they say).

In order to keep the document neat and tidy, I insert comments and then add the image to the comment (technically it's just the background of the comment that is filled with the image I select). See Add a Pop-Up Picture to a Cell in Excel for the exact method of how to do this.

This whole process is rather slow and laborious when it has to be repeated over and over. Can this process by done via a macro? I'm not too hot on macros unfortunately. My understanding is that the procedure can be recorded via a macro and a button assigned to repeat the act again?

[/IMG]

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG28Jul12
[COLOR="Navy"]Dim[/COLOR] ws [COLOR="Navy"]As[/COLOR] Worksheet
[COLOR="Navy"]Dim[/COLOR] sName [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] cmt [COLOR="Navy"]As[/COLOR] Comment
[COLOR="Navy"]Dim[/COLOR] rng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] TheFile
[COLOR="Navy"]Set[/COLOR] ws = ActiveSheet
[COLOR="Navy"]Set[/COLOR] rng = ActiveCell

[COLOR="Navy"]With[/COLOR] Application.FileDialog(msoFileDialogFilePicker)
         .AllowMultiSelect = False          '[COLOR="Green"][B]Only one file[/B][/COLOR]
         .InitialFileName = CurDir         '[COLOR="Green"][B]directory to open the window[/B][/COLOR]
         .Filters.Clear                    '[COLOR="Green"][B]Cancel the filter[/B][/COLOR]
         .Filters.Add Description:="Images", Extensions:="*.jpg,*.png,*.gif", Position:=1
         .Title = "Choose image"
         [COLOR="Navy"]If[/COLOR] .Show = -1 [COLOR="Navy"]Then[/COLOR] TheFile = .SelectedItems(1) Else TheFile = 0
    [COLOR="Navy"]End[/COLOR] With
'[COLOR="Green"][B]No file selected[/B][/COLOR]
[COLOR="Navy"]If[/COLOR] TheFile = 0 [COLOR="Navy"]Then[/COLOR]
MsgBox ("No image selected")
[COLOR="Navy"]Exit[/COLOR] [COLOR="Navy"]Sub[/COLOR]
[COLOR="Navy"]End[/COLOR] If


[COLOR="Navy"]If[/COLOR] rng.Comment [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR]
[COLOR="Navy"]Set[/COLOR] cmt = rng.AddComment
cmt.Text Text:=""
    [COLOR="Navy"]With[/COLOR] cmt.Shape
      .Fill.UserPicture TheFile
      .Width = 100
      .Height = 100
      .Visible = True
    [COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]Else[/COLOR]
[COLOR="Navy"]Set[/COLOR] cmt = rng.Comment
'[COLOR="Green"][B]cmt.Text Text:=""[/B][/COLOR]
    [COLOR="Navy"]With[/COLOR] cmt.Shape
      .Fill.UserPicture TheFile
      .Width = 100
      .Height = 100
      .Visible = True
    [COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick