Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Insert a picture into a cell's comment box

Excel How To

Insert a picture into a cell's comment box

by  renigar  Posted    (Edited  )
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()
[color green]' 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[/color]
Dim Finfo As String [color green]' Used for file extension filters[/color]
Dim FilterIndex As Integer [color green]' Used to indicate default file extension[/color]
Dim Title As String [color green]' Used to hold the file dialog title text[/color]
Dim FileName As Variant [color green]' Holds the file name that is selected[/color]
Dim commentBox As Comment [color green]' Holds the comment box[/color]
Dim myImg As Variant [color green]' Holds the picture, for find dimensions[/color]
Dim ZF As Variant [color green]' Holds the picture zoom factor from inputbox[/color]

[color green]' 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.[/color]
Finfo = "All Files (*.*),*.*,(*.Jpg),*.jpg,(*.png),*.png,(*.tif),*.tif,(*.bmp),*.bmp"

[color green]' 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.[/color]
FilterIndex = 2

[color green]' Set the dialog box title caption[/color]
Title = "Select a Picture File to Insert into Comment Box"

[color green]' Open the select file dialog box[/color]
FileName = Application.GetOpenFilename(Finfo, _
FilterIndex, Title)

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

[color green]' Exit macro if no file is selected[/color]
If FileName = False Then Exit Sub

Set myImg = LoadPicture(FileName)

[color green]' Prompt user for scaling zoom % factor[/color]
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

[color green]' Warning message if not number greater than zero and exit macro[/color]
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

[color green]' Any existing comments must be cleared before adding a new one.[/color]
ActiveCell.ClearComments

Set commentBox = ActiveCell.AddComment

[color green]' Put picture into comment, set attributes and scale display size[/color]
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
[/code]
Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top