Hyperlinks on userform

[ge0rge]

In cells B2:Bxx I have part types.
In cells C2:Cxx I have a hyperlink.
On a userform I have a combobox that I use to select a part types and a textbox that fills in based on the row the part type is in. I would like to somehow make it so that the text box allows me to click the hyperlink and I goto the target windows directory.
Here is my code thus far:

Please Login or Register  to view this content.

txtPartFolder will populate based on the Part Type I select in the combobox but I can't click it to go tp the hyperlinks destination. Anyone know how to accomplish this? Thanks.

[Leith Ross]

Hello ge0rge,

There are different ways to approach this. If you post your workbook it will be easier to demonstrate these solutions

[ge0rge]

Here is a sample keeping in mind I could have anywhere from 1-900+ different hyperlinks. I kept the hyperlinks simple as well. Thanks for taking a look at it.

[just_some_guy]

The way I'm reading this, you want a hyperlink that works off of a drop down menu? Is that right?

[ge0rge]

The dropdown is filled with range =Sheet1!A2:A1000. The txtPartFolder is filled based on what row the item is in in Column A. So if I select the part type found in A27 then the hyperlink found in B27 shows in txtPartFolder. I thought I would be able to click on the text shown in this textbox and goto the designated directory, but no can do.

To better understand it I would take a look at the attached example I previously posted. Thanks.

[jaslake]

Hi ge0rge

I'm currently working on a project that has similar requirements. If you add this code to your UserForm, it should follow the hyperlink on a double click:

Please Login or Register  to view this content.

Please note that you MUST FULLY QUALIFY the path.

Hope this helps, John

[ge0rge]

What do you mean I have to fully qualify the path? Could you give me an example?

[jaslake]

Hi ge0rge

If you right click on any file in your computer and select properties, the "Location" is the fully qualified path of that file (I think).

Such that, the file I sent you has a fully qualified path of:

"C:\Documents and Settings\Administrator\Desktop\ge0rge"

Now, in the macro, I defined "MyPath" as being the "ActiveWorkbook" path which simply says that all my files are in the same path as the workbook the macro is running from. If such is your case then you can use the macro as is. If not, then you need to define the path for the macro. Furthermore, I believe the quote marks are required ("C:\Documents and Settings\Administrator\Desktop\ge0rge").

Hope this helps, John

[ge0rge]

So if the hyperlink found in B6 for example points to this folder "C:\Documents and Settings\ge0rge\My Documents\Test Folder"

The the code would be this?

Please Login or Register  to view this content.

[jaslake]

Hi ge0rge

It'll probably be more like this

Please Login or Register  to view this content.

Since i don't have your environment in which to test, I don't know if this is the proper syntax, however, I think this is the "direction" you need to follow.

Let me know how you make out. I'll create an environment that simulates yours if this doesn't work.

John

[ge0rge]

I get an error on this line:

Please Login or Register  to view this content.

Thanks for helping out.

[jaslake]

Hi ge0rge

What error are you receiving? It's probably a syntax error and that needs corrected. I'm going to need to create you're environment.

Please repost your file with the new code and some sample files of the PartFolder. Based on what you've posted, I'll assume the PartFolder's are located in

Please Login or Register  to view this content.

If I can simulate your environment I think we can solve this for you.

John

[ge0rge]

Runtime error. Cannot open the specified error.

[ge0rge]

Inside "TestFolder" is a folder called Carbide Folder, Aluminum Folder, Carbon Fiber Folder, Magnesium Folder, Chromium Folder. Each folder has a hyperlink pointing to it in Column B. I have changed machines as I am now home.

[jaslake]

Hi ge0rge

Your post didn't include

Inside "TestFolder" is a folder called Carbide Folder, Aluminum Folder, Carbon Fiber Folder, Magnesium Folder, Chromium Folder

it only had "Copy of Example.xls ".

Create a ZIP file that includes Copy of Example.xls and TestFolder and I should be able to unzip them into a folder to create what your working with.

John

[ge0rge]

Sorry, I misunderstood what you needed. This should help.

[jaslake]

Hi ge0rge

The attached works on my machine in both Excel 2000 and 2007. My path is in the code and it conforms to the file structure I've established. The Hyperlinks in Sheet1 have been defined according to the file structure I've established.

Please Login or Register  to view this content.

See what you can do about adapting the code to your path and file structure.

Let me know how I can help.

John

[ge0rge]

Ok, I got it working. I notice it opens an excel file. I want it to only open the folder as a hyperlink to a folder would do. Does it have to open another excel file?

[jaslake]

Hi ge0rge

In this thread, Leith Ross states that the FollowHyperlink method always opens a file, the inference being that this method won't simply take you to the folder.

http://www.excelforum.com/excel-prog...oad-files.html

I'm a little confused what you wish to see. Do you want a list of the files in the folder? If this is the case, I'll do some research on this subject and see what I can come up with.

Let me know. John

[jaslake]

Hi ge0rge

I was playing with the code and discovered that you can indeed go to the folder and not open a file. A minor change in the code

Please Login or Register  to view this content.

This will open up a screen with a list of files in the designated folder. I have no idea if this is what you're looking for.

Let me know.

John

[ge0rge]

Thats it. You have no idea how many dead ends I have came across trying to do what you just did. Alot of people I asked said it could not be done. I can't thank you enough. Thanks.

[jaslake]

Hi ge0rge

Glad it worked for you. I learned something new today also. Thanks for the opportunity. If the solution satisfied you, please mark your Thread as solved. If you feel I've been helpful, please click on the Scales.

Hope to "see" you around.

John

[ge0rge]

I just applied this to the actual project and it makes me want to celebrate.

[jaslake]

Hi ge0rge

Yeah, it's fun when stuff works the way you wish it to. Have an extra beer tonight.

John