Click here to Skip to main content
14,300,474 members
Rate this:
Please Sign up or sign in to vote.
See more:
I have a little problem with Excel - some steps outside my base-competency ...

I want to do some action if the Excel-Workbook opens. I found that the Workbook_Open-method (declared inside the Workbook-script) is responsible for this.
If I save my file in Excel97-Format as xyz.XLS it works properly.
But if I save it als xyz.XLSM (I use Excel 2010) this Event isn't raised - independand of what I'm doing inside the method. Every other Event-Method (inside the Workbook or a table) is working properly.

So ... where is my mistake ?
Or should I change a Property anywhere to another value ?

What I have tried:

Searching in Internet, severall tries ... ask a question here ... :)
Posted
Updated 12-Sep-19 0:17am
Comments
Maciej Los 11-Sep-19 8:30am
   
Ralf, show your code, which is "not working". What method do you use to save Excel file: via code or manually?
Ralf Meier 11-Sep-19 8:48am
   
Hi Maciej,
It's complete independant what I do in the Workbook_Open-method - it works if I save it at Excel97-XLS and it doesn't work if I save it as Excel-XLSM.
I save it manually - that means : the manual-saved XLS-File starts the method when opening the Workbook and does the action - the XLSM-File (with the complete same content) does it not !!!
In the moment I use the old XLS-Format to have the function - but this could not be the Solution ... :(
Maciej Los 11-Sep-19 8:52am
   
Have you tried to compile VBA code before saving excel file with xlsm extension?
Ralf Meier 11-Sep-19 8:55am
   
Sorry ... Excel and compile ???
Maciej Los 11-Sep-19 9:19am
   
Yeah! Go to VBA Code Pane (ALT + F11), Menu "Debug->Compile VBAProject".
Ralf Meier 11-Sep-19 9:27am
   
No change ...
But I think that Excel does this automaticly too ...
Maciej Los 11-Sep-19 9:33am
   
Well Excel doesn't do that automatically.
Have you tried to open this xlsm file on another machine? Have you accepted Excel warning while first run of xlsm file?
Ralf Meier 11-Sep-19 15:13pm
   
Yes ... same behaviour.
And Yes (of course) I switched the Macro-Security inside the Trustcenter to the lowest Level.
It doesn't come from the content of the method - it works properly if I run it in Debug-mode AND ALSO (once again) if I save the absolutely same file in Excel97-Format (*.XLS).
That isn't understandable for me ... but I suppose that there is a Property (inside Excel) which is allready disabled which forces this behaviour.
To make all complete : there is another method inside the Workbook-Object (Workbook_BeforeClose) - this method works allways (also inside the XLSM-File) - means : this Event is raised (or the Handler is connected to this method) and it seams for me that in case of the Workbook_Open-method the Eventhandler is not connected to the method. But this behaviour could not be manipulated by me ... :(

I think, we know each other a little and I hope you know that you need not to search for the basic-failures at me ;-)
Maciej Los 11-Sep-19 15:40pm
   
:)
Weel, sh...t happens... Can you do favour to me? Create new workbook, add Workbook_Open() method and save it with both formats. Then, can you share them (via Google Drive or something similar)? I'd look at them and let you know if the same strange behaviour is happend on my machine.
Ralf Meier 12-Sep-19 3:15am
   
Nice suggestion ...
If I create a new (naked) XLSM-File it works - I call a MsgBox inside Workbook_Open and this happens.
But that doesn't solve my problem - the question is still the same : why does it (my project) works as a XLS-File and why doesn't it work as a XLSM-File ?
What I haven't tried until now is (but I will do it soon) :
Save the project as XLS, then open the XLS and save (and convert) it to XLSM. We will see ...
Maciej Los 12-Sep-19 3:30am
   
Seems your original workbook is broken (and cannot be properly saved in OpenXML format). As i mentioned, sometimes it happens.
Ralf Meier 12-Sep-19 5:04am
   
I don't believe that it is broken - perhaps something different.
I made my test - negative result. It's still the same : XLS works (without any restrictions) and with XLSM the Workbook_Open doesn't work. Each other functionality (and there is a lof of it) is working correct ...
I suppose there is a Setting which does that - but I have no idea where ...

But thanks for your support, Maciej ... :-)
Maciej Los 12-Sep-19 5:47am
   
Sorry, but there's no setting. Think of it. If newly created workbook works as well, why your original workbook does not work properly when you save it as xlsm? It is broken and can't be save in OpenXML format.
Good luck!
Ralf Meier 12-Sep-19 5:54am
   
I agree (basicly) with you ... and also not (I tried the same with some other Workbooks I have - with the same result) ...
Maciej Los 11-Sep-19 15:41pm
   
No, i'm not searching basic-failures at you ;)

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

In reference to our discussion in comments to the question...

There's no setting (in Excel workbook), which swithces/forces execution of Workbook_Open() macro between *.xls and *.xlsm files. That macro is executed independant of version of Excel file. See: Workbook.Open event (Excel) | Microsoft Docs[^]

I think there's something wrong with MS Office installation/configuration. Please, follow the steps from: Excel Macro-Enabled workbook (.xlsm) macros disabled and no obvious way to re-enable - Stack Overflow[^]
   
Comments
Ralf Meier 12-Sep-19 6:45am
   
Very interesting ... I tried all without any real success.
But ... if I go to the Trustcenter and disable the Macro-Execution to "Select disable all macros with notification" and restart Excel I have to confirm the macro-execution. If I decide "Yes" it works (the Workbook_Open) - but only this one try. A 2nd call causes into the allready known result.
So it becomes more and more not understandable for me.
At home, where I tried it also, I have a different Office-Version than at work - with the same result. So it can't be Version-depandant.
But it could perhaps be a "Feature" from something different which is existing at work and also at home ...

So, sorry Maciej, I can't accept this Solution ...
Maciej Los 12-Sep-19 6:57am
   
I do not expect that you accept my solution. I wanted to provide information, which may help to people with the same issue.

It seems like you do not have enough rights to change Office settings permament. It might be caused by windows policies (re-creation of your profile is recommended) or Office installation (repair is required).

Good luck!
Ralf Meier 5 days ago
   
I have thought a bit about what you have written :
It is not logical that I have a problem with my Office-Installation : if you are right it would not be that a new Workbook could do it. It must be something inside the Workbook itself - but also it could not be so (because of all the things I wrote before).
But I understood - it seams to be a common issue ... but it also seams that there is no Solution. So I will work with the Workbooks in Excel97-Format and have the functionality. But that is a stupid work-around ... sorry ...

I gave you the 5+ for your assistance and your support ...
It's allways a pleasure to write and discuss with you Maciej ...
Maciej Los 5 days ago
   
Thank you, Ralf.
It's also pleasure to discuss with you.

Finally, i'd suggest to read this thread: excel - Is there a compatibility issue between macros written for XLS, XLSX and XLSM? - Stack Overflow[^]. There you may find something useful in resolving your issue (especially in a second answer).

Cheers!
Maciej

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100