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:

    
Range("B6").Select
Select Case Range("B6")
Case "Sheet 1"
    ActiveWorkbook.FollowHyperlink Address:=Sheets("Sheet 1")
        Case Else
End Select

(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