Reopening XLSM causes #VALUE errors

[Fred Urble]

I created a new Excel 2007 sheet with VBA code modules in it.
All functions work perfectly when I create the sheet for the first time.
When trying to save it, I must do so as an XLSM file due to it telling me it is a VB Project.
Upon reopening the file again, I get a security message to enable macros, which I allow.
All cells that had the working function before now show as #VALUE.

I have tried changing the Excel trust settings to enable all macros but it does not solve the problem when reopening.

What am I missing??

[Winon]

Hello Fred Urble,

Welcome to the Forum.

In Excel Trust Settings, click on Macro Settings, and then under Developer Macro Settings, "Check" the box to the left of "Trust access to the VBA project object model".

Hope that helps.

[Fred Urble]

Thanks for your quick response!

This setting is already checked.
I have also tried setting the radio button for macro setting above it to allow all macro access vs. being prompted to allow access.
Either way causes the same results......when creating the sheet for the first time it works.....after saving and reopening with no changes, it shows #VALUE.

I would add that the VBA code references to a DLL which is in the same folder as the spreadsheet.

Any other ideas to try?

[Winon]

Hello Fred Urble,

Thank you for the feedback.

I think the problem might be lying here somewhere.

I would add that the VBA code references to a DLL which is in the same folder as the spreadsheet.

It is difficult to give you a ready solution without seeing your code, but my guess is that somewhere, in one ore more of your VBA subs, or modules, you may try to turn Culculation to False at the beginning of the Code, and then towards the end of the Code, back on to Automatic again.

Regards

[Fred Urble]

I was able to find the problem.

I was putting the DLL file in the same directory as the spreadsheet per instructions.
When I put the DLL into the Windows folder, the sheet began calculating properly.

Thanks for your help and replies.

[Winon]

Hello Fred Urble,

Good, glad you were able to sort it out!

Please remember to mark your thread as "Solved".

Regards.