Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
xlVeryHidden | Excel Programming | |||
Xlveryhidden | Excel Discussion (Misc queries) | |||
opposite of XLVeryhidden!! | Excel Programming | |||
XLVERYHIDDEN | Excel Programming | |||
Doing something wrong - xlVeryHidden | Excel Programming |