Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unhiding specific worksheets in VBA (Excel 2003)
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 --- Any help will be greatly appreciated - I have read through the other discussion threads, but I don't see anything specific to my requirements. Regards, |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unhiding specific worksheets in VBA (Excel 2003)
One simple means I'll run by the business owner will be to hide the tabs all
together: ActiveWindow.DisplayWorkbookTabs = False Any other suggestions? "Jinx" wrote: 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 --- Any help will be greatly appreciated - I have read through the other discussion threads, but I don't see anything specific to my requirements. Regards, |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unhiding specific worksheets in VBA (Excel 2003)
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) "Jinx" wrote in message ... One simple means I'll run by the business owner will be to hide the tabs all together: ActiveWindow.DisplayWorkbookTabs = False Any other suggestions? "Jinx" wrote: 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 --- Any help will be greatly appreciated - I have read through the other discussion threads, but I don't see anything specific to my requirements. Regards, |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unhiding specific worksheets in VBA (Excel 2003)
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unhiding specific worksheets in VBA (Excel 2003)
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 "Bob Phillips" wrote: 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) "Jinx" wrote in message ... One simple means I'll run by the business owner will be to hide the tabs all together: ActiveWindow.DisplayWorkbookTabs = False Any other suggestions? "Jinx" wrote: 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 --- Any help will be greatly appreciated - I have read through the other discussion threads, but I don't see anything specific to my requirements. Regards, |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unhiding specific worksheets in VBA (Excel 2003)
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unhiding specific worksheets in VBA (Excel 2003)
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]" wrote: 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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unhiding specific worksheets in VBA (Excel 2003)
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. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unhiding specific worksheets in VBA (Excel 2003)
Is it possible to modify your code to work in an add-in?
Thank you for your help. MSweetG222 "Bob Phillips" wrote: 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) "Jinx" wrote in message ... One simple means I'll run by the business owner will be to hide the tabs all together: ActiveWindow.DisplayWorkbookTabs = False Any other suggestions? "Jinx" wrote: 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 --- Any help will be greatly appreciated - I have read through the other discussion threads, but I don't see anything specific to my requirements. Regards, |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unhiding specific worksheets in VBA (Excel 2003)
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unhiding specific worksheets in VBA (Excel 2003)
I struggle with the "unhide part"
the sub: Workbook_SheetFollowHyperlink does not seems to work on my computer.. the rest seems to be 100% "Bob Phillips" wrote: 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) "Jinx" wrote in message ... One simple means I'll run by the business owner will be to hide the tabs all together: ActiveWindow.DisplayWorkbookTabs = False Any other suggestions? "Jinx" wrote: 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 --- Any help will be greatly appreciated - I have read through the other discussion threads, but I don't see anything specific to my requirements. Regards, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel - unHiding a column affects columns in other worksheets? | Excel Worksheet Functions | |||
(Unsuccessfully!) Unhiding Rows in Excel 2003 | Excel Worksheet Functions | |||
Unhiding worksheets | Excel Discussion (Misc queries) | |||
Unhiding multiple worksheets in Excel 2000 | Excel Worksheet Functions | |||
Unhiding rows in Excel 2003 | Excel Programming |