Personal Excel workbook Macro

[rinku261]

Hi There

I have a macro and i want it to be auto run on all excel file automatically
I have saved the code in Pesonal.xls (Which automatcally opens when excel start) but its not working

What does this code does or what i am aiming to achive here
Every time i close any of the excel file it should show me a msg ...if i click on Yes then it closes the file if no then not


Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim answer As String
Dim question As String
question = "Have taken rest"
answer = MsgBox(question, vbYesNo)
If answer = vbNo Then
MsgBox "Please take rest"
Cancel = True
Exit Sub
Else
ThisWorkbook.Save
End If
End Sub


Please help me with this

 

[Joe4]

The "Workbook_BeforeClose" event procedure will only work on the workbook that it is stored in. So if your store it in your Personal Excel Macro workbook, it will only work when you close your Personal Excel macro workbook.

I think you would have to save that code in every workbook that you want to appy it to.

 

[rinku261]

Ok Jst a quick question how does excel show a massgage "Do you want to Save the changes you made in "XXXX File" everytime when you close any excel file

If that funcationality in excel is available then i guess my problem can be solved as well ...As need smthing similar to tht...Each time we close any file it show me an massgage


Pls any body help

 

[Joe4]

That is inherent to Excel. I do not know of any way of tapping into it. It seems to me that it you would have to do something on the Excel executable level, not on the spreadsheet level, and I doubt you can hack your way into that.

Maybe someone else has some ideas, but I do not know of any way to do what you are after.

 

[rinku261]

Any one else may help me on this