beforeclose placement

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Does the placement of the beforeclose have any bearing on operation. It has a
few times failed this is why I ask following is my placement. Does it need to
be last?
Thanks

Option Explicit
 
The order isn't important--it does have to be under the ThisWorkbook module,
though.
 
Any chance that you have other workbook/worksheet events and in any of those
events you've toggled the application.enableevents to off (without resetting it
back to on).

This would mean that the workbook_beforeclose event would not fire.
 
Dave Peterson said:
Any chance that you have other workbook/worksheet events and in any of those
events you've toggled the application.enableevents to off (without resetting it
back to on).

This would mean that the workbook_beforeclose event would not fire.
Click to expand...
 
I have similar problem. Put a Pop-up calendar in personal.xls by Dave Green.
The beforeclose event doesn't work to clear the calendar from the right click
menu. I have numerous add-ins that are password protected. Don't know if the
applications.enableevents is on or off. He has a work-a-round in the open
event. Also, should I put Option Explicit in. Excel doesn't.

Sorry about the previous blank reply.
 
If events are off, then any event that you're trying to use won't fire.

Maybe you could use the auto_close procedure (in a general module).

Option Explicit
Sub Auto_Close()
On Error Resume Next
Do
Application.CommandBars("Cell").Controls("YourCaption").Delete
If Err.Number <> 0 Then
Err.Clear
Exit Do
End If
Loop
I have similar problem. Put a Pop-up calendar in personal.xls by Dave Green.
The beforeclose event doesn't work to clear the calendar from the right click
menu. I have numerous add-ins that are password protected. Don't know if the
applications.enableevents is on or off. He has a work-a-round in the open
event. Also, should I put Option Explicit in. Excel doesn't.

Sorry about the previous blank reply.
Click to expand...
 
I get a Loop without Do error. I put Option Expicit at the top of the module
I am using. There are other macros in there.
 
Sorry Dave, I forgot the End If statement. It works fine now. Thanks for the
immediate response.

Jake said:
I get a Loop without Do error. I put Option Expicit at the top of the module
I am using. There are other macros in there.
Click to expand...
 
The "option Explicit" is a directive to excel that tells it that you want to be
forced to declare all your variables in that module.

I saved this from a previous post:

If I add "Option Explicit" to the top of a module (or have the VBE do it for me
via tools|options|Editor tab|check require variable declaration), I know that
most of my typos will stop my code from compiling.

Then I don't have to spend minutes/hours looking at code like this:
ctr1 = ctrl + 1
(One is ctr-one and one is ctr-ell)
trying to find why my counter isn't incrementing.

And if I declare my variables nicely:

Dim wks as worksheet
not
dim wks as object
and not
dim wks as variant

I get to use the VBE's intellisense.

If I use "dim wks as worksheet", then I can type:
wks.
(including the dot)
and the VBE will pop up a list of all the properties and methods that I can
use. It saves time coding (for me anyway).

And one final selfish reason.

If I use a variable like:

Dim ThisIsACounterOfValidResponses as Long

I can type
Thisis
and hit ctrl-space and the VBE will either complete the variable name or give me
a list of things that start with those characters.

And by using a combination of upper and lower case letters in my variables, the
VBE will match the case found in the declaration statement.
I get a Loop without Do error. I put Option Expicit at the top of the module
I am using. There are other macros in there.
Click to expand...
 
Thanks, Dave. This makes a lot of sense as to why I would use Option
Explicit. Somehow I still need to figure out why my BeforeClose Event doesn't
work.
 
Back
Top