VBA - Hide or Unhide bunches of tabs

[Amy Beryl]

Hi All -

Note I did not say "Group" in the title of this post so no-one misconstrues this question with tab function of group and ungroup.

After wrestling the following macro to the ground, I was wondering if I am asking too much to write a second worksheet.activation macro that would do the same thing - Hide or Unhide a different bunch of tabs - but leave the first bunch of tabs either hidden or unhidden as they are when the second Hide or Unhide activation is executed.

Hope that is clear although I'm pretty sure it wasn't.

Any help would be much appreciated.

Amy

Private Sub Worksheet_Activate()
Dim Sheet As Worksheet

Application.ScreenUpdating = False

If Sheet11.Name = "Fcst_Reports Open" Then
For Each Sheet In ThisWorkbook.Sheets
Sheet.Visible = xlSheetVisible
Next Sheet
Sheet11.Name = "Fcst_Reports Close"
Sheets("Fcst_Epax").Select
Else
For Each Sheet In ThisWorkbook.Sheets
'Hide All Sheets Except For These:
If (Sheet.Name <> Sheet1.Name And Sheet.Name <> Sheet2.Name _
And Sheet.Name <> Sheet10.Name And Sheet.Name <> Sheet5.Name _
And Sheet.Name <> Sheet6.Name And Sheet.Name <> Sheet7.Name _
And Sheet.Name <> Sheet23.Name And Sheet.Name <> Sheet9.Name) Then
Sheet.Visible = xlSheetHidden
End If
Next Sheet
Sheet11.Name = "Fcst_Reports Open"
Sheets("AA_Database").Select
End If
Application.ScreenUpdating = True
End Sub

 

[Joe4]

After wrestling the following macro to the ground, I was wondering if I am asking too much to write a second worksheet.activation macro

You cannot have two Worksheet_Activate procedures in the same module.
So instead of having a second one, you would need to expand upon this one.

Or, what many people will do is write multiple custom procedures (normal VBA code that is not an Event Procedure), and simply have this Worksheet_Activate procedure call the other procedures, i.e.

Private Sub Worksheet_Activate()
    Call RunMeFirst
    Call RunMeSecond
End Sub


Private Sub RunMeFirst()
[I]'   Code here[/I]
End Sub


Private Sub RunMeSecond()
[I]'   Code here[/I]
End Sub

 

[Amy Beryl]

Since I have the first Worksheet_Activiate on one tab - could I have another one on another tab?

You cannot have two Worksheet_Activate procedures in the same module.
So instead of having a second one, you would need to expand upon this one.

Or, what many people will do is write multiple custom procedures (normal VBA code that is not an Event Procedure), and simply have this Worksheet_Activate procedure call the other procedures, i.e.

Private Sub Worksheet_Activate()
    Call RunMeFirst
    Call RunMeSecond
End Sub


Private Sub RunMeFirst()
[I]'   Code here[/I]
End Sub


Private Sub RunMeSecond()
[I]'   Code here[/I]
End Sub

 

[Joe4]

Which "Tab" do you currently have it on, exactly?

 

[Amy Beryl]

Sheet 11 known as Fcst_Reports Open and Fcst_Reports Close - depending on the status of sheet 11's Worksheet_Actiivate macro.

 

[Joe4]

Yes, you can have other ones on other sheets, but just be aware that those will only run when that particular sheet is activated.

 

[Amy Beryl]

Since the first macro makes exception for many of the other tabs (keeping them visible) how can I get the second macro to leave the first bunch of tabs visible or not visible as they are when the second macro executes?

Macro one - hides tabs 2 - 6 and leaves open tabs 1,7-12
Macro two - hides tabs 8 - 10 but leaves open 1,7,11,12 and tabs 2-6 are left closed if macro one was run but leaves visible tabs 2-6 of macro one was not run.