class module

[Rinku261]

Hi There

Is there any way I want each time when I close any of the workbooks it pops up a msg and if I select yes it allows me to close me the file. If I choose no, then it keeps the file open. It should be something like the built-int message that asks if I want to save an unsaved workbook, but I want this to work even if the workbook is in a saved state.
I think it has to go in the Personal Macro Workbook since I want it to work with every workbook opened on my machine. I’ve heard it requires something called a class module, but how do I do that in a personal macro workbook?


I know its tricky, but I believe in you people. Please help.
cheers
Rinku

[Bob Phillips]

Option Explicit

Private WithEvents app As Application

Private Sub app_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean)
If MsgBox("are you sure that you want to close " & Wb.Name, vbYesNo, "App File Close") = vbNo Then

Cancel = True
End If
End Sub

Private Sub Workbook_Open()
Set app = Application
End Sub


This is workbook event code.
To input this code, right click on the Excel icon on the worksheet
(or next to the File menu if you maximise your workbooks),
select View Code from the menu, and paste the code

[Rinku261]

Hi There

I entered this code pressing F11 (Into VBA ) in this work book code but seems it s not working...........

Secondly i want this funtion to be applied on all the excel file which i open or will open in future .......I mean before i close any workbook it should ask .....if i say Yes only then it should allow me to close the workbook...................

This code should wrk on all or every excel file which i open

Thanks

Regards
Rinku

[Kenneth Hobs]

Obviously, code like that would go into your Personal workbook, not the open workbook. Also, you would need to run that Open event for it to "work" the first time or close the Personal workbook or excel and then Open.

When you use that code and close a workbook, it also shows the message for each XLAM file as well so you may need additional code unless you really want that.

[Bob Phillips]

I can assure you that it works, but as Kenneth says, put it in Personal.xls, save Personal.xls, close Excel and restart. Then see if it works.

You should at least test that it is not Personal that is closing



Private Sub app_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean)
If Wb.Name <> Me.Name Then

If MsgBox("are you sure that you want to close " & Wb.Name, vbYesNo, "App File Close") = vbNo Then

Cancel = True
End If
End If
End Sub