Solved

Using a cell to open an existing worksheet in Excel

Posted on 2011-03-14
4
309 Views
Last Modified: 2012-05-11
Is there a way that when a user would click on a cell, that it could open an existing worksheet within the same file?  Instead of having the user find the correct worksheet, a master list could be created and displayed, and then the user would just 'click' the one they wanted?  If so, how could it be done.  If not, thanks for letting me know.
0
Comment
Question by:rivercity
  • 2
4 Comments
 
LVL 13

Accepted Solution

by:
AustinComputerLabs earned 500 total points
Comment Utility
0
 
LVL 59

Expert Comment

by:Chris Bottomley
Comment Utility
If n a workbook you right click on a tab and select view code.  Now insert  a code module and post the following snippet.

When you run the code it will create a new sheet as a table of contents with hyperlinks automatically

Chris
Sub Hyper()
Dim TOC As Worksheet
Dim sh As Object

    On Error Resume Next
    Set TOC = ThisWorkbook.Worksheets("TOC")
    On Error GoTo 0
    If TOC Is Nothing Then
        Set TOC = ThisWorkbook.Worksheets.Add(Before:=ThisWorkbook.Sheets(1))
        TOC.Name = "TOC"
    End If
    TOC.Move Before:=ThisWorkbook.Sheets(1)
    TOC.Cells.Delete
    TOC.Range("A1") = "Worksheet(s)"
    For Each sh In ThisWorkbook.Worksheets
'        TOC.Range("a" & TOC.Rows.Count).End(xlUp).Offset(1, 0) = sh.Name
        TOC.Hyperlinks.Add Anchor:=TOC.Range("a" & TOC.Rows.Count).End(xlUp).Offset(1, 0), _
            Address:="", SubAddress:=VBA.Chr(39) & sh.Name & VBA.Chr(39) & "!A1"
    Next
    TOC.Columns(1).AutoFit

End Sub

Open in new window

0
 

Author Closing Comment

by:rivercity
Comment Utility
sometimes it's just hard to find the right things.

works perfect.
0
 
LVL 13

Expert Comment

by:AustinComputerLabs
Comment Utility
Thank you for your support.
Master Certification obtained with your answer!!
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
My experience with Windows 10 over a one year period and suggestions for smooth operation
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now