Userform triggers Excel Crash

[bruinenat]

Hi there

I have a problem that is driving me crazy. I have this worbook with one click button. I you click on it, a userform with multiple controls appears.

Sometimes however - this seems to happen randomly - my Excel application crashes. I have looked all over the internet and thought that the problem was solved; now it's back again...

I call the userform with this macro:
Sub Toon()
'2 Crash oplossing 1: application.vbe.mainwindow tonen en hiden
'1) Activate de userform via: ThisWorkbook.VBProject.VBComponents("Userform1").Activate


Retry:
On Error GoTo AltOploss
'----> '1) Begin oplossing 1
ThisWorkbook.VBProject.VBComponents("Editiescherm").Activate
'----> '1) Einde oplossing 1
On Error Resume Next
Editiescherm.Show
Exit Sub
AltOploss:
'----> '2) Begin oplossing 2
On Error GoTo Retry
Application.ScreenUpdating = False
With Application.VBE.MainWindow
.Visible = True
.Visible = False
End With
Application.ScreenUpdating = True
Editiescherm.Show
On Error Resume Next
'----> '2) Einde oplossing 2

'zie ook
'http://www.ozgrid.com/forum/showthread.php?t=38693
End Sub

I'll try to upload the complete file here (my first post, I hope it works out right). Once again, it occurs not every time, so it's really driving me crazy...

I had to upload it to here as it's zipped file size is > 1 MB:

http://www.mediafire.com/?sharekey=36e4a1f0a101cc54e62ea590dc5e5dbbe04e75f6e8ebb871

thank for your thoughts!
Bruno

[Jan Karel Pieterse]

Have you tried running Codecleaner against the file (dl from www.appspro.com (http://www.appspro.com))

[bruinenat]

I would like to; but I don't have administrator rights to install it :-(
I guess I'll have to try this at home then.

[Bob Phillips]

Post it here, we will Code Clean it and post it back.

[Paul_Hossler]

I've had erratic results when a form (usually) got too large. You can read more about the module size issue here. I use 2007, and I think the problem is still there. I had to split a lot of Userform code out to standard modules.



As far as I know, there is no documented limit on the size of a module. It seems the Excel development community has settled on 64KB as the size where strange problems start happening. So, as a rule of thumb, if you have a large module, it may be advantageous to split it up into smaller ones.
To check the size of a module, export it to a text file and check that file’s size in Windows.


http://www.dailydoseofexcel.com/archives/2004/10/13/module-size-limits/


I ran CodeCleaner on the file you posted, and looked at the exported files.

Editiescherm.frx and Editiescherm.frm are 278KB and 67KB, so it might be worth trying to break that one into several modules.

Paul

[Bob Phillips]

Paul, was the form modules size > 64k?

[Paul_Hossler]

There was only the one form (Editiescherm) and when I exported the project, the file sizes were Editiescherm.frx = 278K and Editiescherm.frm = 67KB

The userfrom has a very large number of controls with a lot of code behind them.

HTH

Paul