Won't Compile

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to come up with a VB routine IN XL2007 which hides all but a
specified sheet. In the test routine I have written the specified sheet is
the first sheet, but this is arbitrary. I would be most grateful if someone
could explain the error I get when I try to compile the following:

Sub Testsub()
Dim Sheet, Sheet_ As Worksheet, I As Integer
Set Sheet = ActiveWorkbook.Sheets(1)
MsgBox ActiveWorkbook.Sheets.Count
For I = 1 To ActiveWorkbook.Sheets.Count
Set Sheet_ = ActiveWorkbook.Sheets(I)
If Sheet_ <> Sheet Then Sheet_.Hide
Next
Sheet.Activate
End Sub
 
I got it to work, with some modifications:

Sub Testsub()
Dim Sheet, Sheet_ As Worksheet, I As Integer
Set Sheet = ActiveWorkbook.Sheets(1)
MsgBox ActiveWorkbook.Sheets.Count
For I = 1 To ActiveWorkbook.Sheets.Count
Set Sheet_ = ActiveWorkbook.Sheets(I)
If Sheet_.Name <> Sheet.Name Then Sheet_.Visible = False
Next
Sheet.Activate
End Sub
 
I am trying to come up with a VB routine IN XL2007 which hides all but a
specified sheet. In the test routine I have written the specified sheet is
the first sheet, but this is arbitrary. I would be most grateful if someone
could explain the error I get when I try to compile the following:

Sub Testsub()
Dim Sheet, Sheet_ As Worksheet, I As Integer
Set Sheet = ActiveWorkbook.Sheets(1)
MsgBox ActiveWorkbook.Sheets.Count
For I = 1 To ActiveWorkbook.Sheets.Count
Set Sheet_ = ActiveWorkbook.Sheets(I)
If Sheet_ <> Sheet Then Sheet_.Hide
Next
Sheet.Activate
End Sub
Click to expand...

Here are 2 examples, the first one, will hide all sheets exept the
active sheet, the second one will hide all sheets exept for the one U
pass as a parameter to the sub i.e you call: HideSheetsbyName "Sheet2"

Sub HideSheets()
For Each Sht In ActiveWorkbook.Sheets
If Sht.Name <> ActiveSheet.Name Then
Sht.Visible = False
End If
Next Sht
End Sub

Sub HideSheetsbyName(S As String)
For Each Sht In ActiveWorkbook.Sheets
If Sht.Name <> S Then
Sht.Visible = False
End If
Next Sht
End Sub

Rgd's
 
I'd like to thank you both for your very helpful comments. I realize my
mistake was trying to do a comparison between objects rather than fields of
objects.

In the process of implementing RGD's HideSheets routine I discovered
something strange which may intrigue you. Usually, when you type the name of
a system object such as activeworkbook and then move the cursor to the next
line the VB editor instantly performs the capitalization, e. g. in this case
it turns

activeworkbook

into

ActiveWorkbook.

I notice that in the case of activesheet, just the opposite happens. E. G.
the VB editor turns

ActiveSheet

into

activesheet

But it does interpret activesheet correctly! This is curious, because in
the help file the object is presented as ActiveSheet.

Its not a big deal, but does anyone have any idea what's going on here? (I'm
using XL2007)
 
Back
Top