When things are taking a long time, the first thing I do is close excel and
empty my windows Temp folder. (may work for you.)
Workbook_open was added in xl97. Auto_open is kept for backwards
compatibility.
For the most part, each do equivalent work.
But there are differences (which fires first, left as an exercise!), how they
execute (or stop from executing) when the workbook is opened by code in another
workbook.
You use application.enableevents to stop workbook_open.
But you must explicitly run the auto_open if you want it execute:
someworkbook.RunAutoMacros
(look at vba's help for more info.)
And I've never experienced this (or can't recall if I have), but sometimes
workbook_open can get "confused" with some timing issues and if you separate the
code, problems sometimes clear up:
Private Sub Workbook_Open()
Application.OnTime Now, "Continue_Open"
End Sub
(and continue_open is in a general module)
But I've seen similar posts for Auto_open, too.
======
Just my opinion. I find it easier to explain auto_open than workbook_open, so I
usually use that in newsgroup posts.
(and in real life, I usually use auto_open, too--just too lazy!!)
rob said:
None of these make a difference....
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Calculation = xlCalculationManual
ActiveSheet.DisplayPageBreaks = False
It takes nearly 10 secs to open the wkbk (which includes the 3 seconds to go
to sheet10), but I guess I can live with it too.
This one's new to me....workbook_open/auto_open
How does that differ from workbook_open and what is the correct syntax?
Rob
Dave Peterson said:
I thought it was strange--but I like to put stuff like this in the
workbook_open/auto_open. Even if it is distracting to the user, I find it
easier!
Did "application.screenupdating = false" make it less distracting. It still has
a slight delay for me--but I'm willing to live with that.
rob nobel wrote:
Point taken with selecting sheet at opening time. The main reason I did
this at close was that when I insert this at opening time, it takes
a
couple
of seconds for that line "Sheet10.Select"
to execute so the first thing that appears on the screen is the
sheet
that
was open at last save, which doesn't look as good. I too want to
make
it
"nice for the next person" so that all he sees is the home page at startup.
What frutrates me is that something so simple cannot be done without it
causing a problem elsewhere.
I also tried to stick the Me.Activate bit in and Excel 2000 also
stays
open.
Isn't this strange or is it just me that thinks that?
Back to the Bat cave!
Rob
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.
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 kepot¢, (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
