Stopping other wkbks sharing code

[rob nobel]

How can I stop this code working except for the wkbk it's associated with?
If I open the wkbk that has this code and then another wkbk, and I try to
close both wkbks at the same time....kapow!

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.Calculation = xlCalculationAutomatic
Application.CellDragAndDrop = True
If Sheet4.FilterMode Then Sheet4.ShowAllData
If Sheet4.Range("N13") > 0 Then Sheet4.Range("N5:N12") = False
Sheet10.Select
End Sub

Thanks
Rob

 

[Dave Peterson]

What does kapow mean?

Your code worked ok for me in my simple test.

Are you sure you have sheet4 and sheet10?

And what's in N13 of sheet4? (not an error?)

 

[rob nobel]

Hi Dave, there's no problem with the actual code. But if I should open a new
workbook and then exit Excel whilst the new workbook is active then
"kapow"...
which is the Ozzy version of....
kaput or kaputt ke­pot¢, (slang)
adjective ruined; broken; not working; smashed.
[German]

It seems to me that the new workbook thinks that the before close event
belongs to it, and, as it does not have a Sheet10, it come up with the
"kapow" error of ....
"Method select of object_worksheet failed", with Sheet10.Select highlighted.
Now I presume I can simply get rid of this problem by making the code
reference the actual workbook name that it's attached to but I cannot
understand why another workbook should be reading code that doesn't belong
to it.

Rob

 

[Dave Peterson]

I've never seen excel do this.

Maybe you should post your code that's kablewie! (my technical term.)

Hi Dave, there's no problem with the actual code. But if I should open a new
workbook and then exit Excel whilst the new workbook is active then
"kapow"...
which is the Ozzy version of....
kaput or kaputt ke­pot¢, (slang)
adjective ruined; broken; not working; smashed.
[German]

It seems to me that the new workbook thinks that the before close event
belongs to it, and, as it does not have a Sheet10, it come up with the
"kapow" error of ....
"Method select of object_worksheet failed", with Sheet10.Select highlighted.
Now I presume I can simply get rid of this problem by making the code
reference the actual workbook name that it's attached to but I cannot
understand why another workbook should be reading code that doesn't belong
to it.

Rob

What does kapow mean?

Your code worked ok for me in my simple test.

Are you sure you have sheet4 and sheet10?

And what's in N13 of sheet4? (not an error?)

 

[rob nobel]

Hi Dave
Firstly, my kapow comes from reading too many Batman comics....but where
does yours come from?

Try this and see if it comes up with the kapow (kablewie) error message.

I created a blank wkbk and put this code in ThisWorkbook module.
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sheet4.Select
End Sub

(I made sure I had 4 sheets in that book!)

I then created another wkbk (default 3 sheets only) and tried to exit Excel
WHILST THAT WKBK WITHOUT THE CODE WAS ACTIVE. (Don't just exit the
wkbk....exit the Excel program!)

And there it is ...kapow!

I have Excel 9 (2000)
Rob

I've never seen excel do this.

Maybe you should post your code that's kablewie! (my technical term.)

Hi Dave, there's no problem with the actual code. But if I should open a new
workbook and then exit Excel whilst the new workbook is active then
"kapow"...
which is the Ozzy version of....
kaput or kaputt ke­pot¢, (slang)
adjective ruined; broken; not working; smashed.
[German]

It seems to me that the new workbook thinks that the before close event
belongs to it, and, as it does not have a Sheet10, it come up with the
"kapow" error of ....
"Method select of object_worksheet failed", with Sheet10.Select highlighted.
Now I presume I can simply get rid of this problem by making the code
reference the actual workbook name that it's attached to but I cannot
understand why another workbook should be reading code that doesn't belong
to it.

Rob

What does kapow mean?

Your code worked ok for me in my simple test.

Are you sure you have sheet4 and sheet10?

And what's in N13 of sheet4? (not an error?)

rob nobel wrote:

How can I stop this code working except for the wkbk it's associated with?
If I open the wkbk that has this code and then another wkbk, and I

try
to

close both wkbks at the same time....kapow!

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.Calculation = xlCalculationAutomatic
Application.CellDragAndDrop = True
If Sheet4.FilterMode Then Sheet4.ShowAllData
If Sheet4.Range("N13") > 0 Then Sheet4.Range("N5:N12") = False
Sheet10.Select
End Sub

Thanks
Rob

 

[Frank Kabel]

Hi Rob
try
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Me.Sheet4.Select
End Sub


--
Regards
Frank Kabel
Frankfurt, Germany

Hi Dave
Firstly, my kapow comes from reading too many Batman comics....but
where does yours come from?

Try this and see if it comes up with the kapow (kablewie) error
message.

I created a blank wkbk and put this code in ThisWorkbook module.
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sheet4.Select
End Sub

(I made sure I had 4 sheets in that book!)

I then created another wkbk (default 3 sheets only) and tried to exit
Excel WHILST THAT WKBK WITHOUT THE CODE WAS ACTIVE. (Don't just exit
the wkbk....exit the Excel program!)

And there it is ...kapow!

I have Excel 9 (2000)
Rob

I've never seen excel do this.

Maybe you should post your code that's kablewie! (my technical
term.)

Hi Dave, there's no problem with the actual code. But if I should
open a new workbook and then exit Excel whilst the new workbook is
active then "kapow"...
which is the Ozzy version of....
kaput or kaputt ke­pot¢, (slang)
adjective ruined; broken; not working; smashed.
[German]

It seems to me that the new workbook thinks that the before close
event belongs to it, and, as it does not have a Sheet10, it come
up with the "kapow" error of ....
"Method select of object_worksheet failed", with Sheet10.Select
highlighted. Now I presume I can simply get rid of this problem by
making the code reference the actual workbook name that it's
attached to but I cannot understand why another workbook should be
reading code that doesn't belong to it.

Rob

What does kapow mean?

Your code worked ok for me in my simple test.

Are you sure you have sheet4 and sheet10?

And what's in N13 of sheet4? (not an error?)

rob nobel wrote:

How can I stop this code working except for the wkbk it's
associated with? If I open the wkbk that has this code and then
another wkbk, and I try to close both wkbks at the same
time....kapow!

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.Calculation = xlCalculationAutomatic
Application.CellDragAndDrop = True
If Sheet4.FilterMode Then Sheet4.ShowAllData
If Sheet4.Range("N13") > 0 Then Sheet4.Range("N5:N12") = False
Sheet10.Select
End Sub

Thanks
Rob

 

[rob nobel]

Frank,
This produces the error message "Method or data member not found" just
trying to close that wkbk by itself.
Rob

Hi Rob
try
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Me.Sheet4.Select
End Sub


--
Regards
Frank Kabel
Frankfurt, Germany

Hi Dave
Firstly, my kapow comes from reading too many Batman comics....but
where does yours come from?

Try this and see if it comes up with the kapow (kablewie) error
message.

I created a blank wkbk and put this code in ThisWorkbook module.
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sheet4.Select
End Sub

(I made sure I had 4 sheets in that book!)

I then created another wkbk (default 3 sheets only) and tried to exit
Excel WHILST THAT WKBK WITHOUT THE CODE WAS ACTIVE. (Don't just exit
the wkbk....exit the Excel program!)

And there it is ...kapow!

I have Excel 9 (2000)
Rob

I've never seen excel do this.

Maybe you should post your code that's kablewie! (my technical
term.)

rob nobel wrote:

Hi Dave, there's no problem with the actual code. But if I should
open a new workbook and then exit Excel whilst the new workbook is
active then "kapow"...
which is the Ozzy version of....
kaput or kaputt ke­pot¢, (slang)
adjective ruined; broken; not working; smashed.
[German]

It seems to me that the new workbook thinks that the before close
event belongs to it, and, as it does not have a Sheet10, it come
up with the "kapow" error of ....
"Method select of object_worksheet failed", with Sheet10.Select
highlighted. Now I presume I can simply get rid of this problem by
making the code reference the actual workbook name that it's
attached to but I cannot understand why another workbook should be
reading code that doesn't belong to it.

Rob

What does kapow mean?

Your code worked ok for me in my simple test.

Are you sure you have sheet4 and sheet10?

And what's in N13 of sheet4? (not an error?)

rob nobel wrote:

How can I stop this code working except for the wkbk it's
associated with? If I open the wkbk that has this code and then
another wkbk, and I try to close both wkbks at the same
time....kapow!

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.Calculation = xlCalculationAutomatic
Application.CellDragAndDrop = True
If Sheet4.FilterMode Then Sheet4.ShowAllData
If Sheet4.Range("N13") > 0 Then Sheet4.Range("N5:N12") = False
Sheet10.Select
End Sub

Thanks
Rob

 

[Frank Kabel]

Hi
try
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Me.worksheets("Sheet4").Select
End Sub

But one question: Why do you want to select sheet4 before closing?


--
Regards
Frank Kabel
Frankfurt, Germany

Frank,
This produces the error message "Method or data member not found" just
trying to close that wkbk by itself.
Rob

Hi Rob
try
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Me.Sheet4.Select
End Sub


--
Regards
Frank Kabel
Frankfurt, Germany

Hi Dave
Firstly, my kapow comes from reading too many Batman comics....but
where does yours come from?

Try this and see if it comes up with the kapow (kablewie) error
message.

I created a blank wkbk and put this code in ThisWorkbook module.
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sheet4.Select
End Sub

(I made sure I had 4 sheets in that book!)

I then created another wkbk (default 3 sheets only) and tried to
exit Excel WHILST THAT WKBK WITHOUT THE CODE WAS ACTIVE. (Don't
just exit the wkbk....exit the Excel program!)

And there it is ...kapow!

I have Excel 9 (2000)
Rob

I've never seen excel do this.

Maybe you should post your code that's kablewie! (my technical
term.)

rob nobel wrote:

Hi Dave, there's no problem with the actual code. But if I should
open a new workbook and then exit Excel whilst the new workbook is
active then "kapow"...
which is the Ozzy version of....
kaput or kaputt ke­pot¢, (slang)
adjective ruined; broken; not working; smashed.
[German]

It seems to me that the new workbook thinks that the before close
event belongs to it, and, as it does not have a Sheet10, it come
up with the "kapow" error of ....
"Method select of object_worksheet failed", with Sheet10.Select
highlighted. Now I presume I can simply get rid of this problem by
making the code reference the actual workbook name that it's
attached to but I cannot understand why another workbook should be
reading code that doesn't belong to it.

Rob

What does kapow mean?

Your code worked ok for me in my simple test.

Are you sure you have sheet4 and sheet10?

And what's in N13 of sheet4? (not an error?)

rob nobel wrote:

How can I stop this code working except for the wkbk it's
associated with? If I open the wkbk that has this code and then
another wkbk, and I try to close both wkbks at the same
time....kapow!

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.Calculation = xlCalculationAutomatic
Application.CellDragAndDrop = True
If Sheet4.FilterMode Then Sheet4.ShowAllData
If Sheet4.Range("N13") > 0 Then Sheet4.Range("N5:N12") =
False Sheet10.Select
End Sub

Thanks
Rob

 

[rob nobel]

Hi Frank.
Thanks for the suggestions. Doesn't the Me bit just force the code to action
the active wkbk?
Alas, it seems that Excel still wants to run this code on all other open
wkbks as well. (BUT ONLY when exiting the Excel program!)
Your last suggestion allows me to close the wkbk ok, but I don't know if
you're aware of the original problem which is that having a
Workbook_BeforeClose event seems to interfere with other workbooks that are
unrelated to the file that has that event.

The problem: Have the wkbk open that has this Workbook_BeforeClose event in
it and also have another wkbk open with only 3 sheets.
Try and EXIT EXCEL while the wkbk without the event is active, and an error
message comes up seemingly because it can't find sheet 4 in the active wkbk.
But the event should have nothing to do with the active wkbk! Only with the
wkbk that that event is in.

Wanting to select sheet 4 before closing is only a test example.
I actually want sheet 10 open before closing which happens to be the "Home"
page for this wkbk and I have some stuff on there that needs actioning in
certain circumstances.
I can get around the problem OK but it's more a reason as to why this is
happening. I've developed a wkbk and in testing it to make sure nothing odd
happens when a user gets hold of it I came across this strange behaviour.

Rob

Hi
try
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Me.worksheets("Sheet4").Select
End Sub

But one question: Why do you want to select sheet4 before closing?


--
Regards
Frank Kabel
Frankfurt, Germany

Frank,
This produces the error message "Method or data member not found" just
trying to close that wkbk by itself.
Rob

Hi Rob
try
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Me.Sheet4.Select
End Sub


--
Regards
Frank Kabel
Frankfurt, Germany

rob nobel wrote:
Hi Dave
Firstly, my kapow comes from reading too many Batman comics....but
where does yours come from?

Try this and see if it comes up with the kapow (kablewie) error
message.

I created a blank wkbk and put this code in ThisWorkbook module.
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sheet4.Select
End Sub

(I made sure I had 4 sheets in that book!)

I then created another wkbk (default 3 sheets only) and tried to
exit Excel WHILST THAT WKBK WITHOUT THE CODE WAS ACTIVE. (Don't
just exit the wkbk....exit the Excel program!)

And there it is ...kapow!

I have Excel 9 (2000)
Rob

I've never seen excel do this.

Maybe you should post your code that's kablewie! (my technical
term.)

rob nobel wrote:

Hi Dave, there's no problem with the actual code. But if I should
open a new workbook and then exit Excel whilst the new workbook is
active then "kapow"...
which is the Ozzy version of....
kaput or kaputt ke­pot¢, (slang)
adjective ruined; broken; not working; smashed.
[German]

It seems to me that the new workbook thinks that the before close
event belongs to it, and, as it does not have a Sheet10, it come
up with the "kapow" error of ....
"Method select of object_worksheet failed", with Sheet10.Select
highlighted. Now I presume I can simply get rid of this problem by
making the code reference the actual workbook name that it's
attached to but I cannot understand why another workbook should be
reading code that doesn't belong to it.

Rob

What does kapow mean?

Your code worked ok for me in my simple test.

Are you sure you have sheet4 and sheet10?

And what's in N13 of sheet4? (not an error?)

rob nobel wrote:

How can I stop this code working except for the wkbk it's
associated with? If I open the wkbk that has this code and then
another wkbk, and I try to close both wkbks at the same
time....kapow!

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.Calculation = xlCalculationAutomatic
Application.CellDragAndDrop = True
If Sheet4.FilterMode Then Sheet4.ShowAllData
If Sheet4.Range("N13") > 0 Then Sheet4.Range("N5:N12") =
False Sheet10.Select
End Sub

Thanks
Rob

 

[Dave Peterson]

Remember that you can only select a range on an activesheet. Well, the same
thing holds for selecting a sheet. The workbook has to be active. (Sorry I
missed that in your first post.)

Option Explicit
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Me.Activate
Sheet4.Select
End Sub

But when I did this, the _beforeclose event stopped processing. And both
workbooks stayed open and so did excel (xl2002).

And kablewie may have come from the Batman TV show (from the 60's). Or maybe
that was kapowie!

And I think Frank makes an excellent point. Why do this in _beforeclose? Why
not do the equivalent thing in Workbook_open?

I think it's easier there and if you're trying to make it nice for the next
person, then I think it's a lot easier setting it up before the user starts
editting.

Hi Dave
Firstly, my kapow comes from reading too many Batman comics....but where
does yours come from?

Try this and see if it comes up with the kapow (kablewie) error message.

I created a blank wkbk and put this code in ThisWorkbook module.
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sheet4.Select
End Sub

(I made sure I had 4 sheets in that book!)

I then created another wkbk (default 3 sheets only) and tried to exit Excel
WHILST THAT WKBK WITHOUT THE CODE WAS ACTIVE. (Don't just exit the
wkbk....exit the Excel program!)

And there it is ...kapow!

I have Excel 9 (2000)
Rob

I've never seen excel do this.

Maybe you should post your code that's kablewie! (my technical term.)

Hi Dave, there's no problem with the actual code. But if I should open a new
workbook and then exit Excel whilst the new workbook is active then
"kapow"...
which is the Ozzy version of....
kaput or kaputt ke­pot¢, (slang)
adjective ruined; broken; not working; smashed.
[German]

It seems to me that the new workbook thinks that the before close event
belongs to it, and, as it does not have a Sheet10, it come up with the
"kapow" error of ....
"Method select of object_worksheet failed", with Sheet10.Select highlighted.
Now I presume I can simply get rid of this problem by making the code
reference the actual workbook name that it's attached to but I cannot
understand why another workbook should be reading code that doesn't belong
to it.

Rob

What does kapow mean?

Your code worked ok for me in my simple test.

Are you sure you have sheet4 and sheet10?

And what's in N13 of sheet4? (not an error?)

rob nobel wrote:

How can I stop this code working except for the wkbk it's associated
with?
If I open the wkbk that has this code and then another wkbk, and I try
to
close both wkbks at the same time....kapow!

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.Calculation = xlCalculationAutomatic
Application.CellDragAndDrop = True
If Sheet4.FilterMode Then Sheet4.ShowAllData
If Sheet4.Range("N13") > 0 Then Sheet4.Range("N5:N12") = False
Sheet10.Select
End Sub

Thanks
Rob