Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
BeforeSave, Cancel and ReadOnly
Hello
I am using the BeforeSave event to replace the normal save action with one that determines changed data and writes it to a database. The basic structure is as follows: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) MsgBox "This would be the custom save action" Cancel = True End Sub This works fine except when the workbook is read-only, which I require to prevent users from messing around with the xls file, deleting it accidently or whatever. Then it displays the message: 'savetest.xls' is read-only. To save a copy, click OK, then give the workbook a new name in the Save As dialog box. and continues as it should do with the custom action. How do I get it to stop displaying that message box? It's entirely useless in this case and just confuses people. Thanks Steve |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
BeforeSave, Cancel and ReadOnly
Ah it's ok I think I found a solution:
' In a module: Public Sub ReplacementSave() MsgBox "This is the replacement save subroutine" End Sub ' In ThisWorkbook: Private Sub Workbook_Activate() For Each C In Application.CommandBars.FindControls(ID:=3) C.OnAction = "ReplacementSave" Next Application.MacroOptions Macro:="ReplacementSave", HasShortCutKey:=True, ShortcutKey:="s" End Sub Private Sub Workbook_Deactivate() For Each C In Application.CommandBars.FindControls(ID:=3) C.OnAction = "" Next Application.MacroOptions Macro:="ReplacementSave", HasShortCutKey:=False End Sub Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) MsgBox "This message should not have appeared" Cancel = True End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
BeforeSave, Cancel and ReadOnly
Users will be prompted to save changes if they try to close the workbook or
close Excel and Excel thinks changes have been made, so you might need to add this to the ThisWorkbook module: Private Sub Workbook_BeforeClose(Cancel As Boolean) ReplacementSave Saved = True End Sub -- Jim "Steve Hunter" wrote in message ... | Ah it's ok I think I found a solution: | | | ' In a module: | | Public Sub ReplacementSave() | MsgBox "This is the replacement save subroutine" | End Sub | | | ' In ThisWorkbook: | | Private Sub Workbook_Activate() | For Each C In Application.CommandBars.FindControls(ID:=3) | C.OnAction = "ReplacementSave" | Next | Application.MacroOptions Macro:="ReplacementSave", HasShortCutKey:=True, ShortcutKey:="s" | End Sub | | Private Sub Workbook_Deactivate() | For Each C In Application.CommandBars.FindControls(ID:=3) | C.OnAction = "" | Next | Application.MacroOptions Macro:="ReplacementSave", HasShortCutKey:=False | End Sub | | Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) | MsgBox "This message should not have appeared" | Cancel = True | End Sub | |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
BeforeSave, Cancel and ReadOnly
I think the simplest solution would be to put the line
Application.Displayalerts = False in the WorkBook_Open event to surpress the errormessage as the saving doesn't seem to be a problem |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
BeforeSave, Cancel and ReadOnly
Thanks, that works fine. I noticed that without trapping the BeforeClose event
the save before close dialog would just keep popping back up again and again when clicking Yes. "Jim Rech" wrote: Users will be prompted to save changes if they try to close the workbook or close Excel and Excel thinks changes have been made, so you might need to add this to the ThisWorkbook module: Private Sub Workbook_BeforeClose(Cancel As Boolean) ReplacementSave Saved = True End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
BeforeSave, Cancel and ReadOnly
That does work but it affects all other workbooks open in Excel too, where I
may want to see the alerts. dq wrote: I think the simplest solution would be to put the line Application.Displayalerts = False in the WorkBook_Open event to surpress the errormessage as the saving doesn't seem to be a problem |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Workbook_BeforeClose(Cancel As Boolean) - Cancel won't work | Setting up and Configuration of Excel | |||
Input box to cancel sub when Cancel is clicked. | Excel Programming | |||
Disabling 'Cancel' option when saving work (Yes/No/Cancel) | Excel Programming | |||
How to use Excel VBA to cancel Excel readonly status | Excel Programming | |||
Cancel Macro is user selects 'cancel' at save menu | Excel Programming |