Help with Before Close event

[exceluser66]

I have a macro enabled file that unhides worksheets when a password is entered on the first sheet. Works like a charm. The problem is on closing. On close, the user has the option of saving, not saving or continuing (yes/no/cancel). I want to hide all the sheets except the first one when the file is closed regardless of which option is chosen. In other words, if the user chooses to not save and close, I still want the sheets to hide and go back to the first worksheet. I am using the Before Close event. Clicking No does not save the file but also does not hide the sheets. is this possible?

I am not a coder by trade; self taught VBA using this forum! thanks in advance.

 

[Norie]

Can we see your current code?

 

[exceluser66]

Private Sub Workbook_BeforeClose(Cancel As Boolean)<o:p></o:p>Dim answer As String<o:p></o:p>
 answer = MsgBox("Do youwant to save your changes?", vbYesNoCancel)<o:p></o:p>
    If answer = vbCancel Then<o:p></o:p>
    Cancel = True<o:p></o:p>
    ElseIf answer = vbNo Then<o:p></o:p>
    ActiveWorkbook.Unprotect"pw"<o:p></o:p>
    Sheets("Sample1").Visible= False<o:p></o:p>
    Sheets("Sample2").Visible= False<o:p></o:p>
    ActiveWorkbook.Protect "pw"<o:p></o:p>
   Sheets("Start").Select<o:p></o:p>
    Application.DisplayAlerts =False<o:p></o:p>
    ThisWorkbook.Close<o:p></o:p>
    Else<o:p></o:p>
    ThisWorkbook.Save<o:p></o:p>
    ActiveWorkbook.Unprotect"pw"<o:p></o:p>
    Sheets("Sample1").Visible= False<o:p></o:p>
    Sheets("Sample").Visible= False<o:p></o:p>
   ActiveWorkbook.Protect "pw"<o:p></o:p>
   Sheets("Start").Select<o:p></o:p>
    ThisWorkbook.Save<o:p></o:p>
    End If<o:p></o:p>
    End Sub<o:p></o:p>

 

[exceluser66]

Can someone please help? Please!

 

[GTO]

Hi there,

I am logging out at the moment, but just a couple of quick tips. You will want to use BeforeSave or likely AfterSave to enforce that the workbook is only in a Saved state with only the one sheet showing. Google "Excel Force Enable macros' or similar keywords.

Mark

 

[exceluser66]

Making it show only the Start sheet after saving is not a problem. The issue is when the user does not want to save the last change made (but has saved the file at least once after opening). In this case, is there a way to NOT save the changes made by the user but closing the file such that only the Start sheet is shown.

 

[GTO]

...In this case, is there a way to NOT save the changes made by the user but closing the file such that only the Start sheet is shown.

Not by using the BeforeClose event. This is why I suggested using the BeforeSave event as a pseudo AfterSave event (and to maintain backward compatability, as I believe the AfterSave event was added in 2010).

See if this helps: Excelguru Help Site - Force User To Enable Macros

Mark