VB Form : filename in a textbox can it be a hyperlink/link

stevehorton09 · Aug 24th 2004

Is there a way to make a filename a hyperlink or link within a text box?

My current code (below) just copies the workbook name.

Is there a way so that someone could click on the filename in the text box and it would go to that location.

I.e file is in c:\local\a.xls, this location shown the textbox1, by someone clicking on this it goes to this location in say explorer or opens the worksheet??

Private Sub UserForm_Initialize()

Set wb = ActiveWorkbook
TextBox1.Text = wb.FullName

End sub

xlite · Aug 24th 2004

hi stevehorton09,

try:

Code

Workbooks.Open Filename:=TextBox1.Value

i attached an example just in case you need it

cheers
xlite :gift:

stevehorton09 · Aug 24th 2004

Hi xlite,

That would be great but the textbox is in an email type form so i would prefer that they just clicked on a location. I have sent my current spreadsheet, click on open form (if it does not automatically) and then the top left button in the form.

The email form shows a location in the textbox where the form has been saved to i.e r:\local\

I want it so someone can just click on this location and it opens explorer for example

SEE NEXT POST FOR CORRECT SHEET

stevehorton09 · Aug 24th 2004

here is the sheet without a password on it

xlite · Aug 24th 2004

hi stevehorton09,

sorry a bit confused here,

do you mean you need a "Browse" button whereby a user browse for a file
and once selected, the whole path is reflected in a textbox?

cheers
xlite

stevehorton09 · Aug 24th 2004

Sorry bet its me,

The user fills in a form and presses send.

An email is then populated telling the approver where the form the user has just filled is located.

i.e r:\local\

Afetr it has been sent, I would like the location (shown in the email) to be a hyperlink or a link.

I want the approver to either click on location in the email and be taken to the spreadsheet

OR

The approver clicks on the location in the email and they are taken into the folder itself and can llok for the file

hope this helps!

xlite · Aug 24th 2004

hi steve,

from yr file i gather you have developed your own xl email.

if i understand correctly, the user will be saving a fille somewhere
and the approval wants to know where its location is.

i'd use the GetSaveAsFilename method (place the output somewhere in yr email, here i place it in A1)

Code

Sub RetrieveFileName()
Dim sFileName As String


'Show the open dialog and pass the selected _
file name to the String variable "sFileName"
sFileName = Application.GetSaveAsFilename
    'They have cancelled.
    If sFileName = "False" Then Exit Sub


MsgBox sFileName
Range("A1").Value = sFileName
End Sub

Display More

then you need a link for the approver to click.

i found a nice example at John Walkenbach's site:
http://j-walk.com/ss/excel/tips/tip71.htm

i have modified his example slightly as attached

HTH
xlite

stevehorton09 · Aug 25th 2004

thanks very much!

modified it and it worked perfectly

VB Form : filename in a textbox can it be a hyperlink/link

Participate now!

Share