Re: workbook_open no longer occurs

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 work*** 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" wrote:

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 work***.
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


On Jun 19, 5:14 pm, Steve the large
<Stevethela...@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
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?



"Steve the large" wrote:
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.

"Tom Ogilvy" wrote:

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.

--
Regards,
Tom Ogilvy

"Steve the large" wrote:

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.

"Susan" wrote:

obviously that should have been
application.calculation=xlautomatic

duh
susan

On Jun 19, 1:09 pm, Steve the large <Steve the
l...@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
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.- Hide quoted text -

- Show quoted text -



.