Solved: Deactivate workbook close button

[dragon]

Hi

Does anyone know how I might be able to deactivate this button?

I have a 'before close' procedure which gets messed up if someone tries to close the workbook without saving it first and then chooses 'yes' to the 'Do you want to save the changes you made' prompt.

[Bob Phillips]

Why not just save it in the Before_Close event?

[dragon]

xld

I can't put it in the 'before close' procedure in case the user wants to abandon any changes they have made.

The problem is that the 'before close' procedure I have at the moment looks for a backup of the current file and if it finds the backup it puts a date/time stamp on it and moves it to an archive folder. The backup file is only created if the current file is saved. However, if the user chooses 'yes' to the prompt 'Do you want to save the changes you made', I think there is a delay in Excel saving the file and creating the backup file because my 'before close' procedure does not seem to execute and the file is not moved into the archive folder. (Hope this make sense!).

I am hoping to be able to disable the close button and place command buttons to save, exit etc.

[Bob Phillips]

The problem is that there are so many ways to close the workbook, the X, Alt-F4, File>Close, etc. that you will be forever chasing your tail.

If you save the file on entry to the BeforeClose, you could add a wait to ensure the backup is created, or even create the backup from within the BeforeClose yourself.

[dragon]

xld

I can't add a save entry to the BeforeClose event as the user may decide to abandon any changes they have made once they click the close button. Having a save entry in the BeforeClose event would over-ride their choice.

The users I have in mind are novices and would be happy to use command buttons to close, save, etc file as long as there was not an obvious way for them to do something else (like click on the close button!).

Therefore, I still think the best way to proceed in this circumstance is to deactivate the close button. Is there anyway to do this?

[Bob Phillips]

I disagree profoundly.

It seems to me that you are making conflicting statements.

In post #3 you said ... The backup file is only created if the current file is saved.

In post #5 you said ... can't add a save entry to the BeforeClose event as the user may decide to abandon any changes

Either you want the backup to be made regardless, or you will allow the user to abandon, and therefore no backup is to be made. In the former, save as I suggested. If the latter, test if the file is dirty, if so, do the 'Do you want to save' message yourself and act accordingly, if its not dirty, just create the backup.

Messing with Excel is rarely necessary in my experience.

[dragon]

xld

I made both those statements initially in post # 3 actually. I don't find them conflicting in my mind, so I probably haven't explained myself very well.

The backup file is automatically created by Excel everytime the file is saved and is one save behind (set by File_SaveAs_Tools_General Options_Always Create Backup). Since I am renaming the backup file by inserting a date/time stamp and then moving it to another folder, this means there is no file called 'Backup of...' unless a user subsequently makes changes and decides to save those changes thus again creating new file called 'Backup of...'

The procedure I have in the Before_Close event assumes if the user wants to keep the changes they have made they will save the file before trying to close it. Part of the procedure I have in the Before_Close event checks to see if there is a file named 'Backup of...', if it finds this file it saves the current workbook again so that the 'Backup of...' file is now an exact copy of the current file and then puts a date/time stamp in it's filename before moving it to an archive folder.

I have a problem if the user forgets to save the file and clicks the close button and then answers 'Yes' to the 'Do you want to save the changes you have made' prompt. Because altough the 'Backup of...' file is created, it does not get archived to another folder. I have tried amending the code using the wait method but this did not work for me either.

In post # 6 you say do the 'do you want to save' message yourself and act accordingly. Not sure how to do that. I am attaching the code in the Before_Close event so you can see where I've got to, I've cobbled this together from various sources. Any help would be appreciated...

[VBA]Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim oldname, newname, location, strNewfilename As String
Set filesys = CreateObject("Scripting.FileSystemObject")
If filesys.FileExists("c:\log\Backup of Staff Authorisations.xlk") Then
Application.ScreenUpdating = False
ActiveWorkbook.Save 'to make the backup file (if one exists) an exact copy of current file
location = "c:\log\Staff Authorisations Archive"
strNewfilename = location & "\" & Format(Now, "yyyy-mm-dd h-mm-ss") & " Backup of Staff Authorisations.xlk" 'to place date/time stamp in filename
Application.ScreenUpdating = True
MsgBox "A copy of this file has been archived to " & strNewfilename, , "File Archived"
Application.ScreenUpdating = False
oldname = "c:\log\Backup of Staff Authorisations.xlk"
newname = strNewfilename
Name oldname As newname
Application.ScreenUpdating = False
End If
End Sub[/VBA]

[lucas]

Maybe you could incorporate it into a before save event:
[VBA]Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
End Sub[/VBA]

[dragon]

Hi lucas

Originally had a variation of the code in the BeforeSave event but had to move it to the BeforeClose a few days ago. I can't recall why now, but that was creating some kind of problem.

[Bob Phillips]

This is the sort of thing that I envisaged

[vba]

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim sFile
Application.EnableEvents = False
If Not ThisWorkbook.Saved Then
ThisWorkbook.Save
sFile = Replace(ThisWorkbook.Name, ".xls", "") & Format(Now, "yyyymmdd hh:mm:ss")
ThisWorkbook.SaveCopyAs sFile
End If
Application.EnableEvents = True
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim sFile
Application.EnableEvents = False
'<Optional - this would be before save code>
Cancel = True
If SaveAsUI Then
sFile = Application.GetOpenFilename("Excel Files (*.xls), *.xls")
If sFile <> False Then
ThisWorkbook.SaveAs sFile
sFile = Replace(ThisWorkbook.Name, ".xls", "") & Format(Now, "yyyymmdd hh:mm:ss")
ThisWorkbook.SaveCopyAs sFile
End If
Else
ThisWorkbook.Save
sFile = Replace(ThisWorkbook.Name, ".xls", "") & Format(Now, "yyyymmdd hh:mm:ss")
ThisWorkbook.SaveCopyAs sFile
End If
Application.EnableEvents = True
End Sub
[/vba]

[dragon]

xld

I tried your code but it doesn't do anything. At first I thought it might be because of the ':' in the time format as this would be an invalid character in a filename, I changed it to a hyphen but still nothing?!!

[Bob Phillips]

Okay, I have tested it this time, and this does what I was saying

[vba]

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim sFile
Application.EnableEvents = False
If Not ThisWorkbook.Saved Then
sFile = Replace(ThisWorkbook.Name, ".xls", "") & Format(Now, "yyyymmdd hhmmss")
ThisWorkbook.SaveCopyAs sFile & ".xls"
ThisWorkbook.Save
End If
Application.EnableEvents = True
End Sub


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim sFile
Application.EnableEvents = False
'<Optional - this would be before save code>
Cancel = True
If SaveAsUI Then
sFile = Application.GetOpenFilename("Excel Files (*.xls), *.xls")
If sFile <> False Then
ThisWorkbook.SaveAs sFile
sFile = Replace(ThisWorkbook.Name, ".xls", "") & Format(Now, "yyyymmdd hh:mm:ss")
ThisWorkbook.SaveCopyAs sFile
End If
Else
ThisWorkbook.Save
sFile = Replace(ThisWorkbook.Name, ".xls", "") & Format(Now, "yyyymmdd hh:mm:ss")
ThisWorkbook.SaveCopyAs sFile
End If
Application.EnableEvents = True
End Sub
[/vba]

[dragon]

Thanks xld

Your code helped a lot. In fact for what I was trying to achieve, I was able to do away with the code in the BeforeClose event all together and reduce the code in the BeforeSave event to:

[VBA]Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim sFile
Dim location As String
Application.EnableEvents = False
location = "c:\log\staff authorisations archive\"
sFile = Replace(ThisWorkbook.Name, ".xls", " Backup ") & Format(Now, "yyyymmdd hh-mm-ss")
ThisWorkbook.SaveCopyAs location & sFile & ".xls"
Application.EnableEvents = True
End Sub
[/VBA]

This ensures that a copy of the file is archived only when changes are saved.

Thanks again

[Bob Phillips]

And no nasty deactivating built-in functions :-)

[Bob Phillips]

BTW, doesn't doing away with the BeforeClose still leave you vulnerable? If they don't save it, then they will get a message at the close, and they might say no!

[dragon]

But that's exactly what I wanted. The only reason they would not save (hopefully) would be if they decided the changes they had just made were a mistake and decided to abandon them. Archive only needs to be created if they save the file.