Toggling xlVeryHidden
in [Programming]
|
From: ozgrid.com on 13 Apr 2010 02:53 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" <JacobSkaria(a)discussions.microsoft.com> wrote in message news:A093C318-AAC5-4B61-BA0F-4849203965BE(a)microsoft.com... > 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" <JacobSkaria(a)discussions.microsoft.com> wrote in message >> news:7202285F-9280-4AE0-BBEA-24AFF74D3787(a)microsoft.com... >> > 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 >> >> >>
From: Bob on 13 Apr 2010 11:10 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 > >
From: Bob on 13 Apr 2010 11:12 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 > >
From: Bob on 13 Apr 2010 11:14
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" <JacobSkaria(a)discussions.microsoft.com> wrote in message > news:A093C318-AAC5-4B61-BA0F-4849203965BE(a)microsoft.com... > > 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" <JacobSkaria(a)discussions.microsoft.com> wrote in message > >> news:7202285F-9280-4AE0-BBEA-24AFF74D3787(a)microsoft.com... > >> > 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 > >> >> > >> > |