64bit Excel corrupts xlms with userform when saving it second time

Hi,

I'm in big trouble with a VBA project.

Situation: xlsm file, incuding two userforms. UF1 includes a multipage (8 pages) with about 80 option buttons, 90 labels, 60 text boxes, 20 check boxes and 10 command buttons, using two worksheets (sheet #1 86 columns, sheet #2 just a few constant configuration data). UF2 has about 150 labels and one command button
Works absolutely flawlessly with 32bit Excel versions (2016, 2019). No activeX controls.

With 64bit Excel (2016 as well as 2019)
- I can open and run everything perfectly
- after having it saved, I can open it again and use it
- after having it saved 2nd time, I can open it, but Excel quits immediately when in UserForm_Initialize() any setting of a control's property is being performed, not depending on wether the setting is performed within the initializing routine or via an extra sub. (starting the userform manually shows an error window, claiming the well known error 400) When - with this corrupted file and still within 64bit Excel - I reopen it skipping makro execution, edit anything at any line (for instance adding and instantly removing a colon), save and quit, I again can open and use the file two times.

Opening and using even this "64bit maltreated" file with 32 bit Excel never is a problem. But, unfortunately, the final user has 64bit Excel only. It's quite urgent, they need it for a (medical) scientific project -- and in the current situation, they even can't check the "alpha" and "beta" versions, so further development is pretty impossible.

It's a non profit project, I don't get a cent for it, just a smile.

Any help at the horizon? Would be great.

Should provide sample...

Just to start:

Please Login or Register  to view this content.

should be Me.Text_eingeloggt.Value = ...
Following, where is LoginName declared?. It's a variable or a Control. If you have a control with that name, it gonna crash.

You should check that "Option Explicit" is active on the top of the UserForm code, and then perform a "Compile Workbook" before running the code. That way you could detect declaration errors.

If you have the Option Explicit, and there are no errors, try exporting the UserForm, get it removed from the workbook, save, close and then reimport the Userform.

Thank you for your fast reply.

Me.Text_eingeloggt is a label, simply showing the name of the person who logged in in the userform. So ".Value" is not being offered. Even ".Caption" doesn't change anything, and removing it simply brings the crash to the previous line (and so on).
"Option explicit" is active, "Compile" doesn't recognize any error.
Exporting/re-importing the *.frm doesn't help.
And it's definitely a problem of 64bit Excel, reproducable on different machines, 2016/2019 doesn't matter. There are neither additional controls nor any formulas in the worksheet. And, furthermore, there is no declaration, no API-call in the code. which had to be changed for 64bit.

For me, it very much looks like a bug in Excel 64bit -- any workaround would be welcome. It's a pity that Berlin Charité uses 64bit Office only, but I can't change it.

Many thanks again for your rapid reaction, your patience -- and for coping with my horrible English. I've learned it at school only, some 60 years ago, three lessons/week and during three school years only.

Can you rip the code not related to this thread, and any worksheet with personal data and upload just the basic .xlsm file with the userform that is crashing? Error should be elsewhere, as code shown is quite simple to just crash with these procedures. By the way, is there any reference loaded, or any add-in in the testing machines?

Hi,

The code totals to about 3.500 lines, but nothing highly sophisticated, 99% as simple as what you've seen here... And no, no API calls, no declarations incompatible with 64bit, no AddIns except those standard AddIns which Excel adds when being installed, i.e. same standard MS AddIns for the 64bit and the 32bit versions.

The Problem is not to be found within my code, I'm sure, it is some problem with the file saving procedure of Excel 64bit.

The difference when using it with the 64bit version in fact is: Save it after having it opened in Excel's macro editor and performing a "null" change (add and remove a space, for instance), close the file -- and it runs perfectly when you open it again.
Save it with Excel's standard worksheet's menu function file - save or within the VBA code with ThisWorksheet.Save, ThisWorksheet.SaveAs, or .SaveCopyAs produces a corrupted file which makes Excel 64bit crash silently and without any message.
I even tried adding a 5 seconds wait command after the save command. Doesn't help, Excel leaves an in some way corrupt file. But in fact it is not corrupt -- open it skipping the macros, open the VBA editor, add/remove what you like to, save within the VBA editor -- and you can use it again.

But, if you like to try it your own, just pass me a pm address, and I will send you this (pre-alpha) file, it's about 220KB. There is no secret content in it.

I think my workaround will be lending them a notebook with Excel 32 bit, it seems to be the one and only possibility to let the users perform the needed usability and "find all 386 mistakes"-tests

Thanks again for your as kind as fast reaction. I'm on my way to become an Excelatic, which is quite worse than being a lunatic...

I let my file be checked by some analyze tools - no issues were found. And, sorry to say, none of your really valuable hints helped.

Solution of the problem (an internal Excel problem, indeed) was:
1) in Excel's VBA Editor, I pushed whole multipage content to the clipboard, cleared the multipage, copied all contents back to it
2) exported all modules and userforms
3) saved the file as .xlsx without any makro
4) deleted the original file
5) imported everything back into the xlsx, saved it as xlsm

... and now it runs hazzle free with excel 64bit, and I can go on developing (and amending, as I am a pretty lousy & chaotic programmer) it further.

Many thanks to all of you, in special audeser!