Hiding multiple worksheets :VBA

[ahugh01]

Hello, I was hoping someone could help me with the best logic for hiding multiple excel tabs using VBA. I don't really mind the layout- just something functional. At any point new tabs can be added and I want to be able to use the TRUE FALSE logic within a worksheet instead of VBA.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Tab Name[/TD]
[TD][/TD]
[TD]Hide?[/TD]
[/TR]
[TR]
[TD]New Model Portfolio[/TD]
[TD]New Model Portfolio.Visible[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]Compliance[/TD]
[TD]Compliance. Visible[/TD]
[TD]FALSE[/TD]
[/TR]
</tbody>[/TABLE]

 

[Roderick_E]

Assuming you are going to use a worksheet to contain all these sheet names and you only need columns A & B, A= sheet name B = true or false.
Right click the worksheet and paste this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim sheetname As String
On Error GoTo 0
'change visible status
If Target.Column = 2 Then
sheetname = Cells(Target.Row, Target.Column - 1)
If Target = True Then
Sheets(sheetname).Visible = True
End If
If Target = False Then
Sheets(sheetname).Visible = False
End If
If UCase(Target) = "VERY" Then
Sheets(sheetname).Visible = xlSheetVeryHidden
End If
End If
End Sub

Allows you to also very hide the sheet.

 

[ahugh01]

what does (ByVal Target As Range) mean?

 

[Rick Rothstein]

what does (ByVal Target As Range) mean?

What Roderick meant to say for installing his code is that you should right-click the tab at the bottom of the worksheet your data is located on, then click "View Code" from the popup menu that appears and copy/paste his code into the code window that opened up. Roderick's code is event code (in this case Change event code) and it executes automatically when you enter True, False or Very into a cell in Column B. The part you asked about is automatically handled by VBA... target is a cell reference to the cell that was changed. Note that to keep the event code active, you will need to save your workbook as a macro-enabled workbook.

 

[ahugh01]

Is there another step after pasting it in? If I follow your instructions and then save the tabs are not hiding when they say FALSE?

 

[Rick Rothstein]

Is there another step after pasting it in? If I follow your instructions and then save the tabs are not hiding when they say FALSE?

The code only works when you "change" the value in the cell... entering Edit Mode (double-click the cell or press F2) and then pressing the Enter Key, pressing the Tab Key or clicking a different cell qualifies as "changing" the cell value.

 

[Roderick_E]

Hmm sorry Rick and ahugh, I didn't want to over explain so I under explained. Thanks Rick for helping out.