Unhiding worksheets if Close is Canceled

[JustBreathe]

I've put in the coding that has been suggested to others as the work-around
for not being able to enable macros with a macro.

It hides all the worksheets (except for 'Sheet1' which contains the message
to that the workbook need to be opened with macros enabled ) before closing.
When the workbook is opened with macos enabled, code will unhide those hidden
sheets and hid 'Sheet1'.

This works, except that I have realized that if 'Cancel' is selected when
closing, it leaves the 'Sheet1' reminder sheet visible and the working tabs
hidden. Is there a way to code the beforeclose section so that the worksheet
will revert back to 'sheet1 hidden' and all other sheets visible in the event
the user decides not to close but to continue working in the file?

TIA
Tanya (self-teach VBA as i go)

 

[Jim Jackson]

If msgbox("Your Message", vbOK,Cancel) = vbCancel then
'Return sheet hiding etc. to desired state.
End if

 

[Vergel Adriano]

Hi Tanya,

Maybe something like this:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If vbNo = MsgBox("Close this workbook?", vbYesNo) Then
Cancel = True
Else
'Put the code here that hides everything except Sheet1
End If
End Sub

 

[JustBreathe]

Thanks Vergel. This is what I've gone with, and it works...with one qwirk.
If the workbook was saved just prior to opting to close it, then the workbook
instantly closes out upon selecting to close without giving the vbYesNoCancel
selection and without running the BeforeClose procedure that hides all
worksheets except Sheet1. Can you eye what I might be missing here?! Here
is my code...

tia!!!
Tanya (teaching myself VBA as I go here)


Private Sub CloseHideSheets()
Dim Wks As Worksheet
Set Wks = Worksheets("Sheet1")
Wks.Visible = xlSheetVisible
For Each Wks In ThisWorkbook.Worksheets
If Wks.Name <> "Sheet1" Then
If Wks.Visible = xlSheetVisible Then Wks.Visible = xlSheetHidden
End If
Next Wks
End Sub
_____________________________________
Private Sub Workbook_BeforeClose(Cancel As Boolean)
'
'This code runs the CloseHideSheets procedure before the workbook is closed.
'This is to make all worksheets hidden, except for Sheet1
'which contains the warning that macros need to be enabled
'at the time the workbook is opened in order to work in it.
'The Workbook_Open() sub will unhide all other sheets upon
'opening if macros are enabled.
'
Dim Msg As String
If Me.Saved = False Then
Msg = "Do you want to save the changes you made to "
Msg = Msg & Me.Name & "?"
Ans = MsgBox(Msg, vbQuestion + vbYesNoCancel)
Select Case Ans
Case vbYes
Call CloseHideSheets
Me.Save
Case vbCancel
Cancel = True
Exit Sub
End Select
End If
Call CloseHideSheets
Me.Saved = True
End Sub

 

[Jim Jackson]

I would simplifiy it this way, including a safety line to keep Sheet1 visible.

Private Sub CloseHideSheets()
Dim x as integer
For x = 2 to sheets.count
Sheets(x).visible = false
Sheets("Sheet1").visible = True
Next x
End Sub

 

[Vergel Adriano]

I don't think you can afford to provide your users an option wether to save
the workbook or not before closing it. If the workbook has just recently
been closed, or if the users responds with a "No" to the messagebox, the last
two lines of your code will execute.

Call CloseHideSheets
Me.Saved = True

However,

Me.Saved = True

does not realy save the workbook. It just allows you to close the workbook
without saving it or getting prompted to save it. Because of that, the work
that CloseHideSheets did were in vain and didn't get saved. When you open the
workbook next, all sheets are visible.

One way to accomplish this might be to trap the event on BeforeSave. Just
before saving, hide all sheets except Sheet1. This ensures the workbook
always gets saved in the way you want it. If the user closes the worksheet
without saving or if Excel crashes, you are sure that the workbook was
previously saved in the state that you want it to be. You won't be needing
the BeforeClose code for this approach. I didn't test this, but I believe it
should work. Give it a try.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim shtActive As Worksheet
Dim sht As Worksheet

'remember which sheet the user is currently working on
Set shtActive = ActiveSheet

'Hide all sheets, and save the workbook
CloseHideSheets
Application.EnableEvents = False
Me.Save
Cancel = True
Application.EnableEvents = True

'Show all sheets and bring the user back
'to where he was before the save operation
For Each sht In Me.Worksheets
sht.Visible = xlSheetVisible
Next sht
shtActive.Activate

End Sub

 

[JustBreathe]

works! Thanks for the pointer!!!

I would simplifiy it this way, including a safety line to keep Sheet1 visible.

Private Sub CloseHideSheets()
Dim x as integer
For x = 2 to sheets.count
Sheets(x).visible = false
Sheets("Sheet1").visible = True
Next x
End Sub

 

[Jim Jackson]

I'm very glad to help.
--
Best wishes,

Jim

works! Thanks for the pointer!!!

 

[JustBreathe]

with a little modification, I did get this to work in the BeforeSave
procedure. thanks for the suggestion!!