Use Sheet Label as Clickable Link to

Hi all, I'm trying to create a link to a file using a label and use click event to open it.
However, preferrably I'd like to leave the caption alone and use maybe a mouse over
event to load a string(path to file) the path would be set in another module code

Please Login or Register  to view this content.

I tried to make public dim followlink as string
so any sub using followlink would recogize the path.
However, I have yet to set that up right

Please Login or Register  to view this content.

In my efforts using the last example code, I'm getting an object required error.

Any helps in my endeavor is appreciated.

Thanks,
BDB

o BDB,

Change "Documents" to "Workbooks"...

Please Login or Register  to view this content.

Hello BDB,

You can open any document using the Shell command...

Please Login or Register  to view this content.

Hello BDB,

Did you try the example I posted? It should work. The Shell method only requires the file path and name. The second argument is to control how the window will be displayed.

Hello BDB,

You don't mention which version of Excel you using, but this should work up to 2007. The examples you cited are different from anything I have ever seen for Shell. Where did you find them?

In my efforts, I tried to test for the existence of the file and path by using fso.

Please Login or Register  to view this content.

This did verify the correct path.

Still curious as to how to make an path to an external an object so I could use the following:

Please Login or Register  to view this content.

BDB

Hello BDB,

Can you post the workbook? I would like to run it on my system and see if it behaves the same way.

Hello BDB,

Thanks for taking the time to do all that. Each Excel file has a PDF file it is to open, correct?

Correct. The name is the same. Only difference is the extension.
Label 1 opens excel wb's
Label2 opens pdf's

Is there a way to have the label hold the string for the file path w/o
changing the labels caption ( visible text) ?
Or have the click event fetch the dynamically assigned path from another source?



BDB

Hello BDB,

Okay, I have it working. I love the API. Copy this code into Module1.

Please Login or Register  to view this content.

Label1_Click Event Code

Please Login or Register  to view this content.

Hello BDB,

I only tested the Label1_Click() event code calling the API code. If you want me to fix the workbook so it is completely functional, I will. You need to tell me how this is intended to work with the ComboBox and ListBox and the Labels.

What I sent to you was only an example to show the label_click to open a file setup needed. Though I don't think the function of the listbox is relevant ,the listbox serves as a holder for the group of items the end user selects and uses from a combobox1 & 2
on the sheet. In a reverse action, the user can remove items from the listbox. When the user exercises this action/event, one of the reactions is,the item in combobox2 used to build the selected item in the listbox will be set/select w/code to become the combobox2.value in use. I have this working. I also have two cell where the text serves as a dynamic hyper link to a pdf file and another to an excel file depending on the combobox1items being selected. The only problem is using cells is that I cannot group or rearrange the placement on the sheet like you can with objects.

The only problem using a label that I see is you have to use the caption to hold the file path for a click event. That's very unattractive and leaves the user looking at two labels with a string of text going outside the visible range of the label and not really knowing what purpose they serve.. So I'm wondering how it would be to use a two labels in pairs that sit ontop of each other. The button on top(front) could serve as the link name and the bottom on bottom(back) could serve as the path holder. A Click event on the top button then could retrieve the path from the back button caption. Placing a button on top of another would give the appearance of a single button.

Your more than welcome to construct that for me if you like, however, since my exeperiance with API's is virtually nil, I'm having a hard time following and understanding quite how to get the code you posted to work. So if you could put together an working example or you could ellaborate more on the setup of the code, I probably could get it to work myself.

Either way, I really do appreciate your assistance and mentoring.

Thanks,

BDB

Solved!

For any interested parties,

This turns out to be a pretty simple procedure but finding it wasn't so simple for me.

I put the following into a basic module:

Please Login or Register  to view this content.

Then in my worksheet

Please Login or Register  to view this content.

Make sure to include the file you want to create a link for into the file path.
C:/test/testfile.pdf
C:/test/testfile.xls

Regards,

BDB