Table of Contents for Excel File

[akafrog18]

I have a workbook with 72 worksheets in it. There is a combination of
worksheets used for last year (2006) and this year (2007). Last year
I when I created this file I used the code below in the ThisWorkbook
section of the VBA code. When the workbook was opened it would hide
the other worksheets and just show the Table of Contents for 2006.
Now that we are in 2007 I would like it to show both the 2007 and 2006
Table of Contents. Can someone help me modify this script so it will
show the 2007 also.

Thanks

Private Sub Workbook_Open()
Dim c As Worksheet
For Each c In Sheets
If c.Name <> "Table Of Contents - 2006" Then
c.Visible = False
Else: c.Visible = True
End If
Next c
End Sub

 

[Paul B]

akafrog18, if your sheet is called Table Of Contents - 2007, try this

Dim c As Worksheet
For Each c In Sheets
If c.Name <> "Table Of Contents - 2006" _
And c.Name <> "Table Of Contents - 2007" Then
c.Visible = False
Else: c.Visible = True
End If
Next c

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

 

[akafrog18]

Thanks Paul. That did work. What does the '_' do that allows it to
work correctly? I had tried using the Or and And several times but
always ended up with a Run Time Error.

Thanks

 

[Paul B]

It does nothing as far as making it work, it lets you "split" the line of
code
so instead of having a long line like below. You can see it in the VBA
window and also helps when posting the code to help avoid "word wrap".

If c.Name <> "Table Of Contents - 2006" And c.Name <> "Table Of Contents -
2007" Then
--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

 

[Chip Pearson]

What does the '_' do

The "_" doesn't do anything in terms of changing the functionality of the
code. It simply allows you to write a single logical line of code across two
or more physical lines of code. For example,

Range( _
"A1" _
) _
..Value _
= _
123

is interpreted by VBA as if it were written on a single line:

Range("A1").Value = 123

Note that there must be a space before the underscore character.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)