PDA

View Full Version : xlVeryHidden in WorkBook_Open



brucemc
07-31-2007, 02:10 PM
Why will:

Sheets("WhatEver").Visible = xlVeryHidden

work in a module or sheet code page, but not in the ThisWorkBook module within WorkBook_Open() or WorkBook_BeforeClose()? Note: I have at least one sheet visible at all times.

I did a cut & paste of the exact same code from the Sheet1 module to the ThisWorkBook module under both of the above (at different tests) and it always fails in an error '1004': "Unable to set the Visible property of the WorkSheet class."

Is there a way to do so?

btw: I have found reference to both xlVeryHidden and xlSheetVeryHidden. Neither work in the above scenario, both rendering an Error 1004. Anyone know if there are circumstances to use one as opposed to the other?

Bob Phillips
07-31-2007, 02:11 PM
Try

ThisWorkbook.Sheets("WhatEver").Visible = xlSheetVeryHidden

rory
07-31-2007, 03:29 PM
Just for information, both xlVeryHidden and xlSheetVeryHidden have the value 2, so it doesn't really matter which you use.
That code works for me as you posted it, but xld's method is better.
Regards,
Rory

Bob Phillips
07-31-2007, 05:12 PM
They might both have a value of 2 but the whole point of application constants is that they are application wide, and there is no need to knwo the underlying value.

As such, it may be unlikely, but it is possible that MS will change the values one day, so xlVeryHidden and xlSheetVeryHidden might not be the same, and if you hav picked the wrong one you will be b*##$5$d. As you are talking about Sheet visiblity, best to use the xlSheet constants.

rory
08-01-2007, 01:19 AM
Fair point, though I agree it's a little unlikely - any late bound automation code would break too.