Creating hyperlinks from Data Validation list to other sheets within xls

Hi all

I was hoping someone might be able to help me with some VBA code.

I have 5 cells which are linked to a Data Validation list, and the excel spreadsheet users are encouraged to select 5 options from this drop-down.

Ideally, once they have selected their option, it should link to the corresponding sheet in the workbook when it is next clicked... Is this possible using VBA code?

Intuitively, I think I may need to do it some other way because I would suppose that if the VBA code can overwrite the cell and change it to a hyperlink, it effectively overwrites the Data Validation in that cell?

I have attached a dummy workbook (which currently has no code) which I hope better explains what I mean.

I was thinking code like the following might be applicable, but I can't currently make it work. There is no error message, it just doesn't change the text in the cell to a hyperlink:

Please Login or Register  to view this content.

(I've copied the code above from the extensive code on my original worksheet and just adapted to illustrate for the Dummy Worksheet example.)

I've read a bit about using combo boxes, but I am not an excel season pro, and not sure if they would be helpful in this context...

Any help greatly appreciated, and many thanks in advance!

Best

Diane

On the Main Sheet worksheet place a worksheet change event

Please Login or Register  to view this content.

This is for the drop down in B6 (do you need the other drop down boxes?) This way you do not need to create hyperlinks - the other way is to use a workbook open event to iterate through all the worksheets and place hyperlinks on the front page. So In ThisWorkbook add

Please Login or Register  to view this content.

So if sheets get added then this hyperlink list is created fresh every time workbook is opened

IF you use VBA to add a hardcode hyperlink to a cell with DV in it, it would not remove the DV. But it isn't really necessary. You could just use the "adjacent" cell to display a "Link" using the HYPERLINK formula for the same functionality and no VBA.


If the DV is in cell A1, then in B1 could be this formula:

=IF(A1="", "", HYPERLINK('" & A1 & "'!A1", "Link"))

You could still use a worksheet change event. I did notice that the drop down for example was Sheet 1, Sheet 2 (spaces in between Sheet and number) whereas excel has Sheet1, Sheet2 etc. That is why I used

Please Login or Register  to view this content.

This removed the space from the name in the drop down list. However depending on what the names are you could use something like

Please Login or Register  to view this content.

Use the select case command - Make the Case is = to the name in the drop down list. Then place the actual sheet name in the Worksheets("Name of sheet") command. If you are going to have 4 drop down lists then just change the Range("B6") to the cell with the drop down list and adjust the select case command. Hope that makes sense.

I still think it's remarkably simple to add a column next to your drop downs to get the HYPERLINK() function slipped in there.

Meanwhile, your drop downs must relate to actual sheets in SOME way. If you've created a drop down list you could put the Sheets they link to in the next column to create a table. I added the sheet names on yuor sheet, then selected both sets of values and named that range of cells MyList.

Then a worksheet_change macro can be employed to create real hardcoded hyperlinks in the DV cells themselves.

Please Login or Register  to view this content.

1) The MyList range can create itself, but never with a "select" approach. Humans do that, VBA doesn't need to. I would keep this out of VBA still, let the worksheet do it with a dynamic formula.

Changing the NAME definition of MyList to a dynamic formula will allow it expand on its own to include any additional values you add to the V:W column:

=OFFSET('Role Profile'!$V$4,,,COUNTA('Role Profile'!$V:$V)+1, 2)

BTW, yes, you can safely hide the column, you could also add a sheet to hold all this reference stuff, then simply hide the sheet.


2) The Data Validation in D28:D32 can now use the dynamic name, too, by changing the DV formula to:

=OFFSET(MyList,,,,1)


3) Since you're now using 2-word sheetnames, a tweak to the hyperlink code:

Please Login or Register  to view this content.

4) Design tip: When resorting to Hyperlink navigation, it's usually expected that a "HOME" link will be present on all the other sheets. I added one to A1 of each sheet. It is also expected to be visible at all times, to I did a FREEZE PANES on row2 of all those sheets, too, so row1 is always visible.