Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

VBA Visual Basic for Applications (Microsoft) FAQ

Excel How To

Insert a picture into a cell's comment box by renigar
Posted: 1 Sep 12 (Edited 22 Sep 12)

The following code when ran will open the select file dialog box, the user selects a picture, then an input box asks for the percent of scaling to apply to the display of the picture. The picture will be inserted into the comment box of the currently selected cell. The picture will popup from the worksheet when the mouse hovers over the cell just as a normal comment would. I've commented everything fairly well to give a decent understanding of how it works.

CODE -->

Sub InsertPhotoInComment()
'   Created in Excel 2010/Win 7, also tested: Excel 2003/Vista, Excel 2007/WinXP.
'   Macro allows user to put a picture in a comment box and
'   specify scaled % display size. Note: pictures that have a large
'   file size will dramatically increase workbook file size.

'   Create variables
Dim Finfo As String         ' Used for file extension filters
Dim FilterIndex As Integer  ' Used to indicate default file extension
Dim Title As String         ' Used to hold the file dialog title text
Dim FileName As Variant     ' Holds the file name that is selected
Dim commentBox As Comment   ' Holds the comment box
Dim myImg As Variant        ' Holds the picture, for find dimensions
Dim ZF As Variant           ' Holds the picture zoom factor from inputbox

'   Allows the user to use the File Open dialog to select a file.
'   Set up list of file extension filters for the file type drop down,
'   I used the most common only. Others could be added.
Finfo = "All Files (*.*),*.*,(*.Jpg),*.jpg,(*.png),*.png,(*.tif),*.tif,(*.bmp),*.bmp"

'   Display *.jpg files by default. You can have more extensions in the above list.
'   The FilterIndex determines what number in the list shows by default.
FilterIndex = 2

'   Set the dialog box title caption
Title = "Select a Picture File to Insert into Comment Box"

'   Open the select file dialog box
FileName = Application.GetOpenFilename(Finfo, _
FilterIndex, Title)

'   Show message if no file selected
If FileName = False Then
    MsgBox "No file was selected." & _
    vbNewLine & "Macro will terminate."
End If

'   Exit macro if no file is selected
If FileName = False Then Exit Sub

Set myImg = LoadPicture(FileName)

'   Prompt user for scaling zoom % factor
On Error Resume Next
ZF = InputBox(Prompt:="Your selected file path:" & _
    vbNewLine & FileName & _
    vbNewLine & "Input zoom % factor to apply to picture?" & _
    vbNewLine & "Original picture size equals 100." & _
    vbNewLine & "Input a number greater than zero!", _
    Title:="Picture Scaling Percentage Factor")
If ZF = "" Then Exit Sub

'   Warning message if not number greater than zero and exit macro
If Not IsNumeric(ZF) Or ZF = 0 Then MsgBox "Entered value must be a number greater than zero"
If Not IsNumeric(ZF) Or ZF = 0 Then Exit Sub
' Any existing comments must be cleared before adding a new one.

Set commentBox = ActiveCell.AddComment

'   Put picture into comment, set attributes and scale display size
With commentBox
    .Text Text:=""
    With .Shape
       .Fill.UserPicture (FileName)
       .Width = myImg.Width * ZF / 2645.9
       .Height = myImg.Height * ZF / 2645.9
    End With
    .Visible = False
End With

End Sub 

Back to VBA Visual Basic for Applications (Microsoft) FAQ Index
Back to VBA Visual Basic for Applications (Microsoft) Forum

My Archive

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close