Vba Doesn t Work Again Unless I Close Workbook

08-29-2007,04:00 AM

Hullo

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

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

08-29-2007,04:10 AM

Why not just save information technology in the Before_Close event?

08-29-2007,04:22 AM

xld

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

The trouble is that the 'before close' procedure I have at the moment looks for a fill-in of the current file and if it finds the backup information technology puts a date/time stamp on it and moves it to an archive binder. The backup file is only created if the current file is saved. Nevertheless, if the user chooses 'yeah' to the prompt 'Do you desire to save the changes you lot made', I think in that location 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 non 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.

08-29-2007,04:25 AM

The trouble is that there are so many ways to shut 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 fill-in from inside the BeforeClose yourself.

08-29-2007,05:49 AM

xld

I can't add together a salvage entry to the BeforeClose event as the user may decide to abandon any changes they accept made one time they click the shut button. Having a salvage 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 shut, salvage, etc file as long as there was not an obvious manner for them to practise something else (like click on the close push!).

Therefore, I nevertheless think the best way to continue in this circumstance is to deactivate the close push. Is there anyway to do this?

08-29-2007,06:05 AM

I disagree profoundly.

It seems to me that yous are making alien statements.

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

In mail service #5 you said ... tin can't add a relieve entry to the BeforeClose consequence as the user may determine to abandon whatsoever changes

Either yous desire the backup to exist made regardless, or y'all will let the user to abandon, and therefore no backup is to be made. In the quondam, relieve as I suggested. If the latter, exam if the file is dirty, if so, practice the 'Do you want to save' message yourself and human action appropriately, if its non dirty, just create the fill-in.

Messing with Excel is rarely necessary in my experience.

08-29-2007,06:57 AM

xld

I fabricated both those statements initially in post # 3 really. I don't observe them conflicting in my listen, so I probably oasis'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 Fill-in). Since I am renaming the backup file by inserting a date/time stamp and then moving it to another binder, this means there is no file called 'Backup of...' unless a user after makes changes and decides to salve 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 proceed the changes they have made they will save the file before trying to shut it. Part of the procedure I have in the Before_Close event checks to meet if there is a file named 'Backup of...', if it finds this file it saves the current workbook again and then that the 'Backup of...' file is now an exact copy of the current file and and so puts a date/time stamp in it'south filename before moving it to an archive folder.

I have a problem if the user forgets to save the file and clicks the shut button and so answers 'Yes' to the 'Do you want to relieve the changes yous have made' prompt. Because altough the 'Backup of...' file is created, it does not go archived to some other folder. I have tried amending the code using the wait method simply this did not work for me either.

In post # 6 yous say do the 'do yous want to save' message yourself and act appropriately. Not certain how to practise that. I am attaching the code in the Before_Close effect so you lot tin meet 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") And so
Application.ScreenUpdating = False
ActiveWorkbook.Relieve 'to brand the backup file (if ane 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 identify date/time stamp in filename
Awarding.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
Proper name oldname Every bit newname
Application.ScreenUpdating = Fake
Cease If
Finish Sub[/VBA]

08-29-2007,07:14 AM

Mayhap you could incorporate it into a earlier salvage event:
[VBA]Private Sub Workbook_BeforeSave(ByVal SaveAsUI Every bit Boolean, Cancel Every bit Boolean)
Finish Sub[/VBA]

08-29-2007,07:45 AM

Hi lucas

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

08-29-2007,10:14 AM

This is the sort of thing that I envisaged

[vba]

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

Individual Sub Workbook_BeforeSave(ByVal SaveAsUI Every bit Boolean, Cancel As Boolean)
Dim sFile
Awarding.EnableEvents = False
'<Optional - this would exist before relieve code>
Cancel = True
If SaveAsUI Then
sFile = Awarding.GetOpenFilename("Excel Files (*.xls), *.xls")
If sFile <> Simulated So
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.Proper name, ".xls", "") & Format(At present, "yyyymmdd hh:mm:ss")
ThisWorkbook.SaveCopyAs sFile
End If
Application.EnableEvents = True
Cease Sub
[/vba]

08-30-2007,01:eighteen AM

xld

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

08-30-2007,01:59 AM

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

[vba]

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

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

08-30-2007,03:39 AM

Thanks xld

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

[VBA]Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Abolish Every bit Boolean)
Dim sFile
Dim location As String
Awarding.EnableEvents = Fake
location = "c:\log\staff authorisations archive\"
sFile = Supersede(ThisWorkbook.Proper name, ".xls", " Backup ") & Format(Now, "yyyymmdd hh-mm-ss")
ThisWorkbook.SaveCopyAs location & sFile & ".xls"
Awarding.EnableEvents = True
End Sub
[/VBA]

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

Thank you once more

08-30-2007,04:20 AM

And no nasty deactivating built-in functions :-)

08-thirty-2007,04:22 AM

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

08-30-2007,07:24 AM

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

Vba Doesn t Work Again Unless I Close Workbook

Solved: Deactivate workbook close button

0 Response to "Vba Doesn t Work Again Unless I Close Workbook"

Post a Comment

Iklan Atas Artikel

Iklan Tengah Artikel 1

Iklan Tengah Artikel 2

Iklan Bawah Artikel