Toggling xlVeryHidden
 

I am trying to write a macro for use in Excel 2003 & 2007 that will toggle
between hiding (using xlVeryHidden) and unhiding a specific worksheet
(Sheet2).

I know how to perform a "normal" hide/unhide using the following code:

Sub HideUnhide()
Sheets("Sheet2").Visible = Not _
Sheets("Sheet2").Visible
End Sub

But I don't know how to incorporate xlVeryHidden. I would greatly
appreciate any help.

Thanks,
Bob

Toggling xlVeryHidden
 

Try

Sub HideUnhide()
With Sheets("Sheet2")
If (.Visible = xlVeryHidden) Or (.Visible = False) Then
.Visible = True
Else
.Visible = xlVeryHidden
End If
End With
End Sub

Hope this helps,

Hutch

"Bob" wrote:

I am trying to write a macro for use in Excel 2003 & 2007 that will toggle
between hiding (using xlVeryHidden) and unhiding a specific worksheet
(Sheet2).

I know how to perform a "normal" hide/unhide using the following code:

Sub HideUnhide()
Sheets("Sheet2").Visible = Not _
Sheets("Sheet2").Visible
End Sub

But I don't know how to incorporate xlVeryHidden. I would greatly
appreciate any help.

Thanks,
Bob

Toggling xlVeryHidden
 

Bob, try the below..

Sub HideUnhide()
Sheets("Sheet2").Visible = IIf(Sheets("Sheet2").Visible = _
True, xlSheetVeryHidden, True)
End Sub

--
Jacob (MVP - Excel)


"Bob" wrote:

I am trying to write a macro for use in Excel 2003 & 2007 that will toggle
between hiding (using xlVeryHidden) and unhiding a specific worksheet
(Sheet2).

I know how to perform a "normal" hide/unhide using the following code:

Sub HideUnhide()
Sheets("Sheet2").Visible = Not _
Sheets("Sheet2").Visible
End Sub

But I don't know how to incorporate xlVeryHidden. I would greatly
appreciate any help.

Thanks,
Bob

Toggling xlVeryHidden
 

Use the Sheet CodeName.

Sub HideUnhide()
'Use Sheet CodeName
'http://www.ozgrid.com/VBA/excel-vba-sheet-names.htm
Sheet2.Visible = Not _
Sheet2.Visible = xlSheetVeryHidden
End Sub


--
Regards
Dave Hawley
www.ozgrid.com
"Jacob Skaria" wrote in message
...
Bob, try the below..

Sub HideUnhide()
Sheets("Sheet2").Visible = IIf(Sheets("Sheet2").Visible = _
True, xlSheetVeryHidden, True)
End Sub

--
Jacob (MVP - Excel)


"Bob" wrote:

I am trying to write a macro for use in Excel 2003 & 2007 that will
toggle
between hiding (using xlVeryHidden) and unhiding a specific worksheet
(Sheet2).

I know how to perform a "normal" hide/unhide using the following code:

Sub HideUnhide()
Sheets("Sheet2").Visible = Not _
Sheets("Sheet2").Visible
End Sub

But I don't know how to incorporate xlVeryHidden. I would greatly
appreciate any help.

Thanks,
Bob

Toggling xlVeryHidden
 

Dave, how does that toggle between xlSheetVERYHidden and xlSheetVisible?

--
Jacob (MVP - Excel)


"ozgrid.com" wrote:

Use the Sheet CodeName.

Sub HideUnhide()
'Use Sheet CodeName
'http://www.ozgrid.com/VBA/excel-vba-sheet-names.htm
Sheet2.Visible = Not _
Sheet2.Visible = xlSheetVeryHidden
End Sub


--
Regards
Dave Hawley
www.ozgrid.com
"Jacob Skaria" wrote in message
...
Bob, try the below..

Sub HideUnhide()
Sheets("Sheet2").Visible = IIf(Sheets("Sheet2").Visible = _
True, xlSheetVeryHidden, True)
End Sub

--
Jacob (MVP - Excel)


"Bob" wrote:

I am trying to write a macro for use in Excel 2003 & 2007 that will
toggle
between hiding (using xlVeryHidden) and unhiding a specific worksheet
(Sheet2).

I know how to perform a "normal" hide/unhide using the following code:

Sub HideUnhide()
Sheets("Sheet2").Visible = Not _
Sheets("Sheet2").Visible
End Sub

But I don't know how to incorporate xlVeryHidden. I would greatly
appreciate any help.

Thanks,
Bob

Toggling xlVeryHidden
 

It doesn't :) Should be;

Sub HideUnhide()
With Sheet2
If .Visible = True Then
.Visible = xlSheetVeryHidden
Else
.Visible = True
End If
End With
End Sub

I was just trying to avoid the IIf, and use the CodeName so users cannot
break the macro by renaming or moving the Sheet. I have read on the MS site
somewhere that's it rather clunky compared to If Else statements. I.e looks
can be deceiving :)



--
Regards
Dave Hawley
www.ozgrid.com

"Jacob Skaria" wrote in message
...
Dave, how does that toggle between xlSheetVERYHidden and xlSheetVisible?

--
Jacob (MVP - Excel)


"ozgrid.com" wrote:

Use the Sheet CodeName.

Sub HideUnhide()
'Use Sheet CodeName
'http://www.ozgrid.com/VBA/excel-vba-sheet-names.htm
Sheet2.Visible = Not _
Sheet2.Visible = xlSheetVeryHidden
End Sub


--
Regards
Dave Hawley
www.ozgrid.com
"Jacob Skaria" wrote in message
...
Bob, try the below..

Sub HideUnhide()
Sheets("Sheet2").Visible = IIf(Sheets("Sheet2").Visible = _
True, xlSheetVeryHidden, True)
End Sub

--
Jacob (MVP - Excel)


"Bob" wrote:

I am trying to write a macro for use in Excel 2003 & 2007 that will
toggle
between hiding (using xlVeryHidden) and unhiding a specific worksheet
(Sheet2).

I know how to perform a "normal" hide/unhide using the following code:

Sub HideUnhide()
Sheets("Sheet2").Visible = Not _
Sheets("Sheet2").Visible
End Sub

But I don't know how to incorporate xlVeryHidden. I would greatly
appreciate any help.

Thanks,
Bob

Toggling xlVeryHidden
 

Tom - That did the trick. Thanks!

Regards,
Bob


"Tom Hutchins" wrote:

Try

Sub HideUnhide()
With Sheets("Sheet2")
If (.Visible = xlVeryHidden) Or (.Visible = False) Then
.Visible = True
Else
.Visible = xlVeryHidden
End If
End With
End Sub

Hope this helps,

Hutch

"Bob" wrote:

I am trying to write a macro for use in Excel 2003 & 2007 that will toggle
between hiding (using xlVeryHidden) and unhiding a specific worksheet
(Sheet2).

I know how to perform a "normal" hide/unhide using the following code:

Sub HideUnhide()
Sheets("Sheet2").Visible = Not _
Sheets("Sheet2").Visible
End Sub

But I don't know how to incorporate xlVeryHidden. I would greatly
appreciate any help.

Thanks,
Bob

Toggling xlVeryHidden
 

Jacob - Thanks for your help! Being a relative novice to VBA I am not
familiar with "IIF" and need to read up on it. I like your efficient code.
Thanks again!

Regards,
Bob


"Jacob Skaria" wrote:

Bob, try the below..

Sub HideUnhide()
Sheets("Sheet2").Visible = IIf(Sheets("Sheet2").Visible = _
True, xlSheetVeryHidden, True)
End Sub

--
Jacob (MVP - Excel)


"Bob" wrote:

I am trying to write a macro for use in Excel 2003 & 2007 that will toggle
between hiding (using xlVeryHidden) and unhiding a specific worksheet
(Sheet2).

I know how to perform a "normal" hide/unhide using the following code:

Sub HideUnhide()
Sheets("Sheet2").Visible = Not _
Sheets("Sheet2").Visible
End Sub

But I don't know how to incorporate xlVeryHidden. I would greatly
appreciate any help.

Thanks,
Bob

Toggling xlVeryHidden
 

Dave - Thanks for your help! I really appreciate it.

Regards,
Bob


"ozgrid.com" wrote:

It doesn't :) Should be;

Sub HideUnhide()
With Sheet2
If .Visible = True Then
.Visible = xlSheetVeryHidden
Else
.Visible = True
End If
End With
End Sub

I was just trying to avoid the IIf, and use the CodeName so users cannot
break the macro by renaming or moving the Sheet. I have read on the MS site
somewhere that's it rather clunky compared to If Else statements. I.e looks
can be deceiving :)



--
Regards
Dave Hawley
www.ozgrid.com

"Jacob Skaria" wrote in message
...
Dave, how does that toggle between xlSheetVERYHidden and xlSheetVisible?

--
Jacob (MVP - Excel)


"ozgrid.com" wrote:

Use the Sheet CodeName.

Sub HideUnhide()
'Use Sheet CodeName
'http://www.ozgrid.com/VBA/excel-vba-sheet-names.htm
Sheet2.Visible = Not _
Sheet2.Visible = xlSheetVeryHidden
End Sub


--
Regards
Dave Hawley
www.ozgrid.com
"Jacob Skaria" wrote in message
...
Bob, try the below..

Sub HideUnhide()
Sheets("Sheet2").Visible = IIf(Sheets("Sheet2").Visible = _
True, xlSheetVeryHidden, True)
End Sub

--
Jacob (MVP - Excel)


"Bob" wrote:

I am trying to write a macro for use in Excel 2003 & 2007 that will
toggle
between hiding (using xlVeryHidden) and unhiding a specific worksheet
(Sheet2).

I know how to perform a "normal" hide/unhide using the following code:

Sub HideUnhide()
Sheets("Sheet2").Visible = Not _
Sheets("Sheet2").Visible
End Sub

But I don't know how to incorporate xlVeryHidden. I would greatly
appreciate any help.

Thanks,
Bob