PDA

View Full Version : Solved: VBA Hyperlink from a Cell ?



DigitalDan
05-15-2008, 06:33 PM
I've been using excel for years and have a decent understanding of it, but am just starting to pick up and learn the beginnings of VBA.

I need to take Cell A1 contents which is a number and hyperlink it to another workbook and the column/row specified in A2. I have a formula to come up with the text equivalent of the Column/Row letter&number that it needs to go to at the final destination.

For instance if Cell A1 has "100" in it and A2 has text of "B5" I need to:

1) Provide a hyperlink in a cell to "c:\100.xls". (Each tab in the destination file will be the same as the filename. So file 100.xls will have a single tab worksheet named 100.

2) But in the macro I need to make sure that the current file that the user is in is SAVED and then hyperlink out of it to the new destination file based on A1's contents.
(The user needs to bounce around from spreadsheet to spreadsheet, so they would basically type the destination filename # in A1, and I can make it where they Hit an Icon next to it and trigger the macro)

3) Once there, Position the cursor at the column/row specified in Cell A2, which is really actually referring to that of Column/Row B5. <If too much trouble, ignore this>

I don't want to have additional windows continually opening everytime the user jumps from workbook to workbook. I would like it to close the current window (or however), and just open up the new excel file each time.

I would be so grateful for a solution, and I look forward to learning more of VBA each day I can. Thanks!

Djeetn
05-15-2008, 11:52 PM
Hi,

See attachment for the code of the button.
Hope this could help you out.

Regards,

Dieter

DigitalDan
05-16-2008, 12:09 AM
Djeetn, I really appreciate the help. It is almost there I think as it seemed to work good so far.

One more thing: this spreadsheet is actually for ingredients, so each spreadsheet will have a hyperlink (like the one you sent me) embedded into a corner of it. When the user is on Ingredient 100, and they type in ingredient 125 into the A1 cell on the spreadsheet and hit OPEN, the 100.xls file should close and open the 125.xls file.

Continuing on, when they are in 125.xls file, they again enter the new number they will go to next in the upper corner cell, and it closes and goes to whatever new number they want.

The purpose would be to close the old window automatically when they just hyperlinked to their new number file. All spreadsheets will have the OPEN link on them in the same place so the person can move quickly to each file in a user friendly way.

Is this possible? I am so happy what you gave me thus far though!

Djeetn
05-16-2008, 02:11 AM
Hi DigitalDan,

For the comfort of your users, I replaced cell A1 with a combobox. When opening the workbook "Hyperlink", the combobox is syncronised with the files in the folder c:\Ingredients (so make sure this exists with at least two files in it). That way they're sure the filename really exists. Each time you change the value of the combobox, it closes the first opened drawing, without saving.
Just try it, and you'll see.

Regards,

Dieter

DigitalDan
05-16-2008, 09:11 PM
Thank you so much for that. I have a lot to learn about VBA, but I am on my way. I'll close this thread, but thanks again!