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

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.

--
Regards,
Sébastien
<http://www.ondemandanalysis.com
<http://www.ready-reports.com


"Ken Warthen" wrote:

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

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

"sebastienm" wrote:

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.

--
Regards,
Sébastien
<http://www.ondemandanalysis.com
<http://www.ready-reports.com


"Ken Warthen" wrote:

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

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 a
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


"Ken Warthen" wrote in message
...
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

"sebastienm" wrote:

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.

--
Regards,
Sébastien
<http://www.ondemandanalysis.com
<http://www.ready-reports.com


"Ken Warthen" wrote:

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

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

--
Regards,
Sébastien
<http://www.ondemandanalysis.com
<http://www.ready-reports.com


"Ken Warthen" wrote:

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

"sebastienm" wrote:

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.

--
Regards,
Sébastien
<http://www.ondemandanalysis.com
<http://www.ready-reports.com


"Ken Warthen" wrote:

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

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

--
Regards,
Sébastien
<http://www.ondemandanalysis.com
<http://www.ready-reports.com


"Ken Warthen" wrote:

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

"sebastienm" wrote:

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.

--
Regards,
Sébastien
<http://www.ondemandanalysis.com
<http://www.ready-reports.com


"Ken Warthen" wrote:

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