Form Upload button

[snoozee]

Hi

I have a button on my user form which I need to code so that the user can upload and embed a file into a cell reference, is this possible ?

 

[Yongle]

Try this
Amend default folder path and sheet name

Private Sub CommandButton1_Click()
    Dim myImage As Variant, defaultFolderPath As String
    defaultFolderPath = "[COLOR=#ff0000]C:\Folder\subfolder[/COLOR]"           
    
[I][COLOR=#000080]'hide userform[/COLOR][/I]
    Me.Hide
    
[I][COLOR=#000080]'where is image placed?[/COLOR][/I]
    Sheets("[COLOR=#ff0000]SheetName[/COLOR]").Activate
    Application.InputBox("Select Cell with mouse and click ok", "Insert Image", , , , , , 8).Select

[I][COLOR=#000080]'which image?[/COLOR][/I]
    With Application.FileDialog(msoFileDialogOpen)
        .InitialFileName = defaultFolderPath & "\"
        .Title = "Choose File"
        .AllowMultiSelect = False
        If .Show <> -1 Then Exit Sub
        myImage = .SelectedItems(1)
    End With
    
[I][COLOR=#000080]'insert chosen image in selected cell and show userform[/COLOR][/I]
    ActiveSheet.Pictures.Insert (myImage)
    Me.Show
End Sub

 

[snoozee]

Try this
Amend default folder path and sheet name

Private Sub CommandButton1_Click()
    Dim myImage As Variant, defaultFolderPath As String
    defaultFolderPath = "[COLOR=#ff0000]C:\Folder\subfolder[/COLOR]"           
    
[I][COLOR=#000080]'hide userform[/COLOR][/I]
    Me.Hide
    
[I][COLOR=#000080]'where is image placed?[/COLOR][/I]
    Sheets("[COLOR=#ff0000]SheetName[/COLOR]").Activate
    Application.InputBox("Select Cell with mouse and click ok", "Insert Image", , , , , , 8).Select

[I][COLOR=#000080]'which image?[/COLOR][/I]
    With Application.FileDialog(msoFileDialogOpen)
        .InitialFileName = defaultFolderPath & "\"
        .Title = "Choose File"
        .AllowMultiSelect = False
        If .Show <> -1 Then Exit Sub
        myImage = .SelectedItems(1)
    End With
    
[I][COLOR=#000080]'insert chosen image in selected cell and show userform[/COLOR][/I]
    ActiveSheet.Pictures.Insert (myImage)
    Me.Show
End Sub

Hi thanks for this I’ll give it a try

Just a quick question will this also work with other files e.g. PDF’s

 

[Yongle]

Just a quick question will this also work with other files e.g. PDF’s

Yes - it should insert (but won't display) a PDF

 

[Yongle]

Just spotted a minor glitch
- this avoids the UserForm "disappearing" if File Dialog closed without selecting a file

Private Sub CommandButton1_Click()
    Dim myImage As Variant, defaultFolderPath As String
    defaultFolderPath = "C:\TestArea\pdf"
'hide userform
    Me.Hide
'where is image placed?
    Sheets("SheetName").Activate
    Application.InputBox("Select Cell with mouse and click ok", "Insert Image", , , , , , 8).Select
'which image?
    With Application.FileDialog(msoFileDialogOpen)
        .InitialFileName = defaultFolderPath & "\"
        .Title = "Choose File"
        .AllowMultiSelect = False
        If .Show <> -1 Then[COLOR=#ff0000] GoTo TheEnd:[/COLOR]
        myImage = .SelectedItems(1)
    End With
'insert chosen image in selected cell and show userform
    ActiveSheet.Pictures.Insert (myImage)
[COLOR=#ff0000]TheEnd:[/COLOR]
    Me.Show
End Sub