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

 

[Molby]

Have you got the code set in the Workbook event of ThisWorkbook? In your VBE window on the left, double click on ThisWorkbook. Then on the right click down from General to Workbook, then on the far left select the BeforeClose event.

You should then get

Private Sub Workbook_BeforeClose(Cancel As Boolean)

End Sub

 

[kjv1611]

Thanks, I need to get in the habit of using the drop down menus, instead of just typing it in. That works for closing the Personal.xls file, but it doesn't do for the ActiveWorkBook. Any idea how to change it for that? If you go into a blank module, there are no dropdowns, so not sure if it is possible, or how so. I tried putting this into an empty module under the Personal.xls file:

Private Sub ActiveWorkbook_BeforeClose(Cancel as Boolean)
     MsgBox "Close Active Book"
End Sub

I only put this code in to test the event, but it's a no go. Any idea as to the correct format for this? Anybody?

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

 

[PHV]

The event procedure is:
Private Sub Workbook_BeforeClose(Cancel As Boolean)

End Sub

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[xlbo]

You need to doubleclick on Thisworkbook in the project window for the file you are working on - once in there, you should choose the before_close event from the dropdown - the event is linked to a specific workbook - there is no Activeworkbook event as all events are held and driven from the source workbook itself

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[kjv1611]

Thanks for the info, all. However, I want the code to run solely from the Personal.xls file for any workbook I open, and preferably the one with the current focus. Any way to do the same thing here? It worked when I put the BeforeClose event in the "This Workbook" of the current workbook, but then, when I open that workbook, I have to "enable macros", and I would have to put that into every workbook. Is it possible to use for just the Active workbook, or the workbook with the current focus withought putting the code in each workbook?

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

 

[Molby]

Why do you need to use the beforeclose event then?

Just write into the code in your personal workbook whatever it is you want to do before you shut the other workbook

 

[xlbo]

nope - the event refers to the workbook it sits in only - if there is no beforeclose event held in a workbook, there is no event so it won't fire when the workbook is closed - as Molby says - what is it that needs to run when you close the workbook - maybe there is another way around this ???

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[kjv1611]

I hope there is another way around it, I've just not been succesful in finding it yet. What I want to do is run the code I listed above, I'll repaste it, so you don't have to scroll.

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:="pass", _
            Contents:=True, _
            DrawingObjects:=True, _
            Scenarios:=True, _
            AllowFormattingCells:=True, _
            AllowFormattingColumns:=True, _
            AllowFormattingRows:=True, _
            AllowSorting:=True, _
            AllowFiltering:=True
    Next
End Sub

What it is I want to do is to protect both the workbook, and all contained worksheets upon close or upon save. Either would be sufficient. I think, however, that I've kind of made a way around it, by writing a macro to protect everything, and email it.. will probably add in the code to save after protecting the workbook. Either way, it would be interesting to know if there is a way to do this, so that it could be used in the future for other ideas I may think of. The reason I want to protect the sheets is merely because they are audits (not financial), and it would simply take away temptation for making changes, and it would not allow someone to accidentally change something, which would totally be possible.

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 kjv1611,

You could use the Application WorkbookBeforeClose Event.

In your Personal.xls, Insert a New Class Module; by default it will be called "Class1". Add this code inside it ..

[blue]Public WithEvents xlapp As Application
Private Sub xlapp_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean)

[green]' [b]Wb[/b] is a reference to the Workbook being closed.
' Put your code here with 'Wb' instead of 'ActiveWorkbook'
[/green]
End Sub[/blue]

Next, also in your Personal.xls, Insert a New Module (or open any existing one). Add this code at the start of it ..

[blue]Public SomeName As New Class1[/blue]

And, finally, in the ThisWorkbook section of the Personal.xls, add this code ..

[blue]Private Sub Workbook_Open()
    Set SomeName.xlapp = Application
End Sub[/blue]

Enjoy,
Tony

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

 

[kjv1611]

Tony,
Well, either I did something incorrect, or there's a problem with the code. I'm leaning towards the first, but was hoping you could take a look, and help me out.
First, I'm getting this error message: [BLUE]"Compile error:
Procedure declaration does not match description of event or procedure having the same name"[/BLUE]
Here is the code I now have for my "protect before close" event/class:

Public WithEvents xlapp As Application

Private Sub xlapp_workbookBeforeClose(ByVal wb As Workbook, Cancel As Balloon)
'wb is a reference to the workbook being closed.
'Put your code here with 'wb' instead of 'wb'

' ProtectMyWorkBook Macro
' Macro recorded 5/4/2004 by sferguson
'
   Dim shtCurrent As Worksheet
   
   wb.Protect "pass", Structure:=True, Windows:=False

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

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

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

 

[PHV]

Cancel As Balloon)
should be
Cancel As Boolean)

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[kjv1611]

DOH!!! [SMILE] That was it, PHV! You're a genious, LOL!

Thanks to TonyJollans for the very valuable (to me) code there, and with that, more learning, and thanks to PHV for picking out my WOOPS error! Here's a star to Tony for the most excellent idea with the code, and one to PHV for giving me a big laugh at myself.


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]

A star for Molby I forgot earlier. Though it may not seem like the end-all solution, it was very helpful to me, b/c it helped to remind me to always use the drop-downs if possible, so as to get the correct format, regardless of my memory or my typing.

[SMILE]

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]

One more thing.. (I knew this idea of coding would be most valuable - from TonyJollans). My AutoOpen code was only working for the Personal.xls file after all, so I created a class for it as well, based on the same idea. Works like a charm. So, whenever I open a protected workbook (of my own) it is unprotected, and it is protected when I close it.


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,

You don't need a separate Class for the Workbook_Open Event, just add a new Sub to the Class you already have. I can't remember the name for sure, I think it's xlapp_WorkbookOpen, but you can get it from the dropdowns when editing the class module code.

Enjoy,
Tony

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

 

[kjv1611]

Thanks Tony...
By the way, would that have anything to do with the code running more than once at the end? What I mean is it seemed to be closing pretty slow, so I added in a msgbox to test it, and it ran 5 times the last time, and 10 times the most recent time (after I tried to change it from 2 classes to one class). Any ideas here? I'll post the code I'm using below so you can see what I'm doing:
[BLUE]Class1 Code:[/BLUE]

Public WithEvents xlapp As Application

Private Sub xlapp_workbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean)
'wb is a reference to the workbook being closed.
'Put your code here with 'wb' instead of 'wb'

' ProtectMyWorkBook Macro
' Macro recorded 5/4/2004 by sferguson
'
   Dim shtCurrent As Worksheet
   
   Wb.Protect "pass", Structure:=True, Windows:=False

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

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

ActiveWorkbook.Save
ThisWorkbook.Save
End Sub


Private Sub xlapp_WorkbookOpen(ByVal Wb As Workbook)
'wb is a reference to the workbook being opened.
'Put your code here with 'wb' instead of 'wb'

' UnprotectMyWorkBook Macro
' Macro recorded 5/4/2004 by sferguson
'
    Dim sht As Worksheet
    
        Wb.Unprotect "pass"
        For Each sht In Wb.Sheets
            sht.Unprotect "pass"
        Next
'

'MsgBox "test"
    
End Sub

[BLUE]Code Module to set the new instances of Class1[/BLUE]
Maybe this is where the problem is?

Public NewCloseWorkBook As New Class1
Public NewOpenWorkBook As New Class1

[BLUE]I have this in the "ThisWorkbook" section of Personal.xls[/BLUE]

Private Sub Workbook_Open()
    Set NewOpenWorkBook.xlapp = Application
    Set NewCloseWorkBook.xlapp = Application
End Sub

Also, you'll see I put in ActiveWorkbook.Save and ThisWorkbook.Save in the same code (that's because I keep getting the message "do you want to save changes to the personal macro workbook?" after closing. The ActiveWorkbook.save is to save the protection changes to the active workbook.

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]

Another note on this most recent situation. When I comment out the two lines ActiveWorkbook.Save and ThisWorkbook.save, the whole thing runs faster, but asks me if i want to save changes to the active/open workbook, as well as the Personal Macro Workbook.

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,

Firstly you only need to declare one instance of the Class - removing the second one will halve the number of times the code runs. So, delete ..

[purple]Public NewOpenWorkBook As New Class1[/purple]

.. and ..

[purple] Set NewOpenWorkBook.xlapp = Application[/purple]

Now, the routines run once for each Workbook you have open - including hidden ones and add-ins. So, for example, when you start up or shut down Excel, it is running for your Personal.xls which may be why it is asking if you want to save changes to it.

You probably want to check at the start of each routine what the workbook is. Exactly what you check will depend on exactly what you want to do. To check if a workbook is hidden, I think you have to check the Window, something like ..

[blue][tt]If Wb.Windows(1).Visible = False[/tt][/blue]

Or, perhaps you only want to run the code for certain workbook names - it's up to you!

Do post back if you need further help, but I may not reply until tomorrow now.

Enjoy,
Tony

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

 

[kjv1611]

Understood. I'll try to see what I can do.. I kind of guessed part of my problem was the 2 instances, so took out the 2 I had, and replaced them with
Public WorkbookProtection As New Class1 and
Set WorkbookProtection.xlapp = Application

BYW, I tried the code for checking to see if the windows is hidden, b/c that would definitely be what I want to do, but I keep getting the error message:
[BLUE]Run-time error '9':
Subscript out of range[/BLUE]

It seems it does that whether I use a number or variable for sheet or a variable for an integer counter. I'll work at it tomorrow as well. I did find one post where [BLUE]PHV[/BLUE] was able to answer the problem. Here is the thread I'm talking about: thread707-813908

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