Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hidden Tabs keep appearing once workbook is reopened.
I am working in Excel 2003, I have hidden my tabs which are located at the
bottom of my worksheet. I have buttons located on each tab and once they click on this button it will bring up the tab that is hidden and they can click on another tab and it will hide previous tab and bring up another tab. My question is i want the tabs to remain hidden when i open up the worksheet at the moment i have to rehind them to make my buttons work. I know that i need to keep one tab open. What formula can i use and how would i apply it to the whole workbook but the one tab that will remain open? I have asked this question before but now i had to totally start all over. -- Thank you |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hidden Tabs keep appearing once workbook is reopened.
Either of these placed in Thisworkbook module.
Private Sub Workbook_Open() Dim ws As Worksheet For Each ws In ThisWorkbook.Sheets If ws.Name < "keep visible" Then ws.Visible = xlSheetVeryHidden End If Next ws End Sub Or in beforeclose Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim ws As Worksheet For Each ws In ThisWorkbook.Sheets If ws.Name < "keep visible" Then ws.Visible = xlSheetVeryHidden End If Next ws End Sub Gord Dibben MS Excel MVP On Thu, 6 Nov 2008 13:35:02 -0800, Brenda wrote: I am working in Excel 2003, I have hidden my tabs which are located at the bottom of my worksheet. I have buttons located on each tab and once they click on this button it will bring up the tab that is hidden and they can click on another tab and it will hide previous tab and bring up another tab. My question is i want the tabs to remain hidden when i open up the worksheet at the moment i have to rehind them to make my buttons work. I know that i need to keep one tab open. What formula can i use and how would i apply it to the whole workbook but the one tab that will remain open? I have asked this question before but now i had to totally start all over. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hidden Tabs keep appearing once workbook is reopened.
Gord, thank you for your response. I have just a couple of questions
1. I went with the first formula you supplied and went to the tab that i want to keep open and right clicked to view code and entered the code there. Does it matter where the code it placed? 2. After entering the code do i just hit save and close out and try to reopen this should work right? 3. Last question, do I need to replace any of the wording you gave me with my sheet names? Not sure how the program knows which sheets to keep hidden. Not very good with this formula thing. Much appreciated...Brenda -- Thank you, Brenda "Gord Dibben" wrote: Either of these placed in Thisworkbook module. Private Sub Workbook_Open() Dim ws As Worksheet For Each ws In ThisWorkbook.Sheets If ws.Name < "keep visible" Then ws.Visible = xlSheetVeryHidden End If Next ws End Sub Or in beforeclose Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim ws As Worksheet For Each ws In ThisWorkbook.Sheets If ws.Name < "keep visible" Then ws.Visible = xlSheetVeryHidden End If Next ws End Sub Gord Dibben MS Excel MVP On Thu, 6 Nov 2008 13:35:02 -0800, Brenda wrote: I am working in Excel 2003, I have hidden my tabs which are located at the bottom of my worksheet. I have buttons located on each tab and once they click on this button it will bring up the tab that is hidden and they can click on another tab and it will hide previous tab and bring up another tab. My question is i want the tabs to remain hidden when i open up the worksheet at the moment i have to rehind them to make my buttons work. I know that i need to keep one tab open. What formula can i use and how would i apply it to the whole workbook but the one tab that will remain open? I have asked this question before but now i had to totally start all over. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hidden Tabs keep appearing once workbook is reopened.
Brenda
As I pointed out to you once before,,,,,,,,,,,,,this is VBA code, not "formulas". 1. My response yesterday states................. "Either of these placed in Thisworkbook module." Remove the code from the sheet module. Right-click on the Excel Icon left of "File" on the menu bar and "View Code". That opens Thisworkbook module. Place either of the events in that module. Either will do the same thing.........hide all sheets but the one. 2. Yes, but edit the sheet name first. 3. Replace "keep visible" with your actual sheet name. Gord On Fri, 7 Nov 2008 05:26:02 -0800, Brenda wrote: Gord, thank you for your response. I have just a couple of questions 1. I went with the first formula you supplied and went to the tab that i want to keep open and right clicked to view code and entered the code there. Does it matter where the code it placed? 2. After entering the code do i just hit save and close out and try to reopen this should work right? 3. Last question, do I need to replace any of the wording you gave me with my sheet names? Not sure how the program knows which sheets to keep hidden. Not very good with this formula thing. Much appreciated...Brenda |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hidden Tabs keep appearing once workbook is reopened.
Here is what the code looks like when i "Click on the Excel Icon" to view the
code to enter the formula you supplied to me. Have i done something wrong i entered the code all the way at the bottom. Keep in mind i didn't create this program and have NEVER done Macros before. So the information you are supplying to me is pretty much chinese. The tabs are still showing up when i reopen the worksheet. Private Sub Workbook_Open() 'check for solver If (CheckSolver = False) Then MsgBox "Solver Add-In is not installed, you will not be able to solve least costing on the Auto-Balance sheet. Consult Excel help for information on installing the Solver Add-In." End If 'load the expiry date from cell Expiration!B1 Expiry = getExpirationDate rightNow = Date 'if we have expired, or moved to a different machine If (Expiry = Empty Or rightNow Expiry Or Not IsDataValid(getRegistrationNumber, getExpirationDate)) Then showExpiryDialog End If 'load the workbook loadworkbook End Sub Private Sub loadworkbook() Application.ScreenUpdating = False changeVisibility InstructionsSheet, xlSheetVisible InstructionsSheet.Activate changeVisibility RequirementsSheet, xlSheetVisible changeVisibility EvaluateSheet, xlSheetVisible changeVisibility AutoBalanceSheet, xlSheetVisible changeVisibility HerdDescriptionSheet, xlSheetVisible changeVisibility IngredientsSheet, xlSheetVisible changeVisibility MixSheet, xlSheetVisible changeVisibility ReportsEVSheet, xlSheetVisible changeVisibility ReportsLCSheet, xlSheetVisible changeVisibility ErrorSheet, xlSheetVeryHidden Application.ScreenUpdating = True End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) Application.ScreenUpdating = False resetToStart Geminator.Save Application.ScreenUpdating = True End Sub Private Sub changeVisibility(Sheet As Worksheet, v As Integer) unProtect "AmIn0" Sheet.unProtect "AmIn0" Sheet.Visible = v Sheet.Protect "AmIn0" Protect "AmIn0" End Sub Private Sub resetToStart() changeVisibility ErrorSheet, xlSheetVisible changeVisibility AutoBalanceSheet, xlSheetVeryHidden changeVisibility EvaluateSheet, xlSheetVeryHidden changeVisibility HerdDescriptionSheet, xlSheetVeryHidden changeVisibility IngredientsSheet, xlSheetVeryHidden changeVisibility MixSheet, xlSheetVeryHidden changeVisibility InstructionsSheet, xlSheetVeryHidden changeVisibility ReportsEVSheet, xlSheetVeryHidden changeVisibility ReportsLCSheet, xlSheetVeryHidden changeVisibility RequirementsSheet, xlSheetVeryHidden setUseCount getUseCount + 1 If (getFirstUsed = Empty) Then setFirstUsed Date End If Geminator.Protect "AmIn0" End Sub Private Sub showExpiryDialog() registrationForm.regTextBox.SetFocus registrationForm.regTextBox.SelStart = 0 registrationForm.regTextBox.SelLength = Len(registrationForm.regTextBox.Text) registrationForm.Show End Sub Private Sub WorkbookGeminator_BeforeClose(Cancel As Boolean) Sheets("Requirements").Visible = xlVeryHidden Sheets("MakeAMix").Visible = x1VeryHidden Sheets("HerdDescription").Visible = x1VeryHidden Sheets("Evaluate").Visible = x1VeryHidden Sheets("AutoBalance").Visible = x1VeryHidden Sheets("ReportAutoBalance").Visible = x1VeryHidden Sheets("ReportsEvaluate").Visible = x1VeryHidden ThisWorkbook.Save End Sub -- Thank you, Brenda "Gord Dibben" wrote: Brenda As I pointed out to you once before,,,,,,,,,,,,,this is VBA code, not "formulas". 1. My response yesterday states................. "Either of these placed in Thisworkbook module." Remove the code from the sheet module. Right-click on the Excel Icon left of "File" on the menu bar and "View Code". That opens Thisworkbook module. Place either of the events in that module. Either will do the same thing.........hide all sheets but the one. 2. Yes, but edit the sheet name first. 3. Replace "keep visible" with your actual sheet name. Gord On Fri, 7 Nov 2008 05:26:02 -0800, Brenda wrote: Gord, thank you for your response. I have just a couple of questions 1. I went with the first formula you supplied and went to the tab that i want to keep open and right clicked to view code and entered the code there. Does it matter where the code it placed? 2. After entering the code do i just hit save and close out and try to reopen this should work right? 3. Last question, do I need to replace any of the wording you gave me with my sheet names? Not sure how the program knows which sheets to keep hidden. Not very good with this formula thing. Much appreciated...Brenda |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hidden Tabs in a Workbook | Excel Discussion (Misc queries) | |||
column not hidden/ but not appearing | Excel Discussion (Misc queries) | |||
Tabs in my workbook are hidden | Excel Discussion (Misc queries) | |||
Hidden Cells Keep Appearing | New Users to Excel | |||
Hidden tabs | Excel Discussion (Misc queries) |