Excel VBA - Right Click File Browser to Create Link To Locally Stored .JPGs

[M22RDY]

Hi,

I've been playing about with this for ages, I have row 'U' which I manually link to local JPG files. Basically it has the word "HERE" and linked to a JPG that opens when you click it.

I'm really looking to just RIGHT CLICK a cell in column U, it opens a file browser, when I select the file it places HERE in the cell and links it to the file I've selected.

 

[patel45]

can you provide a link to a sample file with current data and desired result ?

 

[M22RDY]

Hi patel45,

Unfortunately, the sample file contains a lot of sensitive names/addresses as well as financial info.

But the basic premise is I wish to link to a scanned jpg image file just by right clicking on a cell (in row U) and selecting the file, which in turn automatically creates the link.

 

[M22RDY]

[SOLVED]

Here is the solution for anyone stumbling over this thread:

Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
    
    ' Retreive cell column number
    cell_check = ActiveCell.Column

    If cell_check = 21 Then 'checks if Column 21 (U) is selected
        
            Dim file_pick As Long
 
            ' Open the file dialog
            With Application.FileDialog(msoFileDialogOpen)
                .AllowMultiSelect = False
                .Show
 
            ' Display paths of each file selected
            For file_pick = 1 To .SelectedItems.Count
                file_address = .SelectedItems(file_pick)
            Next file_pick
 
            End With
    
    ' Created hyperlink and adds HERE text
    ActiveCell.Hyperlinks.Add Anchor:=Selection, Address:=file_address, TextToDisplay:="HERE"
    
    'Disables Right Click menu
    Cancel = True
    
    End If

End Sub

 

[M22RDY]

There is a strange anomaly happening when I use the above mentioned method.

Initially, when the file is hyper linked, it creates an absolute reference to the file and opens perfectly

Before save, close & re-open
file:///E:\main_sub_directory\sub_directory\file location

Now, the strange thing happens when I save the file and close it, the XLSM is located within the main subdirectory. After it's saves and opened, the link has the main sub directory removed but everything else intact.

After save, close & re-open
file:///E:\sub_directory\file location

Thus it screws up the links and throws 'cannot open selected file' error because it has wiped the main directory (the folder reference) from the hyperlink.

Is there a way to keep the absolute path after a save and re-open?

 

[M22RDY]

Hi,

Just checking if anyone has a solution for this?