What is the Limit of Text Boxes & Labels a User Form can have?

[Brian]

What is the Max File Size a User Form can be?

I have Windows 7 with Office 2007.

Does it have like a 64K limit?

The file size as far as kb. My entire Program is 1,203 Kb.

When I ran it I got a "compile Error out of memory", so I exported my User
Form. Then I looked at the file size of just the User Form in a blank
Workbook
34 KB = .frm
219 KB = frx

My user Form is a Multi Page with 4 Pages on it. There are alot of text
boxes and labels on the 4 sheets. Is there a limit to the number of text
boxes & labels you can have in a user Form?

I have 12 GB of Memory, so how can I run out?

 

[OssieMac]

Hi Brian,

I will be interested if anyone has further comments on this.

It is more likely that you are calling code in a standard module from the
code in the form module and you have an error in the called code.

I have experienced strange errors returned as well as code just not running
when calling code from a forms module. If my memory serves me correctly I
have mentioned this to you one time before although the problem you were
having was under different circumstances.

Any code that you call in a standard module should be able to run on its own
from the standard module for testing purposes with only a few variables used
to provide dummy test data. The code used to set the dummy data can be just
commented out and kept in case you want it again for further modifications to
your project. Running the code in the standard module this way usually
returns the errors at the correct location so you can fix it. Also being able
to run it as a stand alone sub is an indication that you have reasonably well
structured code.

 

[Brian]

My User Form was running fine. I added a 4th page to the User Form with alot
of Labels & Text Boxes on it. There was no Code for the 4th Page yet. I tried
to run it, just to see what it looked like. I got a "Compile Errror out of
Memory".

At that point I exported the User Form and then Imported it into a blank
Workbook. I removed all the Code and tried to run it and I still got "Compile
Errror out of Memory".

Now if you remove pages 1-3, it runs fine. There has to be some kind of a
limit that I crossed when I had all 4 pages in the User Form. Thats why I was
asking if anyone Knew how many Text Boxes and Labels you can have in a User
Form.

 

[Peter T]

The 64K was never a limit set in stone, in any case you've only got 34k.
I've tested forms with 1000+ controls without problems but 219K in the frx
does seem large (but depends more on what it is, eg definition of a picture
would make it large but merely basic control properties doesn't take much).
When you say "a lot" of controls what are you actually talking about.

Regards,
Peter T

 

[Brian]

There are Prob less than 1000 (Text Boxes, Combo Boxes, Labels, etc...).

I removed all the code and put it in a blank workbook and tried to run it
from the VBA window i get the same Error.

 

[JLatham]

OssieMac,
I've got a copy of the userform that Brian is speaking of. Right now there
is no code associated with it at all. Attempting to open it from the VB
Editor (or from a simple UserForm1.Show command in a one-line Sub) causes an
Out Of Memory in Excel 2003 and/or 2007 under both Vista and Windows 7.

I took the form and deleted the 4th page and it opens with no problem. I
then duplicated the userform (with another name), loaded it into the file,
deleted the 1st 3 pages from it and you can open both UserForm1 (3 pages) and
UserForm2 (the 4th page) without any error.

So I'm kind of down to thinking this is some type of system memory wall he's
run into. The form is LARGE: 785.25 x 1316.25w; the 1st 3 pages have a total
of 1,049 controls and that 4th page has another 587 controls on it for a
total of 1,636 when they are in a single UserForm.

Any insight anyone has to limits of pretty much any type associated with
UserForms would be appreciated. I've searched and searched and really
haven't found any hard numbers about it. But I may not be looking in the
right places.

Peter T mentioned the size of the frx file; the userform does have a graphic
of the corporate logo on each of the 4 pages, so that's probably adding to
the file size.

 

[JLatham]

See my post in response to OssieMac above - total of 1,636 controls of all
types on the 4 pages (1049 on the 1st 3, another 587 on the 4th), plus there
is a small graphic of a company logo on each of those 4 pages in the
MultiPage control (could explain the frx file size).

 

[Brian]

I removed all the CES logos and it still no run. I think i broke it.

 

[Peter T]

"Total of 1,636 controls on the multipage"
That is a lot, and probably strong candidate for a different design approach
even without the memory error.

In a very light test it looks like there's an absolute limit of not much
more than 1200 controls on a multipage. Try this -

Add a multipage with 3 pages to a new userform. Size the Multipage to about
700x400 and the form to suit. Leave a small gap between the caption and the
top of the multipage. Also ensure Trust Access to VBProject is enabled in
security settings.

Sub design1()
Dim i&, n&, r&, c&, k&, t&
Dim p As Page
Dim u As UserForm
Dim tbx As MSForms.TextBox

Set u = ThisWorkbook.VBProject.VBComponents("UserForm1").Designer
n = u.Controls.Count - 1
For i = n To 1 Step -1
u.Controls.Remove u.Controls(i).Name
Next

For Each p In u.Controls("MultiPage1").Pages

For r = 0 To 20 - 1
For c = 0 To 20 - 1
t = t + 1
Set tbx = p.Controls.add("Forms.TextBox.1")
With tbx
.Left = c * 33
.Top = r * 15
.Width = 33
.Height = 15
.Text = r + 1 & ":" & c + 1
End With
Next
Next
Next

u.Caption = "Controls: " & t
End Sub

For me 3x20x20 = 1200 was OK, but I couldn't increase a 20 to 21 and go on
to load the form.

Regards,
Peter T

 

[Peter T]

I'm hitting a limit of 1207 controls on a form in 2003 (I doubt XL version
is relevant)

This time start with an empty form, following assumes it's called Userform2

Sub design2()
Dim i&, n&, r&, c&, k&, t&
Dim p As Page
Dim u As UserForm
Dim lab As MSForms.Label

Set u = ThisWorkbook.VBProject.VBComponents("UserForm2").Designer
n = u.Controls.Count - 1
For i = n To 1 Step -1
u.Controls.Remove u.Controls(i).Name
Next

For r = 0 To 80 - 1
For c = 0 To 20 - 1
t = t + 1
Set lab = u.Controls.add("Forms.Label.1")
With lab
.Left = c * 33
.Top = r * 15
.Width = 33
.Height = 15
.Caption = r + 1 & ":" & c + 1
End With
If t = 1207 Then ' <<< change
GoTo enough
End If
Next
Next

enough:
u.Caption = "Controls: " & t
End Sub

Regards,
Peter T

 

[JLatham]

Peter,
Thanks very much for that behind the scenes work and analysis. I'll confer
with Brian to determine a new approach that's acceptable to him.

What I have in mind is to simply split the 4 pages into 4 userforms, with
one acting as a main and the other 3 set up so that they are opened
automatically when the main opens, and prevented from closing except when the
main is closed. All that would be needed would be buttons on each to allow
the selection of one of the others when it is needed to be used.

Seems kind of odd that MSFT doesn't seem to have any published limits
regarding size, content, or number of controls on UserForms.

 

[Peter T]

Actually I can load 1208. In the desgin2 proc (but not design1) change

For i = n To 1 Step -1

to
For i = n To 0 Step -1

Previously I deliberately left the multipage on the form, but with this best
to start with no controls

maybe also change

u.Caption = "Controls: " & t

to
u.Caption = "Controls: " & u.Controls.Count

Peter T

 

[JLatham]

Well, that number, be it 1207 or 1208 is certainly in line with what we're
observing: 3 pages = 1049 controls, but the 4th page pushes the count to over
1600 (and definitely over your 1207/8 limit).

Brian has done a brilliant job of putting his workbook together and I'm just
helping with the fine points, and he even sent me an email suggesting what I
said I'd ask him about earlier: splitting the form up into several forms and
just controlling how those are used during the use of the package. The one
main 3-page form writes to 3 other XL files, and it looks like the 4th page,
and a planned 5th page, are 'utility' type forms that wouldn't even need to
be available to get the work done by the main 3-page form completed.
Splitting into separate forms would make sense in keeping the functions of
the whole application separated rather than tossing everything into a single
form.

 

[Peter T]

Curiously it seems OK to add additional controls beyond the 1208 at
run-time. I added 600 controls to pages 0 & 1 by slightly adapting the first
example I posted. Then during run I added another 600 to page(2). Not sure
I'd actually recommend that though!

Regards,
Peter T

 

[JLatham]

An interesting side issue. Might point to the graphic engine having problems
with 1200+ controls initially? I think trying to add more controls at runtime
is probably a high-risk solution: somewhere you might continue to expand the
userform until you actually hit some kind of system RAM wall and end up
having to redesign the whole thing anyhow. Better to take a less-risky
design strategy right now, before it becomes a distributed application.

Many thanks for all of your help and research on this.