Display File Path for Inserted Picture

Go1rish

New Member
Joined
Mar 25, 2014
Messages
5
Hello. I've created a macro that I love which allows me to replace an image on multiple sheets with the click of the button. I've been asked if I can add the file path for the image (which is saved on the network) to one of those sheets. The part of the macro that adds the image to the sheet in question is:
Rich (BB code):
Sub SwapPic()
ThisWorkbook.Worksheets("Costing Form")
 
    Dim PicFileName As String
    With Application.FileDialog(msoFileDialogFilePicker)
        .Show
        On Error Resume Next
            PicFileName = .SelectedItems(1)
        On Error GoTo 0
    End With
    If PicFileName = "" Then Exit Sub

    With ActiveSheet.Shapes(Application.Caller)
        .TopLeftCell.Select
        .Delete
    End With
    
    With ActiveSheet.Pictures.Insert(PicFileName)
        .Name = "UserPic"
        .OnAction = "SwapPic"
    End With
 
    With ActiveSheet.Shapes.Range(Array("UserPic")).Select
    If Selection.ShapeRange.Height < Selection.ShapeRange.Width Then
    Selection.ShapeRange.Height = 150
    Else: Selection.ShapeRange.Height = 150
    End If
    End With
 
'Delete Pic on Admin
With Sheets("Admin Set-up Sheet").Select
ThisWorkbook.Worksheets("Admin Set-up Sheet")
    ActiveSheet.Shapes.Range(Array("UserPic")).Select
    Selection.Delete
End With

'Copy to Costing Form Pic
With Sheets("Costing Form").Select
    ActiveSheet.Shapes.Range(Array("UserPic")).Select
    Selection.Copy
End With

'Paste to Admin Sheet and Adjust Height
With Sheets("Admin Set-up Sheet").Select
    Range("A74:D83").Select
    ActiveSheet.Paste
    ActiveSheet.Shapes.Range(Array("UserPic")).Select
    If Selection.ShapeRange.Height < Selection.ShapeRange.Width Then
    Selection.ShapeRange.Width = 245
    ThisWorkbook.Worksheets("Admin Set-up Sheet")
    Else: Selection.ShapeRange.Width = 245
    ThisWorkbook.Worksheets("Admin Set-up Sheet")
    End If
End With

'back to Costing Form
With Sheets("Costing Form").Select
     Range("I4:N4").Select
     ThisWorkbook.Worksheets("Costing Form")
End With
    Range("I4:N4").Select
End Sub

I now want to add that file path for the image into Cell F80 on the sheet "Admin Set-up Sheet". Any suggestions?

Thank you.
 
Last edited by a moderator:

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
66,045
Office Version
  1. 365
Platform
  1. Windows
How about adding this to your code
VBA Code:
Sheets("Admin Set-up Sheet").Range("F80").Value = PicFileName

Also, in future, please use code tags & leave your text in black, as it's a lot easier to read.
 
Solution

Go1rish

New Member
Joined
Mar 25, 2014
Messages
5
How about adding this to your code
VBA Code:
Sheets("Admin Set-up Sheet").Range("F80").Value = PicFileName

Also, in future, please use code tags & leave your text in black, as it's a lot easier to read.
this worked brilliantly! Thank you!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
66,045
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Forum statistics

Threads
1,148,339
Messages
5,746,177
Members
423,998
Latest member
eakenila

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top