Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unhiding worksheets if Close is Canceled
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) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unhiding worksheets if Close is Canceled
If msgbox("Your Message", vbOK,Cancel) = vbCancel then
'Return sheet hiding etc. to desired state. End if -- Best wishes, Jim "JustBreathe" wrote: 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) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unhiding worksheets if Close is Canceled
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 -- Hope that helps. Vergel Adriano "JustBreathe" wrote: 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) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unhiding worksheets if Close is Canceled
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 "Vergel Adriano" wrote: 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 -- Hope that helps. Vergel Adriano "JustBreathe" wrote: 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) |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unhiding worksheets if Close is Canceled
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 -- Best wishes, Jim "JustBreathe" wrote: 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 "Vergel Adriano" wrote: 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 -- Hope that helps. Vergel Adriano "JustBreathe" wrote: 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) |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unhiding worksheets if Close is Canceled
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 -- Hope that helps. Vergel Adriano "JustBreathe" wrote: 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 "Vergel Adriano" wrote: 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 -- Hope that helps. Vergel Adriano "JustBreathe" wrote: 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) |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unhiding worksheets if Close is Canceled
works! Thanks for the pointer!!!
"Jim Jackson" wrote: 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 -- Best wishes, Jim "JustBreathe" wrote: 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 "Vergel Adriano" wrote: 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 -- Hope that helps. Vergel Adriano "JustBreathe" wrote: 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) |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unhiding worksheets if Close is Canceled
I'm very glad to help.
-- Best wishes, Jim "JustBreathe" wrote: works! Thanks for the pointer!!! "Jim Jackson" wrote: 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 -- Best wishes, Jim "JustBreathe" wrote: 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 "Vergel Adriano" wrote: 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 -- Hope that helps. Vergel Adriano "JustBreathe" wrote: 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) |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unhiding worksheets if Close is Canceled
with a little modification, I did get this to work in the BeforeSave
procedure. thanks for the suggestion!! "Vergel Adriano" wrote: 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 -- Hope that helps. Vergel Adriano "JustBreathe" wrote: 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 "Vergel Adriano" wrote: 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 -- Hope that helps. Vergel Adriano "JustBreathe" wrote: 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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hideing and unhiding worksheets | Excel Programming | |||
Unhiding all worksheets | Excel Programming | |||
Unhiding worksheets | Excel Discussion (Misc queries) | |||
Unhiding all worksheets. | Excel Programming | |||
Unhiding worksheets | Excel Worksheet Functions |