Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Force worksheet to NOT save
Hello
I have a frustrating situation, which several kind ppl have already provided their input (thankyou). However, I was wondering if someone might be able to examine the code below, and tell me why when I exit a workbook running macros it still asks me if I want to save? My goal is for this NOT to happen. ----------------------------------------------------------- Sub workbook_before_close() Worksheets("Error").Visible = xlSheetVisible Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets ws.Visible = (ws.Name < "Error") * -1 - 1 Next ws Application.DisplayAlerts = False Application.SaveWorkspace Application.DisplayAlerts = True End Sub ----------------------------------------------------------- Sub workbook_open() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets ws.Visible = xlSheetVisible Next ws Worksheets("Error").Visible = xlVeryHidden Application.Sheets("Sheet1").Select If Now = Worksheets("Error").Range("AO241") Then Confirm = MsgBox("This workbook has expired. Please contact support for further assistance.", vbInformation + vbOKOnly, "Workbook Expiry") If Confirm = vbOKOnly Then Application.Quit Else: Application.Quit End If End If ThisWorkbook.Saved = True Application.DisplayAlerts = False End Sub ----------------------------------------------------------- Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) MsgBox "Sorry, but the save functions of this workbook have been disabled." Cancel = True End Sub ----------------------------------------------------------- Thanks again for your help. *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Force worksheet to NOT save
I think the culprit line is this one:
Else: Application.Quit Try: Else ActiveWorkbook.Saved = True Application.Quit "R A" wrote: Hello I have a frustrating situation, which several kind ppl have already provided their input (thankyou). However, I was wondering if someone might be able to examine the code below, and tell me why when I exit a workbook running macros it still asks me if I want to save? My goal is for this NOT to happen. ----------------------------------------------------------- Sub workbook_before_close() Worksheets("Error").Visible = xlSheetVisible Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets ws.Visible = (ws.Name < "Error") * -1 - 1 Next ws Application.DisplayAlerts = False Application.SaveWorkspace Application.DisplayAlerts = True End Sub ----------------------------------------------------------- Sub workbook_open() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets ws.Visible = xlSheetVisible Next ws Worksheets("Error").Visible = xlVeryHidden Application.Sheets("Sheet1").Select If Now = Worksheets("Error").Range("AO241") Then Confirm = MsgBox("This workbook has expired. Please contact support for further assistance.", vbInformation + vbOKOnly, "Workbook Expiry") If Confirm = vbOKOnly Then Application.Quit Else: Application.Quit End If End If ThisWorkbook.Saved = True Application.DisplayAlerts = False End Sub ----------------------------------------------------------- Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) MsgBox "Sorry, but the save functions of this workbook have been disabled." Cancel = True End Sub ----------------------------------------------------------- Thanks again for your help. *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Force worksheet to NOT save
You have a typo in the workbook_before_close name:
Private Sub Workbook_BeforeClose(Cancel As Boolean) Worksheets("Error").Visible = xlSheetVisible Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets ws.Visible = (ws.Name < "Error") * -1 - 1 Next ws With Application .EnableEvents = False .DisplayAlerts = False .SaveWorkspace .DisplayAlerts = True .EnableEvents = True End With End Sub And .saveworkspace will invoke workbook_beforesave unless you tell it not to. I've never really used .saveworkspace. Did you really want to use that? R A wrote: Hello I have a frustrating situation, which several kind ppl have already provided their input (thankyou). However, I was wondering if someone might be able to examine the code below, and tell me why when I exit a workbook running macros it still asks me if I want to save? My goal is for this NOT to happen. ----------------------------------------------------------- Sub workbook_before_close() Worksheets("Error").Visible = xlSheetVisible Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets ws.Visible = (ws.Name < "Error") * -1 - 1 Next ws Application.DisplayAlerts = False Application.SaveWorkspace Application.DisplayAlerts = True End Sub ----------------------------------------------------------- Sub workbook_open() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets ws.Visible = xlSheetVisible Next ws Worksheets("Error").Visible = xlVeryHidden Application.Sheets("Sheet1").Select If Now = Worksheets("Error").Range("AO241") Then Confirm = MsgBox("This workbook has expired. Please contact support for further assistance.", vbInformation + vbOKOnly, "Workbook Expiry") If Confirm = vbOKOnly Then Application.Quit Else: Application.Quit End If End If ThisWorkbook.Saved = True Application.DisplayAlerts = False End Sub ----------------------------------------------------------- Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) MsgBox "Sorry, but the save functions of this workbook have been disabled." Cancel = True End Sub ----------------------------------------------------------- Thanks again for your help. *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Force worksheet to NOT save
And one more thing.
Application.quit Seems kind of excessive to me. If I've just changed a different workbook and haven't saved and don't notice that you're closing the application, you may lose more friends/customers/clients than you want! R A wrote: Hello I have a frustrating situation, which several kind ppl have already provided their input (thankyou). However, I was wondering if someone might be able to examine the code below, and tell me why when I exit a workbook running macros it still asks me if I want to save? My goal is for this NOT to happen. ----------------------------------------------------------- Sub workbook_before_close() Worksheets("Error").Visible = xlSheetVisible Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets ws.Visible = (ws.Name < "Error") * -1 - 1 Next ws Application.DisplayAlerts = False Application.SaveWorkspace Application.DisplayAlerts = True End Sub ----------------------------------------------------------- Sub workbook_open() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets ws.Visible = xlSheetVisible Next ws Worksheets("Error").Visible = xlVeryHidden Application.Sheets("Sheet1").Select If Now = Worksheets("Error").Range("AO241") Then Confirm = MsgBox("This workbook has expired. Please contact support for further assistance.", vbInformation + vbOKOnly, "Workbook Expiry") If Confirm = vbOKOnly Then Application.Quit Else: Application.Quit End If End If ThisWorkbook.Saved = True Application.DisplayAlerts = False End Sub ----------------------------------------------------------- Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) MsgBox "Sorry, but the save functions of this workbook have been disabled." Cancel = True End Sub ----------------------------------------------------------- Thanks again for your help. *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do you force excel to save on close | Excel Discussion (Misc queries) | |||
Force a Readonly Workbook to save to a different folder | Excel Discussion (Misc queries) | |||
Force no save on workbook exit | Excel Programming | |||
Force save as | Excel Discussion (Misc queries) | |||
force upate to datbase using save as | Excel Programming |