Hyper Link Help.

[AndyPandy]

I have two work books the 1st work book as a hyer link that is liked to a specfic sheet in another work book but after 24 hours when i come back to use the sheet, its says hyper link not valid. yet it works when i re- do the hyper link, I do not wish to do this everytime. is there away of getting the hyperlink to open the work book to the specific page without opening the work book 1st

2. I have another work sheet that will act as a main page within a work book, within this work book I will have 22 seprate work sheets. The main page will be called home where by you click on a hyper link that opens up one of the sheets, however my problem is this. i wish to hide all the sheets except the home page sheet and have the hyper links still work. I have already tried to do this but when i have hidden the sheets the hyper link does not work.

 

[Brianwarnock]

Disclaimer
I have never done anything like this.

Looking at the second problem it would appear that Hyperlinks targets must be visible, thus the only approach would appear to be via VBA the rough code below appears to work.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
x = Target
Sheets(x).Visible = True
ActiveCell.Offset(0, -1).Select
Selection.Hyperlinks(1).Follow NewWindow:=False

End Sub

The idea is that using the Close event you Hide all sheets except the home sheet
The Cell next to your Hyperlink contains the sheet name
The user Double clicks on this.

Brian

 

[AndyPandy]

This code does not work it shows run time error can anyone fix this x

 

[pr2-eugin]

This code does not work it shows run time error can anyone fix this x

As Brian has already said, he has not done something like that before. So, just saying it is not working is not going to help ! Tell us more about the run time error, error number, error description and line the error occurs would be a good place to start.

 

[Brianwarnock]

Thanks Paul, we do seem to get people who think that we are telepathic!

Andy the code worked for me, however I have decided to clean things up and do a proper job as you are having difficulties. I said that the original code was rough, it was to give you a pointer to see if it was an approach that you could accept.


In your case you might as well use your intruder list to open the worksheet therefore the code would be as below, tested and working on your test zip

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim sht As String
sht = Left(Target, InStr(1, Target, " ") - 1)
Sheets(sht).Visible = True
ActiveCell.Offset(0, -2).Hyperlinks(1).Follow NewWindow:=False
End Sub

Brian

 

[Brianwarnock]

the zip file opened as xlsx file remember to save the new version as xlsm or the code will be lost.

brian

 

[Brianwarnock]

Curious to know if ou got this to work.