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

prephil

New Member
Joined
Jan 20, 2019
Messages
6
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?

48393641592_07f48c019c_b.jpg
[/IMG]
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
Try this:-

Code:
[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
 

Forum statistics

Threads
1,141,417
Messages
5,706,311
Members
421,441
Latest member
VapesRub

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top