MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Insert Picture Macro


Posted by T. Fornay on October 02, 2000 12:51 PM

How can I create a macro that will prompt user to insert a picture into an Excel form? The user must be able to select any picture from their subdirectories.


Posted by Ben on October 03, 2000 6:57 AM

I didn't test this, but something along its lines shoudl work. GetSaveAsFilename is the only method I know of to bring up a common dialog box in that lets the user specify the location of a file. You can replace C:\Windows\My Document with whatever directory you want the macro to open by default, or omit it altogether. And you can replace .gif or add another file type.

-Ben


Dim PicLocation as String
PicLocation = Application.GetSaveAsFilename("C:\Windows\My Documents", "Image Files (*.gif),*.gif", , "Specify Image Location")
If PicLocation <> "" Then
ActiveSheet.Pictures.Insert(PicLocation).Select
Else
Exit Sub
End If

Posted by Tim Francis-Wright on October 03, 2000 8:12 AM

I would use GetOpenFilename, which should fir the bill exactly.

I have a macro that has:

SelectedFile = Application.GetOpenFilename _
(FileFilter, FilterIndex, Title, ButtonText, MultiSelect)

HTH

Posted by T. Fornay on October 03, 2000 10:57 AM

Unfortunately, neither macro will insert a jpg file (logo) into my document (which is protected).
I get the open file dialog box, select the file, but the image is not inserted. The cells are not locked either.

Any suggestions?


Thanks.

Posted by T. Fornay on October 03, 2000 11:37 AM

This does not work on a protected sheet, and I need my sheet protected. I would use GetOpenFilename, which should fir the bill exactly. I have a macro that has: SelectedFile = Application.GetOpenFilename _

Posted by Ben O. on October 03, 2000 2:09 PM

You could always unprotect your worksheet in the macro, then protect it again before it stops.

Sheets("Sheet 1").Unprotect
.
.
.
Sheets("Sheet 1").Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

I think you can even have the macro fill in a password in if one is required to unprotect the document. If you're worried about users going into the VBA Editor to see the password, you could password protect your VBA project and prevent them from viewing the code at all.

-Ben Unfortunately, neither macro will insert a jpg file (logo) into my document (which is protected).

Posted by Ivan Moala on October 04, 2000 3:27 AM

Try this routine;

Sub Insert_Pict()
Dim Pict
Dim ImgFileFormat As String
Dim PictCell As Range
Dim Ans As Integer

ActiveSheet.Protect True, True, True, True, True
ImgFileFormat = "Image Files (*.bmp),others, tif (*.tif),*.tif, jpg (*.jpg),*.jpg"

GetPict:
Pict = Application.GetOpenFilename(ImgFileFormat)
'Note you can load in any nearly file format
If Pict = False Then End

Ans = MsgBox("Open : " & Pict, vbYesNo, "Insert Picture")
If Ans = vbNo Then GoTo GetPict

'Now paste to userselected cell
GetCell:
Set PictCell = Application.InputBox("Select the cell to insert into", Type:=8)
If PictCell.Count > 1 Then MsgBox "Select ONE cell only": GoTo GetCell
PictCell.Select
ActiveSheet.Pictures.Insert(Pict).Select


End Sub

HTH


Ivan

Posted by T. Fornay on October 04, 2000 10:08 AM

Thanks, that's exactly what I was looking for.