ExcelBanter - Saving

Adhavan Veeraiyan wrote:
I have written an macro to save the contents of the excel
sheet into a database whenever the SAVE is clicked in the
standard command bar. If the excel template is a read only
one, when the save is clicked excel gives a message "
Template is read only choose a different name to save"

One approach would be to take over the File / Save menu item and
associated toolbar buttons by assigning your macro to them.

This would need to be done carefully - ensuring that you remove the
assignment when your workbook is not active or is closed.

Something along these lines

Sub MySave()
If ActiveWorkbook.Name = ThisWorkbook.Name Then
MsgBox "MySave"
ElseIf ActiveWorkbook.ReadOnly Or ActiveWorkbook.Path = "" Then
Application.Dialogs(xlDialogSaveAs).Show
Else
ActiveWorkbook.Save
End If
End Sub

Sub GrabSave()
Dim CB As CommandBar
Dim CTL As CommandBarControl
For Each CB In Application.CommandBars
Set CTL = CB.FindControl(ID:=3, recursive:=True)
If Not CTL Is Nothing Then
CTL.OnAction = "MySave"
End If
Next
Application.OnKey "^S", "MySave"
End Sub

Sub ReleaseSave()
Dim CB As CommandBar
Dim CTL As CommandBarControl
For Each CB In Application.CommandBars
Set CTL = CB.FindControl(ID:=3, recursive:=True)
If Not CTL Is Nothing Then
CTL.OnAction = ""
End If
Next
Application.OnKey "^S"
End Sub

and in ThisWorkbook module

Private Sub Workbook_Activate()
GrabSave
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
ReleaseSave
End Sub

Private Sub Workbook_Open()
GrabSave
End Sub

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - reply in newsgroup