Hyperlinks with hidden worksheets

Good afternoon all,

I have a workbook containing several worksheets.
I have included an Introduction Worksheet with Hyperlinks to each sheet.
However, I would like to be able to have the worksheets hidden (or very hidden),
except the Introduction sheet, until the Hyperlink is clicked, then that sheet is activated,
and when exited (by command button), this sheet becomes hidden again, and returns the user to
the Introduction sheet.

I hope I have expalained this well enough. I have some knowledge of VBA,( I have already set up the Command Buttons on the 
worksheets).
Any help on this matter would be most appreciated.

Thank you

Mathew

0
mpb1 (60)
5/1/2005 4:23:43 PM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
570 Views

Similar Articles

[PageSpeed] 25

Mathew,

It is not possible to hyperlink to a hidden sheet.
Suggest you look into adding buttons someplace on the Introduction
worksheet or adding unformatted rectangles directly above the 
sheet names.  Each button/rectangle would run a macro
to unhide the sheet.

Jim Cone
San Francisco, USA


"MPB" <mpb1@btinternet.com> wrote in message 
news:d52vqf$3oh$1@nwrdmz02.dmz.ncs.ea.ibs-infra.bt.com...
> Good afternoon all,
> 
> I have a workbook containing several worksheets.
> I have included an Introduction Worksheet with Hyperlinks to each sheet.
> However, I would like to be able to have the worksheets hidden (or very hidden),
> except the Introduction sheet, until the Hyperlink is clicked, then that sheet is activated,
> and when exited (by command button), this sheet becomes hidden again, and returns the user to
> the Introduction sheet.
> 
> I hope I have expalained this well enough. I have some knowledge of VBA,( I have already set up the Command Buttons on the 
> worksheets).
> Any help on this matter would be most appreciated.
> 
> Thank you
> 
> Mathew
> 
0
jim.coneXXX (771)
5/1/2005 5:35:27 PM
The hyperlink to the hidden worksheet won't work.  How about just putting a
button from the forms toolbar right next to the worksheet you want to see.

Option Explicit
Sub BTNClick()
    Dim BTN As Button
    Dim wks As Worksheet
    
    Set BTN = ActiveSheet.Buttons(Application.Caller)
    
    Set wks = Nothing
    On Error Resume Next
    Set wks = Worksheets(BTN.TopLeftCell.Offset(0, -1).Value)
    On Error GoTo 0
    
    If wks Is Nothing Then
        Beep 'error
    Else
        wks.Visible = xlSheetVisible
        Application.Goto wks.Range("a1"), scroll:=True
    End If
    
End Sub

I put my worksheet names in column A and the button in column B (of the same
row).  Then I assigned all the buttons the same macro.

(I'd also use a button from the forms toolbar for each of the "return to the
index sheet" buttons.  Then I could use just one macro for each of these, too.)

MPB wrote:
> 
> Good afternoon all,
> 
> I have a workbook containing several worksheets.
> I have included an Introduction Worksheet with Hyperlinks to each sheet.
> However, I would like to be able to have the worksheets hidden (or very hidden),
> except the Introduction sheet, until the Hyperlink is clicked, then that sheet is activated,
> and when exited (by command button), this sheet becomes hidden again, and returns the user to
> the Introduction sheet.
> 
> I hope I have expalained this well enough. I have some knowledge of VBA,( I have already set up the Command Buttons on the
> worksheets).
> Any help on this matter would be most appreciated.
> 
> Thank you
> 
> Mathew

-- 

Dave Peterson
0
ec357201 (5290)
5/1/2005 5:53:39 PM
Sorry Guys, but you are both wrong.  I have just managed to get this to work 
using Hyperlinks.  It is a little tricky, but here goes:

The address for you hyperlink needs to be linked to itself.  To acheive this 
(Excel 2000):

Edit the hyperlink
Select "Place in This Document"
Enter the cell address in the Cell Reference (e.g. if you hyperlink is in 
cell B6, type "B6"
Click OK.

In your Main excel sheet, click Alt-F11 to go to the VBA side of things.
In the Excel objects, right click on the main sheet and select "View Code"
In the code sheet, add:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    TmpRef = Chr(ActiveCell.Column + 64) & ActiveCell.Row
    If Not Intersect(Target.Parent, Range(TmpRef)) Is Nothing Then
        Call Unhide_Sheet
    End If
End Sub

Don't ask me how it works, but it does!
Within your module, you can write a macro (mine is called "Unhide_Sheet") to 
unhide the target sheet (see my code below)

Sub Unhide_Sheet()
    Sheet_Ref = ActiveCell.Offset(0, -1).Value
    Sheets(Format(Sheet_Ref, ">")).Visible = True
    Sheets(Format(Sheet_Ref, ">")).Select
    Sheets("Summary").Visible = False
    Range("A6").Calculate
    Range("A1").Select
End Sub

Essentially, whatever my hyperlink is called, Excel will unhide that sheet 
and hide my main page (called "Summary")

Hope this helps, but if not, contact me as below:

Richard Massey
Simple Excel User!
richard.massey@metcash.com



"MPB" wrote:

> Good afternoon all,
> 
> I have a workbook containing several worksheets.
> I have included an Introduction Worksheet with Hyperlinks to each sheet.
> However, I would like to be able to have the worksheets hidden (or very hidden),
> except the Introduction sheet, until the Hyperlink is clicked, then that sheet is activated,
> and when exited (by command button), this sheet becomes hidden again, and returns the user to
> the Introduction sheet.
> 
> I hope I have expalained this well enough. I have some knowledge of VBA,( I have already set up the Command Buttons on the 
> worksheets).
> Any help on this matter would be most appreciated.
> 
> Thank you
> 
> Mathew
> 
> 
0
Rich (271)
5/2/2005 6:51:03 AM
I don't think you need to point the link at the same cell.  You could actually
point at the hidden worksheet.

Option Explicit
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    Dim testRng As Range
    If Intersect(Target.Parent, Me.Range("a:a")) Is Nothing Then
        Exit Sub
    End If
    
    Set testRng = Nothing
    On Error Resume Next
    Set testRng = Application.Range(Target.Parent.Value)
    On Error GoTo 0
    
    If testRng Is Nothing Then
        'do nothing
    Else
        testRng.Parent.Visible = xlSheetVisible
        Application.Goto testRng, scroll:=True
    End If
    
End Sub

The Link pointed at sheet2 (hidden) range A1.
The value in the cell was:  Sheet2!A1

==
But, personally, I wouldn't classify this as work done by the hyperlink.  I'd
say it was the code that did the work.  And if your going to use code, why not
make it a little more straight forward and just use the code directly.

Rich wrote:
> 
> Sorry Guys, but you are both wrong.  I have just managed to get this to work
> using Hyperlinks.  It is a little tricky, but here goes:
> 
> The address for you hyperlink needs to be linked to itself.  To acheive this
> (Excel 2000):
> 
> Edit the hyperlink
> Select "Place in This Document"
> Enter the cell address in the Cell Reference (e.g. if you hyperlink is in
> cell B6, type "B6"
> Click OK.
> 
> In your Main excel sheet, click Alt-F11 to go to the VBA side of things.
> In the Excel objects, right click on the main sheet and select "View Code"
> In the code sheet, add:
> 
> Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
>     TmpRef = Chr(ActiveCell.Column + 64) & ActiveCell.Row
>     If Not Intersect(Target.Parent, Range(TmpRef)) Is Nothing Then
>         Call Unhide_Sheet
>     End If
> End Sub
> 
> Don't ask me how it works, but it does!
> Within your module, you can write a macro (mine is called "Unhide_Sheet") to
> unhide the target sheet (see my code below)
> 
> Sub Unhide_Sheet()
>     Sheet_Ref = ActiveCell.Offset(0, -1).Value
>     Sheets(Format(Sheet_Ref, ">")).Visible = True
>     Sheets(Format(Sheet_Ref, ">")).Select
>     Sheets("Summary").Visible = False
>     Range("A6").Calculate
>     Range("A1").Select
> End Sub
> 
> Essentially, whatever my hyperlink is called, Excel will unhide that sheet
> and hide my main page (called "Summary")
> 
> Hope this helps, but if not, contact me as below:
> 
> Richard Massey
> Simple Excel User!
> richard.massey@metcash.com
> 
> "MPB" wrote:
> 
> > Good afternoon all,
> >
> > I have a workbook containing several worksheets.
> > I have included an Introduction Worksheet with Hyperlinks to each sheet.
> > However, I would like to be able to have the worksheets hidden (or very hidden),
> > except the Introduction sheet, until the Hyperlink is clicked, then that sheet is activated,
> > and when exited (by command button), this sheet becomes hidden again, and returns the user to
> > the Introduction sheet.
> >
> > I hope I have expalained this well enough. I have some knowledge of VBA,( I have already set up the Command Buttons on the
> > worksheets).
> > Any help on this matter would be most appreciated.
> >
> > Thank you
> >
> > Mathew
> >
> >

-- 

Dave Peterson
0
ec357201 (5290)
5/2/2005 10:48:06 AM
Reply:

Similar Artilces:

Link Access table to Excel worksheet
I want to link these so that they can be edited in either programme. Whichever way, the link box is greyed out. You can make a linked table in Access. It's File - Get External Data - Link. The data lives in the Excel workbook, I think (I don't think Access keeps a copy of it). But you can change data in either Excel or Access. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "dalesrunner" <dalesrunner@discussions.microsoft.com> wrote in message news:A165D6C6-D173-45F7-94D5-352E3AA5121E@microsoft.com... ...

Printing Page Numbers for Multiple Worksheets
I am trying to print an entire workbook and would like the page numbe to be specific to each worksheet set. For example, my workbook contains 7 sheets, each of which has a uniqu header that contains the command for Page X of X Pages. Each shee contains multiple pages. If I print the entire workbook, I can get the printout for each shee to begin with a 1 by manually setting the First Page setting to "1" i Page Setup. No problem there. However, my question is...Can I control the command for "X Pages"? Fo example, when I print the entire workbook, I'd like the first ...

Can't move worksheet to new workbook
I have a client who has always imported comma delimited files into Excel and then copied that worksheet into a master .xls workbook using the Move function. With Excel 2007 this is no longer working. It gives an error stating that the destination does not have enough rows and columns. However this move function works if moving a worksheet that started off as an .xls file - not .txt. Could someone please offer some assistance? Thank you. Hi David Could you provide a few more details. You talk about comma delimited files at the beginning, then .txt files at the end. Are these files...

Tabs in my workbook are hidden
When i maximise a spreadsheet my tabs at the bottom are hidden by the start/drawing menus - does anyone know how to fix this? Assuming that in Tools, Options, View you are set to view sheet tabs, you could try dragging the Drawing toolbar (and other toolbars) by the left edge to the top or right of the screen. Gee Wrote: > When i maximise a spreadsheet my tabs at the bottom are hidden by the > start/drawing menus - does anyone know how to fix this? -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.exc...

Hyperlink #8
Does anyone know why I cant insert a hyperlink in a shared worksheet? Hi this is a restriction of shared workbooks. Only hyperlinks inserted BEFORE sharing the workbook work, no chance to insert new ones -- Regards Frank Kabel Frankfurt, Germany Nick wrote: > Does anyone know why I cant insert a hyperlink in a shared > worksheet? ...

HELP!! Charts won't copy across to another worksheet
Help Please. I have a sheet which has all my descriptive stats on it and graphs drawn from this data. There are 100 charts on the sheet. I am trying to copy the whole sheet WITH the CHARTS. Then I shall only have to change some of the formulas to perform other comparisons. My problem is whichever means I sue to copy the sheet the data cells copy. The gaps are there where the charts should be. NO CHARTS. Any advice grateful received. Have you tried making a second copy of the sheet by holding down CTRL and dragging the sheet tab to the right? best wishes -- Bernard V Liengme www.stfx...

help wanted in updating worksheet automatically
Could you please help us with an excel query. We have an excel file with fields in a random order. The values in these fields have to be picked up and arranged one below the other in a separate area. The values to be picked up always have a fixed cell address. Awaiting reply Elle How about just selecting the range, copy it, paste it to its new location. Then select that new range and do Data|Sort? Madhu wrote: > > Could you please help us with an excel query. > > We have an excel file with fields in a random order. > > The values in these fields have to be pic...

How do I open old Excel Worksheets when XP SP2 installed?
After installing XP SP2 (Service Pack 2), I can no longer open previously created Excel Worksheets. Newly created Excel Worksheets work fine. The error message for the olf Worksheets indicates that they are not Excel format. Should I go back to my "pre SP2" XP???? Mike I'm not going to be much help but want to get one thing straight. When you say XP SP2 do you mean Windows XP or Office XP(2002)? There is an SP3 for Office XP(2002) which you should download if you haven't done so. On Office download site. http://office.microsoft.com/en-us/officeupdate/default.aspx?disp...

Using Access Excel Automation, can I delete or rename a worksheet
Hi I am using automation to produce an excel workbook from an access database. Everything is working fine, except that my workbooks always have 3 additional worksheets - sheet 1, sheet 2 and sheet 3. Can I delete these, or rename them? Stapes On 13 Sep, 11:04, Stapes <steve.sta...@gmail.com> wrote: > Hi > > I am using automation to produce an excel workbook from an access > database. Everything is working fine, except that my workbooks always > have 3 additional worksheets - sheet 1, sheet 2 and sheet 3. Can I > delete these, or rename them? > > Stapes Found ...

T/F Can't set Hyperlink base from within macro
Excel 2000. I wanted to set the Hyperlink base from within a macro. Then I read: "BuiltinDocumentProperties Property Returns a DocumentProperties collection that represents all the built-in document properties for the specified workbook. Read-only." Among the listed built-in properties is "Hyperlink Base". Putting that together with "read only", I deduce that although I can set the Hyperlink Base as a user, this cannot be done via a macro. Am I correct or is there a loophole somewhere that would allow me to do this? Chris Beall Hi Chris: Sub chris3() ...

Worksheets in a workbook?
Hi all New around here and wondered if someone could tell me please how many worksheets I can get into an Excel 97 workbook. I could do with storing a lot of files and didn't want to link outside the workbook if I can help it - had too many bad experiences with data movement and corruption when linked to external sheets. Any help greatly appreciated Thanks Bob ..-.-. Worksheets by themselves, pretty much limited by memory only so if empty you can pretty much keep going. If you put stuff in them though, then that varies depending on what type of data you have in there and what sort of ...

printing worksheet on 3x5 card
I would like to know if it is possible to print a worksheet the size of a 3x5 card, so I can use it with my flashcards for school? Printerchallenged, if you printer supports this size paper then, file page setup, set paper size to 3x5, excel gets the paper size from your printer settings -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "printerchallenged" <printerchallenged@discussions.microsoft.com> wrote in message...

Limiting size of worksheet
I want to set up a worksheet and limit to 25 columns and 200 rows. I seem to can't find it. Any suggestions are appreciated. Jo -- JB ----------------------------------------------------------------------- JBL's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1504 View this thread: http://www.excelforum.com/showthread.php?threadid=26668 Joe You cannot do away with the unused cells. A worksheet has 256 columns and 65536 rows. You can hide the rows and columns you don't want to see. Select them and Format>Hide. Alternative.........You may want to...

TO and CC email addresses hidden when forwarding an email [Outlook 2007]
Dear all, I've encountered a problem when forwarding emails in Outlook 2007. Any help much appreciated! When forwarding an email using Outlook 2007, only the names of the recipients of the recipients are given in the forwarded message (from "-----Original Message-----" downwards); the FROM field contains a "mailto" URL with an email address, but the TO and CC fields give no email addresses. (There are no hidden hyperlinks that encode the email addresses.) Is there a setting that changes this? I have looked through all the settings and could not find one! Thanks a l...

Hyperlink issue
I have a couple of anomolies with excel hyperlinks. I have a spreadsheet created by copying text from an HTML file (saved web page). It has some cells with hyperlinks. These hyperlinks work fine. When I create a new hyperlink to similar page on the same site thing work the same as the copied hyperlinks. I create this the same was as I create the malfunctioning one as below. This page yeilds a normal working hyperlink - (see last paragraph) http://www.woodcraft.com/family.aspx?familyid=1972 HOWEVER, when I create a new hyperlink, to a different page on the same site (a link to one of the...

Bottom of spreadsheet hidden in full screen view
If you change Excel to Full Screen View and your taskbar is always on top (as it normally is), the bottom of the spreadsheet is hidden behind the taskbar. Does anyone know how to get around this without hiding the taskbar? I have found that if I drag the taskbar to the bottom and then back up again, the Excel window resizes itself correctly and the bottom of the spreadsheet is visible again. Problem is, I want to do this from VBA! Can anyone help please??? Bringing this post back to the top because it is getting lost in the depths of the forum. I've searched the whole Excel fo...

Hyperlinks #32
Is there a way to force hyperlinks in Excel to open in the Internet Explorer window that is open instead of opening a new window? I need the links to open in the window I'm logged into. Thanks, Laura Laura, I hope you can help me to fix my problem. I cannot open hyperlinks neither in excel nor in word. The following message is displayed: "Unable to open http\:...........\ No program is registered to open this file". I have office 2003. Your help will be greatly appreciated. Armando "Laura" wrote: > Is there a way to force hyperlinks in Excel to open in...

Comparing data in two similar worksheets
I have to compare data in two reports to find the differences. The report has approx 15 columns and 2500 rows, and I need to extract each entry that has changed since the previous report. Each entry has an ID#, that may or may not have been in both reports, or is in both reports but has differing data in some of the columns. I am relatively new to using excel, so any help at all is appreciated. ...

Printing large worksheet onto HP Designjet 800 plotter
Trying to print excel spreadsheets with a very large number of columns (up to 400) onto an HP Designjet 800 Plotter using landscape layout. When using the "Fit to Page" or enlarging it over 180%, the columns on the end get cut off. We are using a customized paper size of 24" x 60" or 24" x 90". It cuts the worksheet off at the 60" length even if it is set to print to 90". We have tried updating drivers, increasing maximum font size to 1000 in the plotter properties. We were able to print the full spreadsheet by changing the fonts, but that ...

Opening worksheets takes ages
WinXP pro & OfficeXP pro Without any changes to both system and hardware Excel needs ages to open files. I can see 'requesting virus scan' in the status bar and from then loading even the smallest worksheets take around 20 seconds. Anyone on reason and workaround? Tnx Edwin Maybe you should take a look at your anti virus program instead.. -- Regards, Peo Sjoblom "Edwin Niemoller" <laagveen@planet.nl> wrote in message news:btmt4n$5ts$1@reader10.wxs.nl... > WinXP pro & OfficeXP pro > > Without any changes to both system and hardware Excel n...

Hyperlink to cell within the same Workbook
I Have a workbook with about 30 sheets, I named cells contained essential information. With so many sheets I decided to make another one and I call it Summary. In this sheet I created a search with 3 droplists that are linked to functions. Know I need make a link to the cell that contains the result of my search, so I can see the items that have contributed for the result. I have tried the funtion "Hyperlink" but it only works with files or url and I can't make it work within the workbook. How can I make a hyperlink within an workbook? Make an hyperlink to a cell in a...

VLOOKUP formula searching multiple worksheets ??
Does anyone know how to search for info from multiple Excel worksheets using the VLOOOKUP formula or any other formula. I have my VLOOKUP formula(column B) and list of values(column A) (sorted in ascending order) on Worksheet 7 and I need to search for these values in Worksheets 1,2,3,4,5& 6 Column F. Is this possible with Excel ? I've tried this by using the formula VLOOKUP(A2,Wrk1:Wrk6!$F:$G,1,FALSE) but I get a #VALUE! error. You will need a construct along these lines: =IF(ISNA(VLOOKUP(A2,Wrk1!$F:$G,2,0)), IF(ISNA(VLOOKUP(A2,Wrk2!$F:$G,2,0)), IF(ISNA(VLOOKUP(A2,Wrk3!$F:$G,2...

Sum Function Without Including Hidden Rows?
I'm using Excel 2000 and have *hidden* several rows that include numeric data. I want to do a sum function on some of the columns intersecting those rows, but when I insert an AutoSum function, it includes data in the visible rows AND the hidden rows. Is there an easy way to perform a sum function that *only* includes the rows that are *-visible?-* (Which rows are hidden/visible are subject to constant change) Thanks for your help! --- Message posted from http://www.ExcelForum.com/ You would need a UDF for that or if there is a pattern (like if every other column/row is hidden) th...

reference a chart from another worksheet witha hyperlink
Dear experts, would like to reference a chartsheet form another worksheet of the same workbook (to put an explanation of what each chart in the workbook does, put the name of the chart aside, on which you can click to go directly on that chart). I have done it for some worksheets with the "hyperlink" function, but this does not work for charts... Could you please help me? Many thanks in advance! Best regards, -- Valeria Valeria - You can't hyperlink to a chart sheet, as you've discovered. You can fake it with a simple macro, as described in the Charting FAQ: http...

Excel worksheets in asp.net
I have a process to send data to Excel in a browser window from both a server control and via dataset code. I would like to create multiple worksheets (tabs in lower part of Excel spreadsheet) in one xls file. Is that possible? Below is a sample of code I am using to dump to Excel. Thanks. Sub ExportDataSetToExcel(ByVal Source As Object, ByVal E As EventArgs) strExcelSQL = "mc_selPeopleSearchHomeExcel" Response.Clear() Response.ContentType = "application/vnd.excel" Response.Charset = "us-ascii" Response...