PDA

View Full Version : Solved: Workbook close event



jwise
11-29-2007, 02:47 PM
Is there a way to put out a message in a workbook close event that informs the user whether he/she has been in the IDE? Or better yet, that he/she looked at certain macros, and modified/created/deleted others?

This would provide me some insurance...

lucas
11-29-2007, 02:54 PM
insurance against what jwise? Is the message for you or someone else using your file? I would say "backup often"

I guess I'm missing something because when I close excel I get a message that tells me whether I changed anything on the workbook including the vbe and asks me if I want to save those changes...

I think you are stretching the capabilities of even visual basic....but I've been wrong before. Maybe someone will have an idea.

jwise
11-29-2007, 03:26 PM
I probably did a poor job of explaining my question. I apologize.

The enemy is me, i.e. I am trying to protect myself against mistakes I make. Saving data and saving code are two distinctly different activites (to me!). I dislike Excel's message about saving because I don't know which of these (data or code) the message is about.

I am trying to determine which macros were changed upon workbook exit. I admit I have been in the situation where I wanted to save my data but throw away my newly introduced bugs, and vice versa. So this is a way to be certain of the changes. If I know all the macros that were changed (and looked at), I have more information to use to decide about saving.

I guess you can tell that I am not a fan of data and code in the same file.

Simon Lloyd
11-29-2007, 03:33 PM
If you are worried and are not allowed to protect the project for viewing then why not have all your macros in another protected workbook from which your original workbook can call the procedures?

jwise
11-29-2007, 03:45 PM
Thanks for the suggestion. I was not aware of that capability. It does seem I have seen some "qualified" calls, but I've never seen this.

This does not meet my objective, however. Hopefully, I'll have an add-in at some point, and I can use it on all my code and workbooks. I considered this the "simple" case, because it is the most straight forward.

I guess you can say I'm trying to separate code management from VBE. I asked about displaying messages because the information could be easily verified in my test cases. The plan is to do more than display messages in the future.

jwise
12-06-2007, 08:52 AM
I found the solution. Thanks to all the responders because you led me there. Due to a small programming bug, I overlooked this solution on the first pass, but I found my way back to it.



I can't find if you can decipher which macro changed, but I can tell if the object which contains the macro(s) has been saved since its last change. Here is some code to demonstrate this:
Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent
Dim VBCode As VBIDE.CodeModule
Dim Change As String

Set VBProj = ActiveWorkbook.VBProject

For Each VBComp In VBProj.VBComponents
Change = "No "

If VBComp.Saved = False Then Change = "Yes"
... other code ...


Naturally "Change (Yes or No)" indicates whether the object has changed since it was last saved. This has worked for me. I still have some additional testing to do, and I do not claim that this is a "best" solution.

Thanks again to all responders.