Maximum number of controls on a Userform

[Martin Beir]

I am using Excel X for Mac on a G3 system 10.

I have placed 900 controls on a Userform.
No, they do not all display at once; display depends on which pages of
several Multipage controls are selected.

I am not able to add further controls to the Userform. Is the limit
determined by memory available on my computer or is this a rigid parameter
of Userforms.

If I were to upgrade to a later version of Excel, would I be able to exceed
900.

Regards,

Martin

 

[Edwin Tam]

This is a very interesting "stress test" of the Mac office userforms. The
maximum number of controls I placed and all with macros tied to each of them,
and the form still remained stable was about 750.

The issue here is not whether there are limitations. With 900 controls on
the same form, I think your work will be like drawing a picture on a piece of
rice. The present Mac Office VBA is not stable enough to handle that
complexity. Your VBA Project will crash at any time on any user's machine,
randomly. Even if you're sure that you programmed perfectly, your application
will still crash for no reason.

My suggest is to re-design your userform and overall work flow of your VBA
application, instead of finding out ways to break through the limit.
Usability and stability should always be of top priority.

Regards,
Edwin Tam
(e-mail address removed)
http://www.vonixx.com

 

[Martin Beir]

I use the application program for preparing business quotations. If I buy a
new machine and am able to offer additional processes then I need to extend
my program. I add a page to a multipage, add appropriate controls to the new
page, write code and thus my program is suitable extended.

I have been using this program for sometime and hove not experienced any
problems with stability. Although, there are 900 controls on the Userform, I
endeavour to write code which is simple and concise. Many of the controls
are labels and do not have macros tied to them.

In design mode, I am not able to add the 901st control. I select the
appropriate control in the toolbox and position the mouse cusor on the
Userform but no control is placed.

Since my first posting to this newsgroup about this matter, I have
transferred the program to another computer with more available memory but
to no avail. I think that 900 must be a set limit.

I can place additional controls on the Userform through the VBA with "Add"
method but obviously this is less simple and requires additional code.

Regards,

Martin

 

[JE McGimpsey]

In design mode, I am not able to add the 901st control. I select the
appropriate control in the toolbox and position the mouse cusor on the
Userform but no control is placed.

Since my first posting to this newsgroup about this matter, I have
transferred the program to another computer with more available memory but
to no avail. I think that 900 must be a set limit.

I can place additional controls on the Userform through the VBA with "Add"
method but obviously this is less simple and requires additional code.

I can't speak to any limits, and I'm certainly surprised that the
controls can be added via VBA but not via the VBE - perhaps a 'Softie
will see this and post here...

Frankly, I can't imagine using 900 controls on a userform. If it works
for you, that's great, of course, but I would find it a maintenance
nightmare.

One technique I've used is to load multipages in real-time. I typically
use a (very) hidden sheet with labels and lists. When a page loads, I
load the labels, dropdowns, button captions, etc. Controls are re-used,
and provide a consistent interface.

 

[Jim Gordon MVP]

Hi Martin,

I did a Google search. From what I could find the highest maximum number
of controls on a userform before you posted your message was 578 for
Microsoft Access (Windows). It would seem that Mac Office has a much
higher capacity than Windows office. There must be a prize of some sort
for you breaking the record.

-Jim