opening sheets from one sheet

lug

New Member
Joined
Apr 2, 2008
Messages
25
Hi,
I have a file with more sheets and want to prepare an index for them in a sheet and meanwhile to open the sheet when click a name in index, how can i do that.
Hope the question is clear.

Thanks in advance
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Hi,

Create a Box on the Index Sheet and assign it to a macro something like this;

Code:
Sub Open_Sheet_2 ()

Sheets("Sheet2").Activate

End Sub

and repeat for each sheet you wish to link.

You could also simply use hyperlinking to achieve this kind of thing.
 
Upvote 0
Insert > Hyperlink.

On the left hand side of the dialogue box, select "Place in this document" rather than the default "Existing file or web page".

Choose the worksheet and range you want to link to.
 
Upvote 0
Thanks too much for all of your prompt reply
for vba codes that Mr. Schwarzmanne instructed, I opened Macro>tools>visual basic editor and pasted the codes over there but i dont know how to save and close or any other action is needed to work.
((I want to all learn all three ways for index))

Thanks again for your help
 
Upvote 0
Hi Lug,

Once you have Inserted the code into a module go back to Excel and Right click on your shape that you created and there is an option for assign Macro, Click on here and you should see your macro listed, select it and click ok and when you click on the shape it should work fine.

Are you ok with the hyperlinking?
 
Upvote 0
Jon von der Heyden gave me this code some time back. It prompts you to choose a location for a sheets index and the creates the index for you, with each sheet name hyperlinked to it's respective sheet.

Code:
Sub IndexWs()

Dim Ws As Worksheet
Dim c As Range
Dim k As Integer

Set c = Application.InputBox(Prompt:="Where do you want to begin your index:", Type:=8)

k = Worksheets.count

For i = 1 To k

    With c
        .Value = Sheets(i).Name
        .Hyperlinks.Add Anchor:=c, Address:="", _
        SubAddress:="'" & Sheets(i).Name & "'!A1"
    End With
        
    If Sheets(i).ProtectContents = True Then
        c.Offset(0, 1) = "Protected"
    Else
        c.Offset(0, 1) = "Unprotected"
    End If
        
    If Sheets(i).Visible = xlSheetVisible Then
        c.Offset(0, 2) = "Visible"
    Else
        If Sheets(i).Visible = xlSheetHidden Then
            c.Offset(0, 2) = "Hidden"
        Else
            c.Offset(0, 2) = "Very Hidden"
        End If
    End If

    Set c = c.Offset(1, 0)

Next i

End Sub
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,941
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top