BeforeSave, Cancel and ReadOnly

[Steve Hunter]

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

 

[Steve Hunter]

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

 

[Jim Rech]

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
| 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
|

 

[dq]

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

 

[Steve Hunter]

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.

 

[Steve Hunter]

That does work but it affects all other workbooks open in Excel too, where I
may want to see the alerts.