workbook_open no longer occurs

[Steve the large]

I have a file that contains code in the workbook open event.

I have been making periodic enhancements and am up to "version 18" of the
app I am creating. The workbook open event stopped running when I open the
file.

I stripped out the code and just put a 'msgbox "text here..."' call in the
event to just see if it fires off. It does not. All sheet_activate events
still work. All macros run, including workbook_beforeclose, but not
workbook_open.

The workbook_open event still operates as expected in other workbooks,
including "version 17", and un-related workbooks.

I created a brand new workbook, copied version 18's sheets, modules, forms,
names and properties over, and the workbook_open still doesnt work.

Searched the KB, and this is NOT a workbook.open call. I am just double
clicking on the file in windows explorer to start (and my shift key is not
stuck down).

Any suggestions would be appreciated greatly.

 

[Susan]

steve - sometimes when debugging stuff you get to an
application.enableevents=false
& then jump out of it before you get to
application.enableevents=true

when i start having weirdness like this, i do up a quickie subroutine:

sub reset_everything()
application.enableevents=true
application.screenupdating=true
application.calculation=xlmanual
end sub

and whatever else you generally turn off during subs. see if that
helps.

susan

 

[Susan]

obviously that should have been
application.calculation=xlautomatic

duh
susan

 

[Tom Ogilvy]

sounds like you might have Workbook_Open misspelled. (maybe an extra
underscore or something.) given it is in the ThisWorkbook module.

 

[Steve the large]

Well...

One of the first things I did was to comment out my workbook_open() sub
(which had been working in all versions before) so that there was no
"workbook_open()" event.

THEN, I allowed the vbe interface to create a new stub workbook open routine
into which I put the messagebox code. Here is what my workbook_open routine
looks like right now:

Private Sub Workbook_Open()
MsgBox prompt:="How now brown cow"
End Sub

Thanks for the thought, though. As I stated before, this routine was
working right along until something slammed into it's facade.

 

[Steve the large]

Susan, thanks for the reply, I did try that, by putting the enableevents =
true in another routine. But my macro events are running along fine. Its
just the workbook_open() routine that is causing the problem. It just
stopped running in a single workbook. Other workbooks (previous versions,
which I keep, and unrelated workbooks) still work perfectly fine.

 

[Tom Ogilvy]

actually, you said two workbookis:

I created a brand new workbook, copied version 18's sheets, modules, forms,
names and properties over, and the workbook_open still doesnt work.

Now you need to repeat this, but don't copy over everything. Copy over
parts of the workbook until you find which part cause it to stop working.

 

[Steve the large]

Yeah, that's what I was thinking about. The process takes about an hour and
I was hoping I was doing something either stupid, obvious, or known. I can
also just go back to the previous version and make the changes there, but
there were a lot of changes and it doesn't tell me what went wrong. Thanks
for the advice, I'll try it and get back to the thread.

 

[Steve the large]

Update -I can get workbook_open event to fire now, but it's a weird
interaction.

I just finished copying over the code modules, names, forms, worksheets to a
brand new file. Every so often I saved off a copy of the document and opened
the new document with macros enabled. Each time I would see my message box
text being from within Workbook_open.

Until I did the very last thing....

I have four custom properties defined, they are "debug", "version",
"userID", and "AutoSense". They are Y/N, Text, Text, Y/N. They are not
linked to content.

When I added these, my message stopped displaying. Note that there is no
code running that interacts with these properties. The workbook_open() sub
contains only a msgbox call.

I went back to the original file, deleted all the custom properties, and the
workbook_open() sub started running there, also.

I added the custom properties back, (now named gDeb, gVer, gUser, gAS),
workbook_open() stopped working again.

I tried linking them to content in the workbook. This made no difference.

At this point I am mightily confused, I have a work around (don't use custom
properties) but I don't like it. Anyone have any thoughts?

 

[Mark Lincoln]

Since I was unaware of custom properties prior to reading this thread,
I created a workbook and put the following code from VBA Help file
into a Workbook_Open Sub in the ThisWorkbook module...

Private Sub Workbook_Open()

Dim wksSheet1 As Worksheet

Set wksSheet1 = Application.ActiveSheet

' Add metadata to worksheet.
wksSheet1.CustomProperties.Add _
Name:="Market", Value:="Nasdaq"

' Display metadata.
With wksSheet1.CustomProperties.Item(1)
MsgBox .Name & vbTab & .Value
End With

End Sub

....and it runs just as expected. You may want to try something
similar. If it works, it then begs the question: Where are you
defining your custom properties?

Have you used custom properties before with success, or is this your
first use of them? (You may want to post an example of your code.)

Which version of Excel are you using?

Mark Lincoln

 

[Steve the large]

Mark, the custom properties were working fine for several weeks in previous
versions of the file. (The workbook_open routine had been working exactly as
expected in these previous versions).

Something happened (either there was a file corruption, I hit an internal
and as-yet undocumented limit on namespaces, or an interaction is occurring
with an add-in (because of which I removed all Add-Ins, just to simplify/be
sure)) and the workbook_open() event seemed to stop occurring.

To answer your question, I created the custom properties through the
file.properties dialog, under the "custom properties" tab, and am accessing
them through the code.

The App I'm working on has 5 modules, 4 forms, one template worksheet with
several controls, 5 "user interaction" worksheets with controls, and several
summary report sheets. The template is used to create dashboards for various
projects. The overall file size can vary from 1 to 2.5 Meg, and has worked
flawlessly until the workbook_open stopped executing.

When I copy the sheets over to a new sheet, the defined names become quite
long, and I'm wondering if anyone knows the limit on the defined name
namespace. Does anyone know the hard limits on the amount of space available
for custom properties names? Are they using the same bucket within the code
to hold these variables? Right now, I'm leaning towards some type of
overwrite occurring from that namespace into executing code. I'm going to
pursue this and see if I can get the problem to re-occur just through this
method.

At this point I'm starting to think it is a bug in Excel, rather than a
coding problem.

 

[Mark Lincoln]

Okay, another guess: You mention copying over worksheets to a new
workbook (with the resulting defined names "becoming quite long"). I
can imagine that anything getting ever-larger over time is going to
eventually become a problem. I wonder if it would be better to save
the "template" workbook under a new name using Save As and work with
that, rather than copying sheets to a new workbook each time.

Mark Lincoln

 

[Steve the large]

Excellent suggestion, I'm under some limitations, though.

This has to be a single workbook, because that is what I am updating.

Some background is in order....

I'm doing this as a favor to another dept. at a company to which I'm
consulting. Not charging for it.

They had a single workbook with multiple sheets, each sheet being used as a
"dashboard". Multiple people access this single workbook through eRoom.
Opening it up, modifying their dashboard, and putting it back. In the
original workbook they would keep a blank dashboard (sheet) as a template.
They would manually copy this worksheet and fill in tab name and project
information into the copy. This was all done manually.

The group managing this presentation process has two "facilitators" who are
charged with ensuring that the PMs update their sheets. The PMs then give
presentations once a month to the muckies with the dashboads as a
presentation tool. Almost all these people are very unsophisticated Excel &
eRoom users, with little training. I want to make their life easier, and
that means as few changes over what they were doing before as possible.

They are using eroom because the PMs do not all have access to a common
server.

I am creating some automation for the facilitators, so that the dashboard is
created automatically, and there is a tracking/control worksheet where the
facilitators can see all projects at a glance. Along the way I've added some
bells and whistles to make their life easier, but I'm afraid I'm limited to a
single workbook.

There are usually no more than 30-40 projects going on simultaneously so I
wasn't expecting a problem. There are six summary worksheets for different
areas of the company, and when a new dashboard is required, they just click
on a menu choice that brings up a simple dialog to put in project manager,
area, facilitator, etc. Then the template is copied and links are
automatically added to the summary sheets.

Not really that huge in concept.

During this round of debugging, I copied the sheets over to a new workbook,
which had a single call to msgbox routine in the workbook_open( ) sub.
Periodically I would save off to an intermediate file and open that with
macros enabled to see if workbook_open event was functioning.

When I copied sheets over to a new workbook, the defined names (about twenty
or so) get really big because the complete path & file name is pre-pended to
the defined name "linking" the new workbook to the old workbook. I would
then have to go into the defined names and manually remove this file path
data. My concern is that the size of memory allocated to the storage of the
defined names may be dynamically re-allocated beyond some limit, overwriting
code execution space, and that the "properties" may be linked to this memory
storage allocation. These are just guesses at this point. I programmed
Apps in C for 13 years on PCs, and I saw weird stuff like this many times. I
wish I had some decent debbugging tools for this, but sigh.

I would be ecstatic to discover something as mundane as a coding reason for
this weirdness, then I could definitly fix it. Until I find the reason for
this weird occurance, I don't trust the app I've designed using Excel. -Not
a good place for my head, or the people I'm trying to help.

Thanks for the suggestion though, if I had more leeway, I would prefer to
keep the workbooks smaller with more separation of code from data.

 

[Mark Lincoln]

You know you've got a tough problem when you start wishing it was your
fault....

I'm afraid I don't have any better idea of what's happening than you
do. But this has never stopped me from guessing.

You mentioned that the problem occurred after you added the custom
properties, and that this was the last thing you did. Have you tried
adding those to the previous version before making any other changes?
I'm thinking that if the Workbook_Open() code still works afterward,
then you can make your changes one at a time and test each change for
failure of Workbook_Open(). This sounds like it could be quite
tedious for you, but if there's some weird interaction between new
code and custom properties then that's possibly the only way to find
it.

Good luck. I'm hoping to read about a happy resolution soon.

Mark Lincoln

 

[Steve the large]

Sorry, I must have described the problem wrongly. The properties functioning
was added several weeks ago, long before the problem occurred. The older
version worked well, workbook_open() worked just fine as I was adding in that
functionality.

In my previous posts, I was copying the non-working version to a new
workbook, first by copying sheets over, correcting the defined names as I
went. With macros OFF, I'd copy one or two sheets, save off the new file as
a separate version, close everything down. THEN open the "saved off" copy
with macros enabled -and see if my "heartbeat" code (the msgbox routine in
the workbook_open()) would fire off.

If it did, then I closed the "saved off" file.

REopen the new target file with macros disabled, reopen the broken file
(macros disabled), and copy a few more sheets over to the target, and then
save off a different version, close up and re-open & look for heartbeat.

I worked my way through all the sheets this way -no problem.

Then I imported the modules, one at a time, saving off, closing, opening
the saved off, checking for msg.

Then I imported the forms -still working.

Then I manually added the four, little properties manually to the target
program (the very last thing) by opening the files/properties menu. Viola
-the target program heartbeat stopped occurring.

To make sure, I closed the target, re-opened the broken file, and *JUST*
deleted the four custom properties. I made no changes to any code, just
removed the props from via the file/properties... dialog. The 'broken'
version started working -the workbook_open() routine was firing off and I
started seeing the msgbox message. Weird, hunh?

It's still possible I have bad code in there that is re-corrupting the file
somehow, after twenty years as a professional programmer, I've seen
everything. Or it could be a corrupted registry or some such related
nonsense. Being without Admin privileges, I'm limited in how far I can dig.

Anyway, thanks for the support and advice.

 

[Mark Lincoln]

I think I'm the one who wasn't clear. I was wondering what might
happen if you set those custom properties in the new workbook before
copying over the sheets, modules, etc. Would Workbook_Open() stop
working immediately after importing the first sheet or the first
module or....? This method could point to a particular area you can
check more closely.

Also, I can't remember (and don't have time right now to check,
sorry)--did you try adding the custom properties one at a time? Since
we're deep into the weirdness zone with this problem, anything is
possible. Plus it wouldn't take nearly as long as re-importing
everything.

Mark Lincoln