Page 1 of 2 12 LastLast
Results 1 to 15 of 18

Thread: Help with Code

  1. #1
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    Help with Code

    Hi Loungers,

    I have this code (courtesy of help from the lounge) that is attached to a button on a work sheet.

    Sub HideSheets()
    Sheets("Governing Documents").Visible = xlSheetVeryHidden
    Sheets("Subordinate Documents").Visible = xlSheetVeryHidden
    Sheets("Policies and Procedures Reg").Visible = xlSheetVeryHidden
    Sheets("Forms and Guidelines Reg").Visible = xlSheetVeryHidden
    End Sub

    This works fine but relies on someone clicking the button to perform this function - which does not always happen and can't be relied on.

    Would like to change this so it triggers automatically when the spread sheet is closed.

    Any thoughts/help would be much appreciated

    Regards

  2. #2
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts
    you could use code like the following:
    Code:
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.ScreenUpdating = False
    Dim StrShts As String, i As Long
    StrShts = "Governing Documents,Subordinate Documents,Policies and Procedures Reg,Forms and Guidelines Reg"
    With ThisWorkbook
      On Error Resume Next
      For i = 1 To Sheets.Count
        .Sheets(i).Visible = xlSheetVisible
      Next
      For i = 0 To UBound(Split(StrShts, ","))
        .Sheets(Split(StrShts, ",")(i)).Visible = xlSheetVeryHidden
      Next
      .Save
    End With
    Application.ScreenUpdating = True
    End Sub
    Note that, before hiding your sheets, all others are unhidden. I've taken that approach as it is not possible to hide all worksheets in a workbook and, if only your sheets were unhidden when the macro is run, whichever happens to be the last visible one would remain visible. With my code, that could only happen if all other worksheets were deleted (or one or more of your sheets was renamed).
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  3. The Following User Says Thank You to macropod For This Useful Post:

    verada (2013-10-08)

  4. #3
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    Hi Paul - Thanks for the quick reply.

    I have one worksheet called "Cover Page" that must always remain open. So on close all tabs other then "Cover Page" would close.

    Not sure how this will change the code. Also when inserting the code does it need to go into a Module or someplace else - not sure on this.

    Regards

  5. #4
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts
    In that case, you could use code like:
    Code:
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.ScreenUpdating = False
    Dim StrSht As String, i As Long
    StrSht = "Cover Page"
    With ThisWorkbook
      On Error Resume Next
      .Sheets(StrSht).Visible = xlSheetVisible
      For i = 1 To Sheets.Count
        With Sheets(i)
          If .Name <> StrSht Then .Visible = xlSheetVeryHidden
        End With
      Next
      .Save
    End With
    Application.ScreenUpdating = True
    End Sub
    The code goes in the workbook's 'ThisWorkbook' module.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  6. The Following User Says Thank You to macropod For This Useful Post:

    verada (2013-10-08)

  7. #5
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    Thanks Paul - that looks to work just as required - very much appreciated

    Regards

  8. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi Verada

    The code posted in post#4 does what you asked for.

    However, the downside of that particular method is that you can never abandon any unwanted or accidental changes made to that workbook by 'closing and not saving'.

    Whenever you close that workbook, it gets saved automatically whether you like it or not.

    I have seen many cases where Users make an almighty mess of a workbook, by pasting over formulas, deleting stuff they shouldn't, changing sheet names etc etc. but at least, when they recognise their misfortune, they can just close without saving, re-open and start again.

    The Workbook_BeforeClose routine posted above will take this free will away.
    We must have free will.
    We have no choice.

    zeddy

  9. The Following User Says Thank You to zeddy For This Useful Post:

    verada (2013-10-08)

  10. #7
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    Hi Zeddy - I understand and agree that although it is achieving what I am after, there is the potential to have some major issues using this routine.

    Can the code in the post be amended to include a save "yes"/"No" option before closing or is there a better method?

    Thanks for your help

    Regards

  11. #8
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,640
    Thanks
    115
    Thanked 652 Times in 594 Posts
    Verada,

    If you just remove the code line .Save in macropod's code, the sheets will be hidden and then you will be prompted for a save, y/n.

    Maud

  12. #9
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    Hi Maud - thanks for the advise - will make the change.

    Regards

  13. #10
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts
    I agree that user's should be given the option to save. What I coded for, though, was a situation in which I assumed the workbook had already been saved and/or only intended changes were in the workbook. If the workbook had already been saved, the user wouldn't want to get another 'Save' prompt.

    The following code revision should achieve an optimal outcome - if the workbook has:
    been opened and no changes made, the workbook will be re-closed without saving.
    already been saved, it will be re-saved (if required) without any prompts after the worksheet visibility is updated.
    been modified and not saved, the user will be prompted as normally occurs.

    Code:
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.ScreenUpdating = False
    Dim StrSht As String, i As Long, bSv As Boolean
    StrSht = "Cover Page"
    With ThisWorkbook
      bSv = .Saved
      On Error Resume Next
      If .Sheets(StrSht).Visible <> xlSheetVisible Then _
        .Sheets(StrSht).Visible = xlSheetVisible
      For i = 1 To Sheets.Count
        With Sheets(i)
          If .Name <> StrSht Then _
            If .Visible <> xlSheetVeryHidden Then _
              .Visible = xlSheetVeryHidden
        End With
      Next
      If bSv = True And .Saved = False Then .Save
    End With
    Application.ScreenUpdating = True
    End Sub
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  14. #11
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi Verada

    Both replies #8 and #10 don't deal with the issue that a workbook can be saved at any time, in particular, when certain sheets are 'visible'.

    A User can save a workbook at any time while the specified sheets are visible, and continue working.
    A User can then subsequently close the workbook, without saving on exit.
    The saved copy thus has protected sheets visible.
    If the workbook is then subsequently opened (by anybody), with macros disabled,
    then these sheets would remain visible.

    So what you want is a workbook that always starts up with specified sheets hidden.
    You could assign a 'hide' routine to the Workbook_Open event.
    But what if the workbook is opened with macros disabled???

    You could assign a simple 'hide routine' to the Workbook_Before_Save event which would hide specified sheets.
    But then, if the User wants to continue working with the workbook, they now have to run the routine to unhide these sheets.
    This can be tedious if the User wants to save their work frequently in a session.

    To allow for this, simply test whether any of the 'protected' sheets are visible
    and use the Workbook_Before_Save routine.
    1. If any protected sheet is visible:
    record where the User currently is
    (i.e. what sheet, what cell and what scroll row and column position),
    then run the 'hide' routine,
    then save the file,
    then return the User to the same place when the Save was executed.

    This is a little more complicated than it sounds.
    I have attached an example file which demonstrates this.

    zeddy
    Attached Files Attached Files

  15. #12
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts
    Quote Originally Posted by zeddy View Post
    Both replies #8 and #10 don't deal with the issue that a workbook can be saved at any time, in particular, when certain sheets are 'visible'.

    A User can save a workbook at any time while the specified sheets are visible, and continue working.
    A User can then subsequently close the workbook, without saving on exit.
    You obviously haven't understood the code - it runs upon closure regardless of whether the workbook has been saved. I do agree, though, that having a similar routine run upon opening is a good thing. In any event, the posted code does what was asked for.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  16. #13
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi Paul

    Your code only runs on Worbook_Close.
    1. The User opens the workbook, uses a password to make certain sheets visible, makes some changes to the file, and uses Ctrl-S to save the workbook. The workbook has now been saved with these sheets visible.
    2. The user makes further changes, but decides they don't want these changes saved i.e. decides to abandon these when closing the workbook. The workbook status at this point is .saved = False i.e. not saved.
    3. As stated in your post: • been modified and not saved, the user will be prompted as normally occurs.
    ..so the user, not wishing to save the last changes, chooses not to save.
    The upshot is, in this scenario, the last saved version has sheets that are visible when they should not be.

    So, as I said in my post:
    Both replies #8 and #10 don't deal with the issue that a workbook can be saved at any time, in particular, when certain sheets are 'visible'

    zeddy

  17. #14
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi Paul

    ..but you are correct, I didn't understand your code.
    Especially this bit:
    If bSv = True And .Saved = False Then .Save

    Since you have:
    With ThisWorkbook
    bSv = .Saved

    ..it seems you think bsv can be True and False at the same time.

    But please enlighten me.

    zeddy

  18. #15
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts
    The line:
    bSv = .Saved
    Stores the current saved state before the sheet-hiding routine runs. So, if the user has saved the workbook bSv = True.
    After the sheet-hiding routine has run, the save state may have changed, but bSv won't have changed. In that case, bSv = True and .Saved = False. There is no implication that "bsv can be True and False at the same time".
    Cheers,

    Paul Edstein
    [MS MVP - Word]

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •