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
---------
Private Sub Workbook_BeforeClose(Cancel As Boolean)
ThisWorkbook.Save
End Sub
----------
Private Sub Workbook_open()
Userform1.Show
End Sub

The order isn't important--it does have to be under the ThisWorkbook module,
though.



Curt wrote:

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
---------
Private Sub Workbook_BeforeClose(Cancel As Boolean)
ThisWorkbook.Save
End Sub
----------
Private Sub Workbook_open()
Userform1.Show
End Sub

--

Dave Peterson

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.

Curt wrote:

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
---------
Private Sub Workbook_BeforeClose(Cancel As Boolean)
ThisWorkbook.Save
End Sub
----------
Private Sub Workbook_open()
Userform1.Show
End Sub

--

Dave Peterson

"Dave Peterson" wrote:

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.

Curt wrote:

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
---------
Private Sub Workbook_BeforeClose(Cancel As Boolean)
ThisWorkbook.Save
End Sub
----------
Private Sub Workbook_open()
Userform1.Show
End Sub

--

Dave Peterson

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.

"Dave Peterson" wrote:

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.

Curt wrote:

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
---------
Private Sub Workbook_BeforeClose(Cancel As Boolean)
ThisWorkbook.Save
End Sub
----------
Private Sub Workbook_open()
Userform1.Show
End Sub

--

Dave Peterson

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("YourCapt ion").Delete
If Err.Number < 0 Then
Err.Clear
Exit Do
End If
Loop
On Error GoTo 0
End Sub

Jake wrote:

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.

"Dave Peterson" wrote:

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.

Curt wrote:

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
---------
Private Sub Workbook_BeforeClose(Cancel As Boolean)
ThisWorkbook.Save
End Sub
----------
Private Sub Workbook_open()
Userform1.Show
End Sub

--

Dave Peterson


--

Dave Peterson

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.

"Dave Peterson" wrote:

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("YourCapt ion").Delete
If Err.Number < 0 Then
Err.Clear
Exit Do
End If
Loop
On Error GoTo 0
End Sub

Jake wrote:

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.

"Dave Peterson" wrote:

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.

Curt wrote:

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
---------
Private Sub Workbook_BeforeClose(Cancel As Boolean)
ThisWorkbook.Save
End Sub
----------
Private Sub Workbook_open()
Userform1.Show
End Sub

--

Dave Peterson


--

Dave Peterson

Sorry Dave, I forgot the End If statement. It works fine now. Thanks for the
immediate response.

"Jake" wrote:

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.

"Dave Peterson" wrote:

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("YourCapt ion").Delete
If Err.Number < 0 Then
Err.Clear
Exit Do
End If
Loop
On Error GoTo 0
End Sub

Jake wrote:

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.

"Dave Peterson" wrote:

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.

Curt wrote:

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
---------
Private Sub Workbook_BeforeClose(Cancel As Boolean)
ThisWorkbook.Save
End Sub
----------
Private Sub Workbook_open()
Userform1.Show
End Sub

--

Dave Peterson


--

Dave Peterson

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.

Jake wrote:

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.

"Dave Peterson" wrote:

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("YourCapt ion").Delete
If Err.Number < 0 Then
Err.Clear
Exit Do
End If
Loop
On Error GoTo 0
End Sub

Jake wrote:

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.

"Dave Peterson" wrote:

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.

Curt wrote:

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
---------
Private Sub Workbook_BeforeClose(Cancel As Boolean)
ThisWorkbook.Save
End Sub
----------
Private Sub Workbook_open()
Userform1.Show
End Sub

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

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.

"Dave Peterson" wrote:

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.

Jake wrote:

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.

"Dave Peterson" wrote:

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("YourCapt ion").Delete
If Err.Number < 0 Then
Err.Clear
Exit Do
End If
Loop
On Error GoTo 0
End Sub

Jake wrote:

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.

"Dave Peterson" wrote:

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.

Curt wrote:

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
---------
Private Sub Workbook_BeforeClose(Cancel As Boolean)
ThisWorkbook.Save
End Sub
----------
Private Sub Workbook_open()
Userform1.Show
End Sub

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson