Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Won't Compile
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 -- -regards |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Won't Compile
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Won't Compile
On Jul 30, 12:58 pm, GeorgeJ
wrote: 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 -- -regards 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Won't Compile
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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Compile Error. Else without If? | Excel Discussion (Misc queries) | |||
Compile Error on Chr(13) | Excel Discussion (Misc queries) | |||
compile data | Excel Discussion (Misc queries) | |||
help help help compile error | Excel Discussion (Misc queries) | |||
compile errors | Excel Discussion (Misc queries) |