Hyperlink to cell in sheet from data validation list

[Jasesair]

I have a Data Validation List in cell A8 containing names - Name1, Name2, Name3 etc. I'm wanting to link each name to elsewhere on the sheet. In my case, Name1 links to M3, Name2 links to W3, Name3 links to AG3 and so on - they're linking to cells that are 10 cells to the right of the previous. When the user makes their selection, I also like that to automate without the need to click again on the link.

Is this possible?

 

[Saurabhj]

Hi, Yes this is possible using VBA.

Can you please share the dataset using XL2BB. It will help to check how your data is arranged on the sheet.

 

[Jasesair]

Hi, Yes this is possible using VBA.

Can you please share the dataset using XL2BB. It will help to check how your data is arranged on th

Apologies, I can't do this at work - it's not possible. Not sure if it's needed, but the data for the list comes from another sheet in the Workbook.

 

[Peter_SSs]

Try this Worksheet_Change event code

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address = "$A$8" And Len(Range("A8").Value) > 0 Then Application.Goto Reference:=Cells(3, Application.Match(Range("A8").Value, Sheets("DV List").Range("A:A"), 0) * 10 - 7)
End Sub

21 02 10.xlsm
	A
1	Names
2	Name1
3	Name2
4	Name3
5
DV List

21 02 10.xlsm
	A
1
2
3
4
5
6
7
8	Name2
Hyperlink

Cells with Data Validation
Cell	Allow	Criteria
A8	List	='DV List'!$A$2:$A$4

Here is my sheet after I have selected "Name2" in A8

 

[Jasesair]

So, so good! It caused another data validation list (in sheet "DV List") selection to navigate to the "Hyperlink" sheet A8 (it wasn't previously navigating anywhere). I've just fixed that by forcing it to navigate to a suitable cell.

Thanks again.

 

[Jasesair]

I fear my next query is something for a new thread, but I'm not certain. The cells this navigates to are currently protected, so when the sheet is protected, the worksheet change obviously doesn't work. Is there a solution to this? The sheet needs to be protected and needs to be only 'Select unlocked cells'. These cells could be unprotected, but will need to have the formulas held in each protected, or even hidden ideally.

 

[Peter_SSs]

If you want protection and only select unlocked cells, why are you trying to navigate to a locked cell. It's contradictory.

 

[Jasesair]

Agree, it is contradictory!!! It's navigating to people's names because below the names are cells that only become unlocked when certain other things happen - this stuff happens before the user uses the data validation list. These unlocked cells will vary so there's not any one cell that I could direct them to. To simplify, I want to be able to direct the user to the name which then puts the user in the right location.

 

[Peter_SSs]

Just the same. If you don't want the code to select a locked cell, where do you want the selection?

.. or perhaps you don't want/need to select anything in the M/W/AG/... columns, just scroll so they are visible?

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address = "$A$8" And Len(Range("A8").Value) > 0 Then ActiveWindow.ScrollColumn = Application.Match(Range("A8").Value, Sheets("DV List").Range("A:A"), 0) * 10 - 7
End Sub

 

[Jasesair]

Oh wow, that's exactly what I'm chasing! ScrollColumn - I'll be using this plenty in the future! Many thanks.