Assistance requested: Macro to create specially formatted comment?

[Guest]

I occasionally need to include a photo in an Excel sheet (such as a
serial number plate or an aircraft part) and learned a handy,
unobtrusive method:

In a blank cell (I usaully have text "Photo" entered as a user hint),
I insert a comment but delete the text (usually my profile/login
name), then I format the cell for color/fill/picture and browse choose
the photo. When a user subsequently points to the cell, the photo
pops up. When the mouse is pointed away, the photo disappears. File
size is not an issue - I have relatively unlimited capacity and the
small JPG photos don't eat up much space anyway.

Well, that's all fine & dandly, but takes a dozen or so clicks - even
if all the photos have been gatherd into a single folder. SO I tried
to record a macro that takes me all the way to the photo selection
step, but I cannot figure out how to get it to work. With the browse
window open, I cannot stop the macro recorder.

Any help (or a sharp rap on the head, if necessary) to show me how to
do this would be much appreciated! BTW, using Excel 2000 mostly but
sometimes 2002 & 2003 versions.

/Lac/

 

[Bob Phillips]

If you mean some code to select and open the file for you, take a look at
the GetOpenFilename method in VBA help.

 

[Guest]

I shall do that as soon as I get the chance - thanks. But I thunk the
macro recorder would simply capture the steps I take manually....
Hey, wait a minute! Might the problem be that I am using the mouse to
navigate the menus down to where I pick the file? Will the macro
recorder track and record selections made with mouse clicks? No that
I think about it, I hadn't tried it with the keyboard.

/Lac/

 

[Dave Peterson]

I think Bob meant to actually use application.getopenfilename inside your macro
to prompt you for the filename of the picture to insert.

I recorded a macro when I did it manually. Then I modified that code to look
like this:

Option Explicit

Sub testme01()

Dim myPictureName As Variant

myPictureName = Application.GetOpenFilename _
(filefilter:="Picture Files,*.jpg;*.bmp;*.tif;*.gif")


If myPictureName = False Then
Exit Sub 'user hit cancel
End If


With ActiveCell
'delete existing comments
If .Comment Is Nothing Then
'do nothing
Else
.Comment.Delete
End If
.AddComment
With .Comment.Shape
.Fill.Transparency = 0#
.Line.Weight = 0.75
.Line.DashStyle = msoLineSolid
.Line.Style = msoLineSingle
.Line.Transparency = 0#
.Line.Visible = msoTrue
.Line.ForeColor.RGB = RGB(0, 0, 0)
.Line.BackColor.RGB = RGB(255, 255, 255)
.Fill.Visible = msoTrue
.Fill.ForeColor.RGB = RGB(255, 255, 255)
.Fill.BackColor.SchemeColor = 80
.Fill.UserPicture myPictureName
End With
End With

End Sub

I usually throw away the stuff that I don't need (all that .comment.shape stuff
that is ok with the defaults), but I left them in case you want to modify any of
it.

 

[Bob Phillips]

Dave,

As ever you see what I do not <vbg>. I read that latest post that the OP was
working his way through it okay. Seeing what I want I guess <evbg>.

Bob

 

[Dave Peterson]

It makes life a lot easier if I only see the things I want to see!

 

[Guest]

Ohhh, yeah! I finally got a chance to work on this and your code
worked perfectly. This is gonna save me some time - especially when I
map it to a tool bar button or hot key.

If I might impose once more: the picture your code inserts is quite
small compared to the original graphic file. Which line is
responsible for the scaling, or is that just default behavior? If it
is default behavior, is there a way to predetermine a different
picture size so all photos come up the same size? (I realize I can
manipulate the photos by manually inserting them when necessary.)

Thanks to all who particpated in this thread.

/Lac/

 

[Bob Phillips]

Change the Comment.Shape bock to

With .Comment.Shape
.Fill.Transparency = 0#
.Line.Weight = 0.75
.Line.DashStyle = msoLineSolid
.Line.Style = msoLineSingle
.Line.Transparency = 0#
.Line.Visible = msoTrue
.Line.ForeColor.RGB = RGB(0, 0, 0)
.Line.BackColor.RGB = RGB(255, 255, 255)
.Fill.Visible = msoTrue
.Fill.ForeColor.RGB = RGB(255, 255, 255)
.Fill.BackColor.SchemeColor = 80
.Fill.UserPicture myPictureName
.Height = 50
.Width = 100
End With
End With

 

[Guest]

Simple enough! Thanks again.

/C/