Archive of Mr Excel Message Board
Back to General Excel archive index
Back to archive home
Insert Picture MacroPosted 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.
Re: Insert Picture MacroPosted 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.
Dim PicLocation as String
PicLocation = Application.GetSaveAsFilename("C:\Windows\My Documents", "Image Files (*.gif),*.gif", , "Specify Image Location")
If PicLocation <> "" Then
Re: Insert Picture MacroPosted 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)
Re: Insert Picture MacroPosted 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.
Re: Insert Picture MacroPosted 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 _
Re: Insert Picture MacroPosted 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").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).
Re: Insert Picture MacroPosted by Ivan Moala on October 04, 2000 3:27 AM
Try this routine;
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"
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
Set PictCell = Application.InputBox("Select the cell to insert into", Type:=8)
If PictCell.Count > 1 Then MsgBox "Select ONE cell only": GoTo GetCell
Re: Insert Picture MacroPosted by T. Fornay on October 04, 2000 10:08 AM
Thanks, that's exactly what I was looking for.
This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store
to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.