Toggle xlSheetVeryHidden

[Rob]

Excel 2000

I'm using the following code to cycle through a workbook and where a sheet
has been hidden it's property is changed to visible. However, I'm manually
setting the hidden sheets to very hidden in the VBA browser and need to
toggle back to very hidden using code. Unfortunately, the following changes
from very hidden to visible but then only sets hidden if the sheet was
visible i.e. after running this code a user can unhide the sheets by a
choice from Format: Unhide menu.

I have tried all combinations of xlsheetveryhidden etc. but to no avail -
any ideas?

Thanks Rob

For Each sh In ActiveWorkbook.Worksheets
With sh
If .Name <> "MENU" Then _
.Visible = Not (.Visible = True)
End With
Next sh

 

[Frank Kabel]

Hi Rob
try
For Each sh In ActiveWorkbook.Worksheets
With sh
If .Name <> "MENU" Then
if .visible then
.visible = xlsheetveryhidden
else
.visible = True
end if
end if
End With
Next sh

 

[Rob]

Hi Frank,

The sample code sets the sheets to VeryHiodden if they are visible but does
show them if VeryHidden to start with. Tried changing .visible = True to
..visible = xlsheetvisible but not different. Is it something to do with not
looping through sheets?

Thanks, Rob

 

[Frank Kabel]

Hi
try
For Each sh In ActiveWorkbook.Worksheets
With sh
If .Name <> "MENU" Then
if .visible=xlSheetvisible then
.visible = xlsheetveryhidden
else
.visible = xlSheetvisible
end if
end if
End With
Next sh


this will change all sheets which are visible to very hidden and should
make all sheets visible that are hidden

 

[Bob Phillips]

Rob,

Not sure I fully understand what you want but here is a shot

Dim sh As Worksheet

For Each sh In ActiveWorkbook.Worksheets
With sh
If .Name <> "MENU" Then
If .Visible Then
.Visible = xlSheetVeryHidden
ElseIf .Visible = xlHidden Then
.Visible = True
End If
End If
End With
Next sh

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Rob]

Frank,

Thanks, that did it. The line:
If .Visible=xlSheetVisible Then worked whereas

If .Visible Then (without = xlSheetVisible) didn't!

Thanks very much.

Bob, Tried your suggestion but like franks first piece of code the sheets
didn't become visible after being hidden. For some strange reason it seems
that If.Visible Then doesn't work without the = XlSheetVisible.

Many thanks to you both again. Rob

 

[Grasu200]

Hide worksheets selectively

Very interesting board. I have a question about data security in Excel and I wasn't able to find the answer yet. This board is very close though to provide that answer, but not quite.
I am working on a workbook that has 3 worksheets and the first one collects data from the other two. The other two worksheets I would like to hide and password protect - different password for diff. worksheet - so that a certain user can only read and input data on one worksheet and the second user do the same data on the other worksheet.
It is a performance tracking db, where each worksheet is assigned to a different user and I would like to keep them from viewing the other users worksheet. Would this be doable in Excel? I have excel 2000 and 2003.
Thank you!
Grasu200 from Toronto

Frank,

Thanks, that did it. The line:
If .Visible=xlSheetVisible Then worked whereas

If .Visible Then (without = xlSheetVisible) didn't!

Thanks very much.

Bob, Tried your suggestion but like franks first piece of code the sheets
didn't become visible after being hidden. For some strange reason it seems
that If.Visible Then doesn't work without the = XlSheetVisible.

Many thanks to you both again. Rob

"Frank Kabel" wrote in message
news:[email protected]...
> Hi
> try
> For Each sh In ActiveWorkbook.Worksheets
> With sh
> If .Name <> "MENU" Then
> if .visible=xlSheetvisible then
> .visible = xlsheetveryhidden
> else
> .visible = xlSheetvisible
> end if
> end if
> End With
> Next sh
>
>
> this will change all sheets which are visible to very hidden and should
> make all sheets visible that are hidden
> --
> Regards
> Frank Kabel
> Frankfurt, Germany
>
> "Rob" schrieb im Newsbeitrag
> news:[email protected]...
> > Hi Frank,
> >
> > The sample code sets the sheets to VeryHiodden if they are visible
> but does
> > show them if VeryHidden to start with. Tried changing .visible =
> True to
> > .visible = xlsheetvisible but not different. Is it something to do
> with not
> > looping through sheets?
> >
> > Thanks, Rob
> > "Frank Kabel" wrote in message
> > news:%[email protected]...
> > > Hi Rob
> > > try
> > > For Each sh In ActiveWorkbook.Worksheets
> > > With sh
> > > If .Name <> "MENU" Then
> > > if .visible then
> > > .visible = xlsheetveryhidden
> > > else
> > > .visible = True
> > > end if
> > > end if
> > > End With
> > > Next sh
> > >
> > > --
> > > Regards
> > > Frank Kabel
> > > Frankfurt, Germany
> > >
> > > "Rob" schrieb im Newsbeitrag
> > > news:#[email protected]...
> > > > Excel 2000
> > > >
> > > > I'm using the following code to cycle through a workbook and
> where a
> > > sheet
> > > > has been hidden it's property is changed to visible. However,
> I'm
> > > manually
> > > > setting the hidden sheets to very hidden in the VBA browser and
> need
> > > to
> > > > toggle back to very hidden using code. Unfortunately, the
> following
> > > changes
> > > > from very hidden to visible but then only sets hidden if the
> sheet
> > > was
> > > > visible i.e. after running this code a user can unhide the sheets
> by
> > > a
> > > > choice from Format: Unhide menu.
> > > >
> > > > I have tried all combinations of xlsheetveryhidden etc. but to no
> > > avail -
> > > > any ideas?
> > > >
> > > > Thanks Rob
> > > >
> > > > For Each sh In ActiveWorkbook.Worksheets
> > > > With sh
> > > > If .Name <> "MENU" Then _
> > > > .Visible = Not (.Visible = True)
> > > > End With
> > > > Next sh
> > > >
> > > >
> > > >
> > >
> >
> >
>