Need to have an index sheet but all other sheets should be hidden

[Madhu]

Hi, Am a novice in excel. What i am trying to do is crate a index sheet with
different logo/pictures for sheets in the workbook. And when i end up
clicking on the logo, it should open the worksheet that its linked to. But i
do not want a workbook with som many sheets visible when opened & it shoudl
have only the index sheet with all references.

I did try & insert a picture & was able to successfully insert hyperlink to
the pic, and when the pic is clicked it takes me to the reference sheet.

Problem: If i hide the reference sheet, the hyperlink does not work. Any
solution for this?

 

[Jacob Skaria]

Hi Madhu

Assuming your index sheet is named as "INDEX" try the below and feedback.

1. If you are using Excel 2007: Right click each picture>Size and
Properties>Alt Text>. (Excel 2003 : Right click>Format
Picture>Web>Alternative text). Enter the sheet name in the alternative text
area for each shape. Please make sure the name is ** exactly same ** as the
chart name (even spaces count !).

2. Set the Security level to low/medium in (Tools|Macro|Security). From
workbook launch VBE using short-key Alt+F11. From menu 'Insert' a module and
paste the below code. Save. Close the VBE window and get back to Workbook.

Sub GoToMySheet()
Application.ScreenUpdating = False
For intTemp = 1 To Sheets.Count
If UCase(Sheets(intTemp).Name) <> "INDEX" Then _
Sheets(intTemp).Visible = False
Next
Set shp = ActiveSheet.Shapes(Application.Caller)
Sheets(Trim(shp.AlternativeText)).Visible = True
Sheets(Trim(shp.AlternativeText)).Activate
Application.ScreenUpdating = True
End Sub

3. Right click the first shape>Assign Macro>From the list Select GoToMySheet
and click OK. Now try mouse click the shape. If that works repeat the same
for each shape.

PS: You can place a hyperlink in each sheet to get back to index sheet. You
just need to edit the alternative text for the picture in case you add new
picture or when you change the sheet name..

If this post helps click Yes