Maximum number of controls on a user form

[Ken Warthen]

Can anyone tell me if there is a maximum number of controls that Excel can
handle on a user form? I have a user form with a multi-page control on which
there are many textfields, labels, and command buttons. The form recently
stopped being visible when run and crashes Excel. I ran into a similar
situation years ago with MS Access, so I wouldn't be surprised if that might
be causing my problem. I've also gotten error messages stating that my
procedures are too long, so I had to break them up into smaller subroutines.
Any help is greatly appreciated.

Ken Warthen
(e-mail address removed)

 

[sebastienm]

Hi
How many controls do you have? It should just be limited by memory if i
remember correctly.

Also, a code module is limited to 64K, and around/above that limit, I have
seen strange behavior occuring. The following addin for the vba Editor has a
toolbar showing the size of the active code module:
<http://www.bmsltd.ie/VBETools/Default.htm>

You could push code to general modules. You could also create a Class for
each tab of the multipage, then handle the code there (declare a class
variable for each control and handle the behavior in the class). With 1
Class module per Tab, that would cut down the lines-of-code/module.

 

[Ken Warthen]

I have 935 controls on my userform. You think that's too many? I suppose I
could use separate forms, but I liked the idea of using a multipage control
on a single form. I guess it's more important for the application to work
than have a pleasing user interface that doesn't work.

Ken

 

[RB Smissaert]

I have 935 controls on my userform

I don't think that should be a problem, although I don't have a form with
that many.
Have a form with over 500 controls and no problem there.
I think the 2 most important things to keep your project healthy are:
Firstly, have Option Explicit at the top of every module and make sure that
the project can always compile.
Secondly, with large projects like this, after every save run a code
cleaner.
This is a free one:
http://www.appspro.com/Utilities/CodeCleaner.htm
I use the commercial Excel Workbook Rebuilder from Andrew Baker:
http://www.vbusers.com/commercial/commercial.asp
and I think that is better and worth the modest fee.

Module size below 64K is often mentioned, but I have seen no problems if you
go a bit over that.
At one stage I thought that the number of modules could be a problem (more
than 100), but this doesn't matter.

RBS

 

[sebastienm]

That's a lot.
Does each Page of the Multipage have similar controls?

 

[sebastienm]

That's a lot.
How many pages do you have in the Multipage? And does all pages have more or
less similar controls?