Userforms in Excel can give a professional look and feel to any Excel project, database or data entry system. Several of our recent projects include dynamic pictures where the clients images are used to show stock from their own PC or directly from a webpage. Either way can be used to show any image from your hard drive or directly from a webpage.
The example code below should be added to a userform rather than a standard VBA module, in the userform initialize event. The example hard drive location should be changed to the location of a file on your own system, but the web based URL image will work directly.
We’d be happy to assist on any Excel or data project you are currently working on or looking to achieve. Leave a message below or contact us through the form. We’ll get straight back to you.
Copy this first part of the code into a VBA Module – say Module 1 for ease of reference. Please note this will NOT work on 64-Bit versions of Excel.
Private Declare Function URLDownloadToFile Lib "urlmon" Alias _
"URLDownloadToFileA" (ByVal pCaller As Long, ByVal szURL As String, ByVal _
szFileName As String, ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long
Public Function DownloadFilefromWeb(url As String, fileName As String) As Boolean
DownloadFilefromWeb = URLDownloadToFile(0, url, fileName, 0, 0)
Then the following code goes into your userform.
'Work with the pictures from a URL
Dim url_path As String
Dim file_path As String
url_path = I1
file_path = ThisWorkbook.Path & "\" & "Image.png"
' Download picture
DownloadFilefromWeb url_path, file_path
' Load picture
.Picture = LoadPicture(file_path)
' Delete Saved file