Browse, insert, copy image & transfer as hyperlink from userform to worksheet

Browse, insert, copy image & transfer as hyperlink from userform to worksheet

 Click here to download this file

vba codes :

Option Explicit

Private Sub CommandButton1_Click()

' to browse and load image in userform

Application.FileDialog(msoFileDialogOpen).AllowMultiSelect = False

Application.FileDialog(msoFileDialogOpen).Show

Dim myPath As String

On Error Resume Next

myPath = Application.FileDialog(msoFileDialogOpen).SelectedItems(1)

Image1.Picture = LoadPicture(myPath)

Image1.PictureSizeMode = fmPictureSizeModeStretch

End Sub

Private Sub CommandButton2_Click()

' Condition that will check image1 is blank or not

If Me.Image1.Picture Is Nothing Then

MsgBox "Please add image first"

Exit Sub

End If

' Define worksheet

Dim sh As Worksheet

Set sh = Sheets("Sheet1")

' to find last row where I want to transfer data

Dim i As Long

i = sh.Range("A" & Rows.Count).End(xlUp).Row + 1

' data will transfer from TextBox1 to Col A

sh.Cells(i, 1) = Me.TextBox1.Value

On Error Resume Next

' the path of the image which is selected to upload in the userform has to be recorded by myPath

Dim myPath As String

myPath = Application.FileDialog(msoFileDialogOpen).SelectedItems(1)

' to copy the image from above location

Dim ipath As String

ipath = "C:\MY FOLDER\" & Me.TextBox1 & ".jpg"

' now copy myPath & paste it on ipath

FileCopy myPath, ipath

' to Hyperlink the image which is loaded on userform

sh.Cells(i, 2).Select

ActiveCell.Hyperlinks.Add anchor:=Selection, Address:=ipath, TextToDisplay:=Me.TextBox1.Value

End Sub