Hyperlink to Folder Location

[Eurekaonide]

Hi All

I have a form which the user inputs data into and then the data gets fed into the set relevant cells on a worksheet.

One of the boxes they need to fill in is a Hyperlink to a folder location.

What I guess i would liek to happen is that when they click (or in deed Tab) to this cell that the normal dialogue box pops up to select the path to the document location and then show this path. in the corresponding cell (say Cell AJ)
does anyone have any code that i could try for this please, tried searching but cannot find what I'm after.

 

[RSXchin]

It's called GetFolder

I've got the code on another computer. It's a very useful User Defined Function

 

[Eurekaonide]

Anyone know where I can get an example of this GetFolder code - sounds ideal?

 

[RSXchin]

put this in your personal workbook, or on ThisWorksheet
edit: Don't forget to allow the Scripting reference.

Function GetFolder(Optional strPath As String) As String
    Dim fldr As FileDialog
    Dim sItem As String
    Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
    With fldr
        .Title = "Select a Folder"
        .AllowMultiSelect = False
        .InitialFileName = strPath
        If .Show <> -1 Then GoTo NextCode
        sItem = .SelectedItems(1)
    End With
NextCode:
    GetFolder = sItem
    Set fldr = Nothing
End Function

 

[Eurekaonide]

Thanks for this, how do I get this to work with a form ? i.e. I ahve a text box which I want to use to get and display the Hyperlink / Folder path and then to populate the information back into a said named cell??

 

[RSXchin]

Create a doubleclick event for your textbox on your form and call the function

Private Sub TextBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    getfolder()
End Sub

 

[Eurekaonide]

Thank you so much for your help on this.

Upon running my code for the Form I can double click in it and the dialogue box comes up, I can go and choose my path however a slight problem nothing is showing up and the textbox still ends up blank and hence the cell is not filled - what have I done incorrectly?

 

[RSXchin]

sorry, forgot the good bit
(or maybe even ".value")

Private Sub TextBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    textbox1.text = getfolder()
End Sub

 

[Eurekaonide]

Thanks this works great; I can run my form double click in the text box enter the folder location and it displays in the Textbox and then puts it into the corresponding cell.

One Last query!

Is there a way of making this into a Hyperlink now in the Cell? What I would like to be able to do is at a later stage using another form and text box call up the cell which holds the hyperlink to click on it and it open the corresponding folder?
Not sure if this can be achieved however?

 

[RSXchin]

however you're putting the text into whatever cell, change it to

whateveryourcellis.formula ="=Hyperlink(" & textbox1.text & ")"