| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Don't save file if condition is met
Hello,
I have tried this macro to disable the save function if the cell A1 contains the value 1 when the user wants to save the file I want the file to close without asking if the user any questions, but there seems to be a coding error. Can anyone help please? PS I imagine I should also use the same code for the event "Private Sub Workbook_BeforeClose"? Thank you in advance André Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) If Worksheets("Start").Cells(1, 1) = 1 Then MsgBox "You have already Eliminated Rows! THIS FILE WILL NOT SAVE!" ActiveWorkbook.Close = False End If End Sub In ELIMINATE ROWS MACRO: Worksheets("Master").Cells(1,1).Value = 1 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Don't save file if condition is met
Andre,
Trick it with a small lie. ActiveWorkbook.Saved = True ActiveWorkbook.Close Steve "Andre Croteau" wrote in message ... Hello, I have tried this macro to disable the save function if the cell A1 contains the value 1 when the user wants to save the file I want the file to close without asking if the user any questions, but there seems to be a coding error. Can anyone help please? PS I imagine I should also use the same code for the event "Private Sub Workbook_BeforeClose"? Thank you in advance André Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) If Worksheets("Start").Cells(1, 1) = 1 Then MsgBox "You have already Eliminated Rows! THIS FILE WILL NOT SAVE!" ActiveWorkbook.Close = False End If End Sub In ELIMINATE ROWS MACRO: Worksheets("Master").Cells(1,1).Value = 1 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Don't save file if condition is met
You could use:
me.close savechanges:=false But be careful what you do. If I spent hours working on changes and tried to save the workbook and your code closed it so I lost my work, I'd be ticked. Andre Croteau wrote: Hello, I have tried this macro to disable the save function if the cell A1 contains the value 1 when the user wants to save the file I want the file to close without asking if the user any questions, but there seems to be a coding error. Can anyone help please? PS I imagine I should also use the same code for the event "Private Sub Workbook_BeforeClose"? Thank you in advance André Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) If Worksheets("Start").Cells(1, 1) = 1 Then MsgBox "You have already Eliminated Rows! THIS FILE WILL NOT SAVE!" ActiveWorkbook.Close = False End If End Sub In ELIMINATE ROWS MACRO: Worksheets("Master").Cells(1,1).Value = 1 -- Dave Peterson |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Don't save file if condition is met
I don't think it's the code.
If you build a smaller workbook with just enough data to get through your code, does it work ok? (Just a guess.) Andre Croteau wrote: In both instances of trying the solutions of Steve and Dave, it caused Excel to crash, as it did with my original solution. In Steve's case, it first closed that specific file without saving it, but the macro saved the second file that was opened. Then it crashed! "Excel encountered a problem and must close" In Dave's example, the message came up "Excel encountered a problem and must close" I am at a loss... "Andre Croteau" wrote in message ... Hello, I have tried this macro to disable the save function if the cell A1 contains the value 1 when the user wants to save the file I want the file to close without asking if the user any questions, but there seems to be a coding error. Can anyone help please? PS I imagine I should also use the same code for the event "Private Sub Workbook_BeforeClose"? Thank you in advance André Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) If Worksheets("Start").Cells(1, 1) = 1 Then MsgBox "You have already Eliminated Rows! THIS FILE WILL NOT SAVE!" ActiveWorkbook.Close = False End If End Sub In ELIMINATE ROWS MACRO: Worksheets("Start").Cells(1,1).Value = 1 -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Don't save file if condition is met
In both instances of trying the solutions of Steve and Dave, it caused Excel
to crash, as it did with my original solution. In Steve's case, it first closed that specific file without saving it, but the macro saved the second file that was opened. Then it crashed! "Excel encountered a problem and must close" In Dave's example, the message came up "Excel encountered a problem and must close" I am at a loss... "Andre Croteau" wrote in message ... Hello, I have tried this macro to disable the save function if the cell A1 contains the value 1 when the user wants to save the file I want the file to close without asking if the user any questions, but there seems to be a coding error. Can anyone help please? PS I imagine I should also use the same code for the event "Private Sub Workbook_BeforeClose"? Thank you in advance André Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) If Worksheets("Start").Cells(1, 1) = 1 Then MsgBox "You have already Eliminated Rows! THIS FILE WILL NOT SAVE!" ActiveWorkbook.Close = False End If End Sub In ELIMINATE ROWS MACRO: Worksheets("Start").Cells(1,1).Value = 1 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Don't save file if condition is met
Hi Dave,
I have created the simplest of files, with no data except what I would put in cell A1 of sheet "START" I have built it from scratch in two different computers, and both of them CRASH when I try to save the file with the value 1 in cell A1. Does it have to do with the fact that the commands inside the "BeforeSave" event actually deals with NOT saving the file??? (or does it have to do with Boolean stuff ?) Any other way to accomplish what I want to do? Thanks for your help This is the code that I have put in the "ThisWorkbook" section Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) If Worksheets("Start").Cells(1, 1) = 1 Then MsgBox "You have already Eliminated Rows! DON'T SAVE THIS FILE!" Me.Close savechanges:=False End If End Sub "Dave Peterson" wrote in message ... I don't think it's the code. If you build a smaller workbook with just enough data to get through your code, does it work ok? (Just a guess.) Andre Croteau wrote: In both instances of trying the solutions of Steve and Dave, it caused Excel to crash, as it did with my original solution. In Steve's case, it first closed that specific file without saving it, but the macro saved the second file that was opened. Then it crashed! "Excel encountered a problem and must close" In Dave's example, the message came up "Excel encountered a problem and must close" I am at a loss... "Andre Croteau" wrote in message ... Hello, I have tried this macro to disable the save function if the cell A1 contains the value 1 when the user wants to save the file I want the file to close without asking if the user any questions, but there seems to be a coding error. Can anyone help please? PS I imagine I should also use the same code for the event "Private Sub Workbook_BeforeClose"? Thank you in advance André Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) If Worksheets("Start").Cells(1, 1) = 1 Then MsgBox "You have already Eliminated Rows! THIS FILE WILL NOT SAVE!" ActiveWorkbook.Close = False End If End Sub In ELIMINATE ROWS MACRO: Worksheets("Start").Cells(1,1).Value = 1 -- Dave Peterson |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Don't save file if condition is met
I created a single worksheet workbook in xl2003.
I named that sheet Start. I put 1 in A1. I put your code in the ThisWorkbook module. I tried to save the workbook and your message popped up and the workbook closed. Maybe something else is going on??? I even put some junk in the before_Close event and it didn't crash. Option Explicit Private Sub Workbook_BeforeClose(Cancel As Boolean) MsgBox "hi from close" End Sub Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) If Me.Worksheets("Start").Cells(1, 1).Value = 1 Then Cancel = True MsgBox "You have already Eliminated Rows! THIS FILE WILL NOT SAVE!" Me.Close False End If End Sub I added the cancel = true and it didn't make any difference. Andre Croteau wrote: Hi Dave, I have created the simplest of files, with no data except what I would put in cell A1 of sheet "START" I have built it from scratch in two different computers, and both of them CRASH when I try to save the file with the value 1 in cell A1. Does it have to do with the fact that the commands inside the "BeforeSave" event actually deals with NOT saving the file??? (or does it have to do with Boolean stuff ?) Any other way to accomplish what I want to do? Thanks for your help This is the code that I have put in the "ThisWorkbook" section Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) If Worksheets("Start").Cells(1, 1) = 1 Then MsgBox "You have already Eliminated Rows! DON'T SAVE THIS FILE!" Me.Close savechanges:=False End If End Sub "Dave Peterson" wrote in message ... I don't think it's the code. If you build a smaller workbook with just enough data to get through your code, does it work ok? (Just a guess.) Andre Croteau wrote: In both instances of trying the solutions of Steve and Dave, it caused Excel to crash, as it did with my original solution. In Steve's case, it first closed that specific file without saving it, but the macro saved the second file that was opened. Then it crashed! "Excel encountered a problem and must close" In Dave's example, the message came up "Excel encountered a problem and must close" I am at a loss... "Andre Croteau" wrote in message ... Hello, I have tried this macro to disable the save function if the cell A1 contains the value 1 when the user wants to save the file I want the file to close without asking if the user any questions, but there seems to be a coding error. Can anyone help please? PS I imagine I should also use the same code for the event "Private Sub Workbook_BeforeClose"? Thank you in advance André Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) If Worksheets("Start").Cells(1, 1) = 1 Then MsgBox "You have already Eliminated Rows! THIS FILE WILL NOT SAVE!" ActiveWorkbook.Close = False End If End Sub In ELIMINATE ROWS MACRO: Worksheets("Start").Cells(1,1).Value = 1 -- Dave Peterson -- Dave Peterson |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Don't save file if condition is met
Hi Dave,
I tried the code you listed below, and it worked! There were a few differences with my code, notably "Me.Worksheets" and "Cancel = True" Anyway, all seems OK now....Thank you so much for your help and persistance. kind regards André "Dave Peterson" wrote in message ... I created a single worksheet workbook in xl2003. I named that sheet Start. I put 1 in A1. I put your code in the ThisWorkbook module. I tried to save the workbook and your message popped up and the workbook closed. Maybe something else is going on??? I even put some junk in the before_Close event and it didn't crash. Option Explicit Private Sub Workbook_BeforeClose(Cancel As Boolean) MsgBox "hi from close" End Sub Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) If Me.Worksheets("Start").Cells(1, 1).Value = 1 Then Cancel = True MsgBox "You have already Eliminated Rows! THIS FILE WILL NOT SAVE!" Me.Close False End If End Sub I added the cancel = true and it didn't make any difference. Andre Croteau wrote: Hi Dave, I have created the simplest of files, with no data except what I would put in cell A1 of sheet "START" I have built it from scratch in two different computers, and both of them CRASH when I try to save the file with the value 1 in cell A1. Does it have to do with the fact that the commands inside the "BeforeSave" event actually deals with NOT saving the file??? (or does it have to do with Boolean stuff ?) Any other way to accomplish what I want to do? Thanks for your help This is the code that I have put in the "ThisWorkbook" section Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) If Worksheets("Start").Cells(1, 1) = 1 Then MsgBox "You have already Eliminated Rows! DON'T SAVE THIS FILE!" Me.Close savechanges:=False End If End Sub "Dave Peterson" wrote in message ... I don't think it's the code. If you build a smaller workbook with just enough data to get through your code, does it work ok? (Just a guess.) Andre Croteau wrote: In both instances of trying the solutions of Steve and Dave, it caused Excel to crash, as it did with my original solution. In Steve's case, it first closed that specific file without saving it, but the macro saved the second file that was opened. Then it crashed! "Excel encountered a problem and must close" In Dave's example, the message came up "Excel encountered a problem and must close" I am at a loss... "Andre Croteau" wrote in message ... Hello, I have tried this macro to disable the save function if the cell A1 contains the value 1 when the user wants to save the file I want the file to close without asking if the user any questions, but there seems to be a coding error. Can anyone help please? PS I imagine I should also use the same code for the event "Private Sub Workbook_BeforeClose"? Thank you in advance André Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) If Worksheets("Start").Cells(1, 1) = 1 Then MsgBox "You have already Eliminated Rows! THIS FILE WILL NOT SAVE!" ActiveWorkbook.Close = False End If End Sub In ELIMINATE ROWS MACRO: Worksheets("Start").Cells(1,1).Value = 1 -- Dave Peterson -- Dave Peterson |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Don't save file if condition is met
I'm not sure if it was the changes or just coincidence. I'd keep an eye on that
workbook. But glad it worked ok for you. Andre Croteau wrote: Hi Dave, I tried the code you listed below, and it worked! There were a few differences with my code, notably "Me.Worksheets" and "Cancel = True" Anyway, all seems OK now....Thank you so much for your help and persistance. kind regards André "Dave Peterson" wrote in message ... I created a single worksheet workbook in xl2003. I named that sheet Start. I put 1 in A1. I put your code in the ThisWorkbook module. I tried to save the workbook and your message popped up and the workbook closed. Maybe something else is going on??? I even put some junk in the before_Close event and it didn't crash. Option Explicit Private Sub Workbook_BeforeClose(Cancel As Boolean) MsgBox "hi from close" End Sub Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) If Me.Worksheets("Start").Cells(1, 1).Value = 1 Then Cancel = True MsgBox "You have already Eliminated Rows! THIS FILE WILL NOT SAVE!" Me.Close False End If End Sub I added the cancel = true and it didn't make any difference. Andre Croteau wrote: Hi Dave, I have created the simplest of files, with no data except what I would put in cell A1 of sheet "START" I have built it from scratch in two different computers, and both of them CRASH when I try to save the file with the value 1 in cell A1. Does it have to do with the fact that the commands inside the "BeforeSave" event actually deals with NOT saving the file??? (or does it have to do with Boolean stuff ?) Any other way to accomplish what I want to do? Thanks for your help This is the code that I have put in the "ThisWorkbook" section Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) If Worksheets("Start").Cells(1, 1) = 1 Then MsgBox "You have already Eliminated Rows! DON'T SAVE THIS FILE!" Me.Close savechanges:=False End If End Sub "Dave Peterson" wrote in message ... I don't think it's the code. If you build a smaller workbook with just enough data to get through your code, does it work ok? (Just a guess.) Andre Croteau wrote: In both instances of trying the solutions of Steve and Dave, it caused Excel to crash, as it did with my original solution. In Steve's case, it first closed that specific file without saving it, but the macro saved the second file that was opened. Then it crashed! "Excel encountered a problem and must close" In Dave's example, the message came up "Excel encountered a problem and must close" I am at a loss... "Andre Croteau" wrote in message ... Hello, I have tried this macro to disable the save function if the cell A1 contains the value 1 when the user wants to save the file I want the file to close without asking if the user any questions, but there seems to be a coding error. Can anyone help please? PS I imagine I should also use the same code for the event "Private Sub Workbook_BeforeClose"? Thank you in advance André Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) If Worksheets("Start").Cells(1, 1) = 1 Then MsgBox "You have already Eliminated Rows! THIS FILE WILL NOT SAVE!" ActiveWorkbook.Close = False End If End Sub In ELIMINATE ROWS MACRO: Worksheets("Start").Cells(1,1).Value = 1 -- Dave Peterson -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Save Condition | Excel Worksheet Functions | |||
| Save If Condition is true | Excel Programming | |||
| Code to Test Condition and Save Record to Excel | Excel Programming | |||
| Excel marcos firing on file save as but not file save | Excel Programming | |||
| Save File to Another Directory, but not change Users File Save location | Excel Programming | |||
Linear Mode
