Hidden Tabs keep appearing once workbook is reopened.

[Brenda]

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.

 

[Gord Dibben]

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

 

[Brenda]

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

 

[Gord Dibben]

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

 

[Brenda]

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