Excel BeforeClose() Event 3

[kjv1611]

I am using the AutoOpen event to unprotect my workbooks and worksheets on open, but cannot seem to get the opposite to happen in the beforeclose event. Actually, I've not gotten anything to work with the BeforeClose() Event in my first attempts. How would I go about using the BeforeClose() Event, and actually getting it to work. Any examples, instructions, websites, tutorials, etc. welcome. I would like any information anyone has on this event. The BeforeSave event and/or the BeforeClose event would be great. Also, if someone could tell me how all the events trigger in Excel like in Access. (I have 3 Access books, but no Excel books to date for reference. My books are from Sybex, and have been most helpful).

Thanks for any information in this area.

BTW, here is my code linked to a button for protecting the sheets/workbook:

Sub ProtectMyWorkBook()
'
' ProtectMyWorkBook Macro
' Macro recorded 5/4/2004 by sferguson
'
   Dim shtCurrent As Worksheet
   
   ActiveWorkbook.Protect "admin", Structure:=True, Windows:=False

   ' Loop through each worksheet in the active workbook
   For Each shtCurrent In ActiveWorkbook.Worksheets

      ' Protect the worksheet. Allow the users to format
      ' and sort cells.
        shtCurrent.Protect Password:="admin", _
            Contents:=True, _
            DrawingObjects:=True, _
            Scenarios:=True, _
            AllowFormattingCells:=True, _
            AllowFormattingColumns:=True, _
            AllowFormattingRows:=True, _
            AllowSorting:=True, _
            AllowFiltering:=True
    Next
End Sub

I basically copied and pasted the code from the msdn website, then edited it to my liking. Here is the link where I found it, in case anyone needs some information in protecting workbooks/sheets (this is not "hacker proof", but it does good enough for my purposes.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnexcl2k2/html/odc_xlwebProtSht.asp

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[Loomah]

Stephen
Although you probably have (or at least are a long way toward achieving) your solution these links may be of some interest regarding what events are and the order in which they fire (in Excel).

http://www.cpearson.com/excel/events.htm

http://www.cpearson.com/excel/AppEvent.htm

;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[TonyJollans]

Hi Stephen,

I've had a bit of a play with this now and the workbook close event doesn't happen for every workbook. On my (2000) setup at the moment I have my Personal.xls, The Analysis Toolpak Add-In and Adobe Acrobat (6) Add-In - none of which have visible windows. The event only appears to happen for the Toolpak - I don't know why at the moment.

I get the same error you get so that obviously isn't a viable check. I suggest you try and see what is running which you don't want and, perhaps, make explicit checks against workbook names.

Rather than saving, you could just set the Workbook .Saved Property to True after making your changes (both in the Open and Close Events) - but remember to check (on Close) to see if there are any actual changes for which you (or your User) should be prompted. Pseudocode is (more or less) ..

(Open)
Unprotect
wb.saved = true

(Close)
If Wb.saved = false then wb.save
Protect
wb.saved = true

I am a bit busy today - it is my son's birthday and I have to get a trampoline made up and sited before he gets home from school. I will check in from time to time and will try and find out about the criteria which trigger the event. Meanwhile, good luck! And the links which Loomah posted to Chip Pearson's site probably make good reading.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[kjv1611]

Thanks for the links, Loomah. The info there could be most useful.

TonyJollans,
Thanks for the effort, and thanks for the info on the save part. I'll look more at it later on today. Go have some fun w/your son! [THUMBSUP2]

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[kjv1611]

GREAT NEWS!!

Well, I was having more errors with the whole If Wb.Windows(1).Visible = False Then desal (which would be good if I knew how to use it), so I just commented it out for testing. Then, I did some more research in the help file of Excel VBA, and found the "saved" property, and thought that'd be an option as apposed to running ActiveWorkBook.Save and ThisWorkBook.Save . Those for some reason run multiple times. All I had to do was set the .Saved value of each to True, and the whole thing closed in a split second, while protecting and saving both files (didn't protect Personal.xls, don't think) on close.

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[TonyJollans]

Hi Stephen,

Setting .Saved to True doesn't Save, so no changes will be preserved on disk (to Personal.xls or anything else). As I said, you do need to watch out for the cases where you have made changes which you DO want saved.

I'm still looking into which workbooks the event triggers for and why, and may post a separate question here if I can't sort it out.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[kjv1611]

Thanks for that info.. I'll be trying to search on it as well, and post back if find info before I see any follow-up posts. Probably won't be until next week, now, however. Thanks a bunch for your efforts!

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[kjv1611]

Okay, well this one has driven me crazy enough, I think. For a while, I had it causing Excel to crash, I think b/c of a memory overflow or something. Other times, It seems to just try to save "PERSONAL.XLS" and whatever Workbook I have open at the time multiple times (it's not trying to save the other workbooks (add-ins), b/c I have a Select Case statement clearing those out (or so it seems). Well, anyway, Here's what I've got so far - it runs, but still tries to save the Personal and the currently used (visible) workbook multiple times. Don't know why, if I say "no" to my msgbox for saving. Anyways, for now I may just do withought the whole protecting on save thing. I can just use a button to protect everything for now. If anyone has any good ideas on this, or further follow-up, post away, I'll be sure to review anything here, so as to possibly be able to use this method...

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV