Application hanging for apparently no good reason

[zagrijs]

I built a database application in Excel with a form.

Since about a week ago, without having changed any code, the application let Excel hang (Error message: "Excel has stopped working") immediately when I attempt to load the form.

I have uninstalled an reinstalled Excel - no luck. I have tried the application on my wife's laptop - no luck.

I have commented out all coding. Then it loads - obviously. The moment I uncomment perfectly correct code in Userform_Initiliaze, the error occurs again.

The funniest part is, if I uncomment any line in Form_Initialize, the form would load - until I save, close and reload the application; then it is the same error again.

I'm desparate. I have to present and distribute the application tomorrow and I simply don't know what is wrong and how to correct it.

The application is to complex to post the code here.

Please help

Zagrijs Venter

[zagrijs]

I have managed to make some progress. It would seem I have made a mistake pertaining to he loading of the form and attempted to show without loading. I have put a "Load Form" in the apps Auto_Open and "Unload Form" in the workbook's Workbook_BeforeClose event. It works as long as I don't save the file under a new name.

The moment I save it under a new name and attempt to open the file it requests confirmation that macros should be enabled; when I click to enabled the macros Excel hangs.

A security issue? How do I solve it?

[Aussiebear]

Post your code here and someone will no doubt have a run through it for you

[zagrijs]

Hi Aussiebear,

Thanks for your reply.

The code is ENORMOUS and it would be totally unfair to anyone to post it all here.

I understand that participants are assisting in their private time and I do not want to abuse people's time, espcially as I'm convinced the problem is not in the code, but in other issues like security or something similar.

I was hoping that somebody who reads it would from experience have an idea where the problem is and direct me in the right direction.

Kind regards

[snb]

You only need to post the Userform_Initialize code.

I hope you are familiar with the userform.show method

[Aussiebear]

I think the issue will be in the code given that you indicated
1.

Since about a week ago, without having changed any code, the application let Excel hang (Error message: "Excel has stopped working") immediately when I attempt to load the form.

And 2.

The funniest part is, if I uncomment any line in Form_Initialize, the form would load - until I save, close and reload the application; then it is the same error again.

As Snb has indicated the issue appears to be in the code you have constructed relating to the form. If you can give us a look at this section we might be able to assist you.

[snb]

A possible reason could be the use of 'rowsource' for a listbox or combobox.
If something is wrong with the defined 'rowsource' the list/combobox can't load any content.

[zagrijs]

[zagrijs]

I tried to post the code yesterday, but must have made a mistake as an empty message was posted.

Thanks for snb's remark about the rowsource property. It put me unto the trail of what is wrong.

I used the "list" property for all my comboboxes.

combobox.list = array("something1","something2") works fine.

The offending code seems to be my use of "list" with a range, eg
combobox.list = workheets("Sheet2").range("a1:a5).value. After commenting all those lines out, the problem is "solved".

I saw examples of how to use rowsource. It would seem that it only accepts the "rowsource" from the active sheet. I have been unable to get it to work using data from another sheet than the active sheet.

Is it possible to specify another sheet for the rowsource without changing the active sheet?

[snb]

combobox.list = workheets("Sheet2").range("a1:a5).value

My suggestion: never use 'rowsource' to populate a listbox/combobox.
The preferred method is .List , provided you do not make typos:

combobox.list = worksheets("Sheet2").range("a1:a5").value

[zagrijs]

There were no typing errors in my code. However, I have replaced the code using "list" & a worksheet range with "list" and an array in all the instances. Because some of the lists are used more than once, I stored the elements in an array first and used the variable containing the array with "list". It works fine! The file save quicker and more elegantly.

Thanks again for the helped.