Need double click event solution

[RET79]

Hi there.

I want users to be able to choose the file paths for the links into this file I have, they will list the file paths in a table. However the file paths need to look like this:

K:PandVValuationResilresil_01liabilities[ob_max int+zillmer.xls]

Now, I want to give the user two options:

1. to type in the paths themselves
2. to double click on the cell where they want to put the file path in and then that would trigger a macro to put them through the get filepath thing then the macro would return the name of the file into the cell they just double clicked.

This would be cool, sadly I do not know how to go about this 'double click event' thing.

Many thanks,

RET79

[RET79]

I have this sort of macro right now to return the filepaths in that form, I want this activated when I double click the cell C9 in this case.


Option Explicit

Sub OpenCostOfTerminalBonusForm58()

Dim fileToOpen
Dim fs As Object
Dim file_name As String
Dim folder_path As String
Dim ext_name As String

fileToOpen = Application.GetOpenFilename("Worksheets, *.xls")




Set fs = CreateObject("Scripting.FileSystemObject")
file_name = fs.GetBaseName(fileToOpen)
folder_path = fs.GetParentFolderName(fileToOpen)
ext_name = fs.GetExtensionName(fileToOpen)



Range("C9") = folder_path & "" & "[" & file_name & "." & ext_name & "]"



End Sub

Many thanks,

RET79

[ This Message was edited by: RET79 on 2002-05-17 10:51 ]

[RET79]

Or if double clicking is not possible, if anyone can suggest some other way to get activate this macro. I have put macro buttons next to the cells which require the file paths but this has comfused some users.

RET79

[RET79]

Silly me, just figured it out for myself, need to put this in the workbook double click place:


Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)





Dim fileToOpen
Dim fs As Object
Dim file_name As String
Dim folder_path As String
Dim ext_name As String

fileToOpen = Application.GetOpenFilename("Worksheets, *.xls")




Set fs = CreateObject("Scripting.FileSystemObject")
file_name = fs.GetBaseName(fileToOpen)
folder_path = fs.GetParentFolderName(fileToOpen)
ext_name = fs.GetExtensionName(fileToOpen)


ActiveCell = folder_path & "" & "[" & file_name & "." & ext_name & "]"




End Sub