workbook close trigger unloading and termination of user forms.

hi I have a excel VBA Macro program which works fine in Excel 2010 or older version, but it has a problem in excel 365. in the following code, "savefile.close" works fine in excel 2010 by closing a file namely"savefile" , but in excel 365, it would close the file, and also unload all the user forms and trigger the "UserForm_Terminate()". all the user forms are not stored in this "savefile". Unloading user forms would erase and zero all the data in the textboxes of user forms of which I want to keep the values unchanged.

I tried to run in excel 2010 several times today, and pretty sure this "close" command only close the workbook (savefile) and did not trigger unloading or termination of the user forms.

Can anyone shed some light into this area? many thanks for your help.

the following is the code:

Private Sub CommandButton1_Click()
Application.DisplayAlerts = False
SaveFile.Close
Application.DisplayAlerts = True
End Sub

Hi there,

Here's a knee-jerk and completely untested suggestion which should disable any events associated with closing the "SaveFile" workbook:

Please Login or Register  to view this content.

Hope this helps.

Regards,

Greg M

hi Greg M, thank you for your quick reply and suggestion. but I am afraid that solution would not work for me, and maybe because I did not make myself clear in the first post.

I want the command of "SaveFile.Close" to always run with no exceptions, and there is no situation where I need to set "false" for EnableEvents. To be clearer, my observation is this code "SaveFile.Close" runs differently in Excel 2010 and Excel 365, as detailed below:

1. in excel 2010, "SaveFile.Close" simply close out that workbook namely "savefile", no more no less, which works fine for me in years for 2010 and older versions like excel 2003 or even older.

2. In excel 365, "SaveFile.Close" close out the file, does the same as in excel 2010, but also continue to run further and mysteriously trigger other steps:

a. unload all user forms. (all these user forms are stored in another master file of the macro program, not in the "savefile", so by right, closing out "savefile" shall not unload userforms. )

b. because the program unloaded all the userforms, the program will run Initialize Event of the UserForms when program redo "userform.show" (a needed step).

c. in the initialize event of userform, there is a coding to zero all textboxes and checkboxes, as needed for the program usage, but such a "zeroing" function must run only at the first time. In other works, for the 2nd and subsequent times of "userform.show", all the values in textboxes and checkboxes shall be maintained.

so with my limited knowledge of VBA coding, I think excel 365 runs differently under the command of "workbook. close" comparing to excel 2010, and I wonder if anyone came cross a similar issue and had any solutions. Maybe a substitute code/ command in excel 365 does the closing workbook function only without mistriggering anything else, just like what excel 2010 does.

Any help or hints would be highly appreciated.

Regards
Ben

please see above.