Do you want to save the changes you made to Book1.xls?

[vladk]

Hi guys,

Is there any way to replace this famous message with the custom one when Excel file is read only?

Vladk

 

[Loomah]

Hi Vladk
Here's one possible solution but you haven't given much to work on here. This will tell Excel when the file is closed that it is already saved. You can put your custom message in where I have.

Beware - this will not save the workbook on closing unless you now specifically put the code in to do that.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
ThisWorkbook.Saved = True
MsgBox "Your custom message"
End Sub

;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[vladk]

Hi Loomah,
Your idea is great!
Thank you

Vlad

Private Sub Workbook_BeforeClose(Cancel As Boolean)

On Error GoTo Exit_Err

Dim intResp As Integer

ThisWorkbook.Saved = True
'I assume the file is read-only, which is in my case
intResp = MsgBox("This workbook is read only. " & _
"Would you like to save a copy?", vbQuestion + vbYesNoCancel)

If intResp = vbYes Then
Application.Dialogs(xlDialogSaveAs).Show
End If

Cancel = (intResp = vbCancel)

If Not Cancel Then
'Do my cleaning
End If

Exit Sub

Exit_Err:
MsgBox "Error " & Err.Number & " occured in Workbook_BeforeClose." & vbCr & Err.Description, vbCritical, ActiveWorkbook.Name

End Sub