Unhiding specific worksheets in VBA (Excel 2003)

[Jinx]

Hi all,
I have a workbook containing ~60 worksheets in which I would like to hide
~50 of them but have individual worksheets open on demand. The worksheets
contain hyperlinks to other worksheets within the same workbook and are
simply referenced as an Excel hyperlink. On clicking a hyperlink I would
like it to unhide the relevant worksheet, on hitting the Excel Web 'back'
button I would like it to rehide the relevant worksheet.
Is this feasible as currently constructed or am I looking at additional
macros to replace the hyperlinking?
Extract of the code doing the worksheet hiding:
---
Private Sub Workbook_Open()
On Error GoTo ErrorHandler
Dim wst As Worksheet

'Ensure the Data sheet is hidden and can't be shown without password
ThisWorkbook.Worksheets("Data").Visible = xlVeryHidden

'Ensure the Exchange Rate sheet is hidden and can't be shown without
password
ThisWorkbook.Worksheets("ExchangeRates").Visible = xlVeryHidden

'protect the Worksheets from alteration
For Each wst In ThisWorkbook.Worksheets
wst.Protect (PWORD)
Next wst

'Unprotect the Exchange Rate Worksheet to obtain Reuters updates
ThisWorkbook.Worksheets("ExchangeRates").Unprotect PWORD

'activate the MainMenu worksheet
ThisWorkbook.Worksheets("MainMenu").Activate

'define the data range names
DefineDataColumns

'sort the specifed data ranges
SortDataRanges "Data_AssociatedDealerGroup", "Data_DealerNames",
"Data_DealerIDs"
SortDataRanges "Data_AssociatedUnitCode", "Data_RMCodes"
SortDataRanges "Data_RMUnitCodes", "Data_RMUnitNames"
SortDataRanges "Data_AssDealerGrp", "Data_AssMarginTempl"

Exit_Workbook_open:
Exit Sub
ErrorHandler:
DisplayError Err.Number, Err.Description
Resume Exit_Workbook_open
End Sub

 

[Jinx]

One simple means I'll run by the business owner will be to hide the tabs all
together:
ActiveWindow.DisplayWorkbookTabs = False
Any other suggestions?

 

[Bob Phillips]

Here is one suggestion that seems to work.

The hyperlink fires and meks the sheet visible before linking. Deactivating
the sheet hides it again. It assumes the hyperlinks are all on a sheet
called Master

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
If Sh.Name <> "Master" Then
Sh.Visible = False
End If
End Sub

Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target
As Hyperlink)
Dim iPos As Long
Application.EnableEvents = False
iPos = InStr(Target.SubAddress, "!")
Worksheets(Left(Target.SubAddress, iPos - 1)).Visible = True
Target.Parent.Hyperlinks(1).Follow
Application.EnableEvents = True
End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Peter Huang [MSFT]]

Hi

That would be an idea.

BTW: Here is some information for your reference.
I think you may try the SheetFollowHyperlink event, which will be fired
when you click a hyperlink.
But based on my research, there is no such an event which will be fired
when you back to the oringal sheet.

Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target
As Hyperlink)
Dim s() As String
s() = Split(Target.Name, "!")
Application.Sheets(s(0)).Visible = True
End Sub


Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Jinx]

Hi Bob,

Thanks for your response. The problem I have is that around half the
worksheets have hyperlinks to the other half of the worksheets - providing
the ability to drill-down for more information containing live Reuters feeds.
So there is no "Master" worksheet containing the hyperlinks.

I've just run through the application for the business owners and they seem
happy with the hidden tabs - I've subsequently had to introduce a basic
navigation interface to allow the users to move between worksheets.

The way this application is evolving I think we may have to look at
web-enabling it. The technical problem is then likely to shift to gaining
access to the live Reuters feeds within the server environment (well outside
the scope of this discussion group).

Thanks again for your response.

Regards,
Jinx

 

[Peter Huang [MSFT]]

Hi

Thanks for your quickly reply!
Based on my research, there is no other event will be fired when we click
the hyperlink than the SheetFollowHyperlink. So it is hard to move back to
the original sheet and rehide the sheet.

For your workaround, it seems to be OK.

Based on my knowledge, there will be a new Excel service in the next Office
version which may meet your request.
For now, you may consider a web page solution in asp.net

If you still have any concern, please feel free to post here.

Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Jinx]

Hi Peter,

Thanks for the response. The meeting I had with the business owners and UA
testers today went really well and they like the fact that all the tabs are
hidden.

I look forward to testing the next version of Office and any new Excel
services it makes available.

We'll wait for the formal feedback from UAT and work on a web-enabled
version behind the scenes.

Once again, thanks for your response.

Regards,
Jinx

 

[Peter Huang [MSFT]]

Hi Jinx,

You are welcomed!

Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[MSweetG222]

Is it possible to modify your code to work in an add-in?

Thank you for your help.

MSweetG222

 

[MSweetG222]

I figured it out. I don't know if it is the correct way, but it works.

In a class module:

Public WithEvents App As Application
Public WithEvents SheetFollowHyperlink As Workbook

Sub App_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink)
Dim iPos As Long
Application.EnableEvents = False
iPos = InStr(Target.SubAddress, "!")
Worksheets(Left(Target.SubAddress, iPos - 1)).Visible = True
Target.Parent.Hyperlinks(1).Follow
Application.EnableEvents = True
End Sub


Let me know if there is a better way.
Thank you,
MSweetG222

 

[Ger]

I struggle with the "unhide part"
the sub: Workbook_SheetFollowHyperlink does not seems to work on my
computer.. the rest seems to be 100%