VBA Macro Input Box to pull Jpg Image from a folder

[maurovescera]

Hello,

I am hoping someone can help me, I am trying to write a macro in VBA that a user can use an input box to select their folder an image will be located, and the title of the image will be in Column A and the image will be loaded to column based on the name in column A. My code is below and it pulls the image now, but needs the folder location manually input in the macro, and cannot get the input box to work without selecting the title name. Any suggestions?

Sub InsertPicsr1()
Dim fPath As String, fName As String
Dim r As Range, rng As Range
Dim shpPic As Shape

Application.ScreenUpdating = False
fPath = "Q:\Mauro Vescera\Sourcing Projects\Article_Image_Test\"
Set rng = Range("A1:A100000" & Cells(Rows.Count, 4).End(xlUp).Row)
For Each r In rng
On Error GoTo errHandler
If r.Value <> "" Then
Set shpPic = ActiveSheet.Shapes.AddPicture(Filename:=fPath & r.Value & ".jpg", linktofile:=msoFalse, _
savewithdocument:=msoTrue, Left:=Cells(r.Row, 2).Left, Top:=Cells(r.Row, 2).Top, Width:=-20, Height:=-20)
With shpPic
.LockAspectRatio = msoTrue
If .Width > Columns(2).Width Then .Width = Columns(2).Width
Rows(r.Row).RowHeight = .Height
End With
End If
errHandler:
If Err.Number <> 0 Then
Debug.Print Err.Number & ", " & Err.Description & ", " & r.Value
On Error GoTo -1
End If
Next r
Application.ScreenUpdating = True
End Sub

 

[Jon Peltier]

You can use this line to allow a user to browse to a folder and select one or more JPG files.

Dim vFiles As Variant
vFiles = Application.GetOpenFilename(Title:="Select JPG file(s)", FileFilter:="JPG Files (*.jpg),*.jpg", MultiSelect:=True)

This does not open the files, it only returns the file names selected in the dialog.

If MultiSelect is False, GetOpenFileName returns a string the full path/filename of one file.
If MultiSelect is True, GetOpenFileName returns an array with the full path/file name of each selected file.

If the user cancels the dialog, GetOpenFileName returns the Boolean value False. To test for this, use this:

If TypeName(vFiles) = "Boolean" then ' exit the sub