Excel Hyper link and Hide sheet

Question

I want to hyperlink another sheet in the same file but want to hide the sheet also.

I created a Index sheet as the first sheet of excel file. Index sheet has a hyperlink to other sheets. If i hide the other sheets and click on the Index sheet hyperlink, it does not work, it work only when sheets are visible.

I do not want people to get confused with many sheets. I want one Index sheet, this sheet will have hyperlink to other sheet on the same excel file. I will hide the other sheets. Reader clicks the hyper link in the index sheet and gets the other respective sheet (but cannot see the sheet)

How we can do this?

Answer 1

Re:  debug warning

Most likely the underscore in one or both of these lines is missing:

Line 5...

"strAddress = _"

-OR-

Line 7...

"ThisWorkbook.Worksheets(Left$(strAddress, _"

Note that a space comes before the underscore.

The " _" is a line continuation character that joins a code line with the line below.

'---

Jim Cone

Answer 2

Re:  hyperlink to hidden sheet

Place the following code in the Index sheet module (not in a regular module).

'---

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)

 'Jim Cone - Portland, Oregon USA

  On Error GoTo FixThings

  Dim strAddress As String

  strAddress = _

  Application.WorksheetFunction.Substitute(Target.SubAddress, "'", vbNullString)

  ThisWorkbook.Worksheets(Left$(strAddress, _

      InStr(1, strAddress, "!") - 1)).Visible = xlSheetVisible

  Application.EnableEvents = False

  Target.Follow

FixThings:

  Application.EnableEvents = True

End Sub

'---

Jim Cone

Portland, Oregon USA

free & commercial excel programs

https://goo.gl/IUQUN2 (Dropbox)

Answer 3

Jim Cone,

Thanking you for the reply. I copied and pasted the code in VBA editor

May be i could have pasted the code wrong. It is not working. It gives debug warning.

Answer 4

Re:  hyperlink to hidden sheet and _

"Can i get the solution if i follow the link below your signature?"

The answer to your question about 'following the link below by signature' is:  No, you can't find a solution there.

The solution I posted is VBA programming code that should be pasted into the code module behind your index sheet.  That code module is acessed by right-clicking your Index sheet tab and choosing "View Code".

The code is pasted into the larger white area on the right side.  It will work automatically, everytime you click a hyperlink to a hidden sheet.

If you still have questions, please be very specific.

'---

Jim Cone

Answer 5

Dear Jim Cone,

Thanking you for the reply.

However i could not fully understand the method and fix. Can i get the solution if i follow the link below your signature?

JBR1968