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

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?



0
Utf
3/27/2010 3:19:01 AM
excel.programming 6508 articles. 2 followers. Follow

14 Replies
1316 Views

Similar Articles

[PageSpeed] 25

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.

 
-- 
Regards,

OssieMac


"Brian" wrote:

> 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?
> 
> 
> 
0
Utf
3/27/2010 7:18:01 AM
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.


"OssieMac" wrote:

> 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.
> 
>  
> -- 
> Regards,
> 
> OssieMac
> 
> 
> "Brian" wrote:
> 
> > 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?
> > 
> > 
> > 
0
Utf
3/27/2010 11:43:01 AM
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" <Brian@discussions.microsoft.com> wrote in message 
news:3BF11020-DCC1-459E-86A4-B2AE692601C9@microsoft.com...
> 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?
>
>
> 


0
Peter
3/27/2010 12:37:44 PM
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.



"Peter T" wrote:

> 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" <Brian@discussions.microsoft.com> wrote in message 
> news:3BF11020-DCC1-459E-86A4-B2AE692601C9@microsoft.com...
> > 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?
> >
> >
> > 
> 
> 
> .
> 
0
Utf
3/27/2010 8:48:01 PM
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.

"OssieMac" wrote:

> 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.
> 
>  
> -- 
> Regards,
> 
> OssieMac
> 
> 
> "Brian" wrote:
> 
> > 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?
> > 
> > 
> > 
0
Utf
3/27/2010 9:07:01 PM
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).

"Peter T" wrote:

> 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" <Brian@discussions.microsoft.com> wrote in message 
> news:3BF11020-DCC1-459E-86A4-B2AE692601C9@microsoft.com...
> > 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?
> >
> >
> > 
> 
> 
> .
> 
0
Utf
3/27/2010 9:09:02 PM
I removed all the CES logos and it still no run. I think i broke it.

"JLatham" wrote:

> 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).
> 
> "Peter T" wrote:
> 
> > 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" <Brian@discussions.microsoft.com> wrote in message 
> > news:3BF11020-DCC1-459E-86A4-B2AE692601C9@microsoft.com...
> > > 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?
> > >
> > >
> > > 
> > 
> > 
> > .
> > 
0
Utf
3/27/2010 9:21:01 PM
"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


"JLatham" <JLatham@discussions.microsoft.com> wrote in message 
news:A7E0A024-25FE-4E62-B40C-CEA4CCD1D946@microsoft.com...
> 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).
>
> "Peter T" wrote:
>
>> 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" <Brian@discussions.microsoft.com> wrote in message
>> news:3BF11020-DCC1-459E-86A4-B2AE692601C9@microsoft.com...
>> > 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?
>> >
>> >
>> >
>>
>>
>> .
>> 


0
Peter
3/27/2010 11:33:29 PM
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

"Peter T" <peter_t@discussions> wrote in message 
news:ujF7fXgzKHA.928@TK2MSFTNGP05.phx.gbl...
> "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
>
>
> "JLatham" <JLatham@discussions.microsoft.com> wrote in message 
> news:A7E0A024-25FE-4E62-B40C-CEA4CCD1D946@microsoft.com...
>> 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).
>>
>> "Peter T" wrote:
>>
>>> 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" <Brian@discussions.microsoft.com> wrote in message
>>> news:3BF11020-DCC1-459E-86A4-B2AE692601C9@microsoft.com...
>>> > 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?
>>> >
>>> >
>>> >
>>>
>>>
>>> .
>>>
>
> 


0
Peter
3/28/2010 12:04:01 AM
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" wrote:

> "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
> 
> 
> "JLatham" <JLatham@discussions.microsoft.com> wrote in message 
> news:A7E0A024-25FE-4E62-B40C-CEA4CCD1D946@microsoft.com...
> > 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).
> >
> > "Peter T" wrote:
> >
> >> 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" <Brian@discussions.microsoft.com> wrote in message
> >> news:3BF11020-DCC1-459E-86A4-B2AE692601C9@microsoft.com...
> >> > 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?
> >> >
> >> >
> >> >
> >>
> >>
> >> .
> >> 
> 
> 
> .
> 
0
Utf
3/28/2010 12:13:01 AM
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


"Peter T" <peter_t@discussions> wrote in message 
news:%23NuGkogzKHA.1236@TK2MSFTNGP06.phx.gbl...
> 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
>
> "Peter T" <peter_t@discussions> wrote in message 
> news:ujF7fXgzKHA.928@TK2MSFTNGP05.phx.gbl...
>> "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
>>
>>
>> "JLatham" <JLatham@discussions.microsoft.com> wrote in message 
>> news:A7E0A024-25FE-4E62-B40C-CEA4CCD1D946@microsoft.com...
>>> 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).
>>>
>>> "Peter T" wrote:
>>>
>>>> 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" <Brian@discussions.microsoft.com> wrote in message
>>>> news:3BF11020-DCC1-459E-86A4-B2AE692601C9@microsoft.com...
>>>> > 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?
>>>> >
>>>> >
>>>> >
>>>>
>>>>
>>>> .
>>>>
>>
>>
>
> 


0
Peter
3/28/2010 12:28:25 AM
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" wrote:

> 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
> 
> 
> "Peter T" <peter_t@discussions> wrote in message 
> news:%23NuGkogzKHA.1236@TK2MSFTNGP06.phx.gbl...
> > 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
> >
> > "Peter T" <peter_t@discussions> wrote in message 
> > news:ujF7fXgzKHA.928@TK2MSFTNGP05.phx.gbl...
> >> "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
> >>
> >>
> >> "JLatham" <JLatham@discussions.microsoft.com> wrote in message 
> >> news:A7E0A024-25FE-4E62-B40C-CEA4CCD1D946@microsoft.com...
> >>> 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).
> >>>
> >>> "Peter T" wrote:
> >>>
> >>>> 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" <Brian@discussions.microsoft.com> wrote in message
> >>>> news:3BF11020-DCC1-459E-86A4-B2AE692601C9@microsoft.com...
> >>>> > 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?
> >>>> >
> >>>> >
> >>>> >
> >>>>
> >>>>
> >>>> .
> >>>>
> >>
> >>
> >
> > 
> 
> 
> .
> 
0
Utf
3/28/2010 3:38:01 AM
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" <JLatham@discussions.microsoft.com> wrote in message 
news:11D0E297-1272-4DB3-B3B1-1F944A9B60D3@microsoft.com...
> 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" wrote:
>
>> 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
>>
>>
>> "Peter T" <peter_t@discussions> wrote in message
>> news:%23NuGkogzKHA.1236@TK2MSFTNGP06.phx.gbl...
>> > 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
>> >
>> > "Peter T" <peter_t@discussions> wrote in message
>> > news:ujF7fXgzKHA.928@TK2MSFTNGP05.phx.gbl...
>> >> "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
>> >>
>> >>
>> >> "JLatham" <JLatham@discussions.microsoft.com> wrote in message
>> >> news:A7E0A024-25FE-4E62-B40C-CEA4CCD1D946@microsoft.com...
>> >>> 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).
>> >>>
>> >>> "Peter T" wrote:
>> >>>
>> >>>> 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" <Brian@discussions.microsoft.com> wrote in message
>> >>>> news:3BF11020-DCC1-459E-86A4-B2AE692601C9@microsoft.com...
>> >>>> > 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?
>> >>>> >
>> >>>> >
>> >>>> >
>> >>>>
>> >>>>
>> >>>> .
>> >>>>
>> >>
>> >>
>> >
>> >
>>
>>
>> .
>> 


0
Peter
3/28/2010 5:27:34 PM
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.

"Peter T" wrote:

> 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" <JLatham@discussions.microsoft.com> wrote in message 
> news:11D0E297-1272-4DB3-B3B1-1F944A9B60D3@microsoft.com...
> > 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" wrote:
> >
> >> 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
> >>
> >>
> >> "Peter T" <peter_t@discussions> wrote in message
> >> news:%23NuGkogzKHA.1236@TK2MSFTNGP06.phx.gbl...
> >> > 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
> >> >
> >> > "Peter T" <peter_t@discussions> wrote in message
> >> > news:ujF7fXgzKHA.928@TK2MSFTNGP05.phx.gbl...
> >> >> "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
> >> >>
> >> >>
> >> >> "JLatham" <JLatham@discussions.microsoft.com> wrote in message
> >> >> news:A7E0A024-25FE-4E62-B40C-CEA4CCD1D946@microsoft.com...
> >> >>> 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).
> >> >>>
> >> >>> "Peter T" wrote:
> >> >>>
> >> >>>> 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" <Brian@discussions.microsoft.com> wrote in message
> >> >>>> news:3BF11020-DCC1-459E-86A4-B2AE692601C9@microsoft.com...
> >> >>>> > 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?
> >> >>>> >
> >> >>>> >
> >> >>>> >
> >> >>>>
> >> >>>>
> >> >>>> .
> >> >>>>
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >> .
> >> 
> 
> 
> .
> 
0
Utf
3/29/2010 12:56:01 AM
Reply:

Similar Artilces:

Forms and Reports Don't Retain Size on Opening
I recently did some work on a database and reissued a new front end. On my client's system (and on another computer of mine), forms and reports open in postage stamp size (or close to it). What could be the cause of this and how do I correct it? -- susan Check the pop-up property and make sure it is set to no. Or, if they are, you can expand them or set a new size, save them, and then they should open correctly. -- Milton Purdy ACCESS State of Arkansas "Susan L" wrote: > I recently did some work on a database and reissued a new front end. On my ...

File Size Limit??
Hi, Can anyone tell me if there is a limit to the file size of an Excel 2000 document. We've got a guy here who has a 63Mb spreadsheet, and keeps wondering why he's getting "Out of memory" messages every time he opens it. I've put an extra 512Mb in his PC, but he's still getting lock-ups. He's convinced that there are no limits to the size of the file, but I'm pretty certain that there is... Anyone help? Thanks, Jon. The limit is memory. -- Don Guillett SalesAid Software donaldb@281.com "Jon" <anonymous@discussions.microsoft.com> ...

can i creat post it type notes on desktop using outlook
same as above No, close Outlook and the Notes will close as well. Use the Sticky Notes feature of Windows Vista or Windows 7 instead or use one of the many 3rd party tools which can do this as well. -- Robert Sparnaaij [MVP-Outlook] Coauthor, Configuring Microsoft Outlook 2003 http://www.howto-outlook.com/ Outlook FAQ, HowTo, Downloads, Add-Ins and more http://www.msoutlook.info/ Real World Questions, Real World Answers ----- "shamkant" <shamkant@discussions.microsoft.com> wrote in message news:22C776E7-61A8-46DC-9BD7-C39EFABE7E78@microsoft.com... >...

how can I get the total hrs of a delay when it is greater than 24
I work for an airline and sometimes I need to know the total hours of a delay, and when the delay is greater than 24 hours. In my report I hav something like this: arrival time in colum A departure time colum B result in colum C 10/23/09 21:00 10/24/09 23:00 2:00 and really the delay was of 26 hours. How can I obtain this? I already set up my Tools-Options-Canculation to 1904 date system Format Custom as [h]:mm No need to use 1904 date system -- Kind regards, Niek Otten Microsoft MVP - Excel "El Ixmahana" <ElIxmahana...

Dollar text
Hi I do my proposal in Excel and I'm wondering if I can format a cell to convert dollar amounts $100.00 to One Hundred Dollars and 00/100 like when I write a Check? You might look at this article: http://support.microsoft.com/default.aspx?scid=kb;en-us;213360 -- Jim "Plumb34" <Plumb34@discussions.microsoft.com> wrote in message news:DE4A7F72-B1BE-4AA1-95BE-630B662951A7@microsoft.com... | Hi | I do my proposal in Excel and I'm wondering if I can format a cell to | convert dollar amounts $100.00 to One Hundred Dollars and 00/100 like when I | write a Check? | |...

set focus on combo box
I have a simple form displaying records with a few simple record navigation buttons. When a user clicks 'add record' i have set it so the focus is set to a certain combo box where the user uses the pull down menu and selects whatever ID they need. Is there any way however to automatically open the pull down menu from the combo box as the focus is set to it so when a user clicks add record the focus is upon the combo box and the menu automatically opens. Thanks nathan Ciao Nathan wrote: > I have a simple form displaying records with a few simple record > navigation buttons...

User Access Error!
It works yesterday well with the CRM, but today the system shows the following error: The logged on user is either not a Microsoft CRM user, is disabled in the system, has not been assigned any security roles, or has not been assigned a license. Error details: Details on this error have not been provided by the platform. It already verifies and everything seems to be well but I cannot enter nor like administrator. That I can do? Thanks! Can you start up the deployment manager? In that case, you should try to add a new user. This user should be member of the 'System administrator...

access2007 option group dotted line box around text
I have an access 2007 application with a form with a unbound option box with three option buttons and labels. When I select them sometimes the dotted box shows around 2 of the labels. The options have values 1, 2 and 3. Not sure why it is not just showing the dotted line box around the selected option? Any tips to fix it? Mark see my previous post for suggestions and resolution. I accidentally thought a deleted this one and it didn't post. Mark "Mark Andrews" <mandrewsNOSPAM@rptsoftware.com> wrote in message news:Okuv5ohaKHA.5608@TK2MSFTNGP05....

Limits
I am doing a fairly easy programme on excel. It is a catchment runoff, and evaporation calculator. But i would like to place a limit on a cell, and have that exess go into another column, is this possible? EG 75 rainfall, soil can hold only 10mm more. SO 10mm INFILTRATES, ie that cell should have a limit of the max soil moisutre, the other 65mm RUNOFF's and thats a different column. ANYBODY PLEASEEEEEEEEEE!!!! lol You need a Workbook_Change event macro for that. The code you would write in that macro should first check that the change occurred in a cell within the range you want. ...

Text box format
Hello, today I ask my first question in this forum *flourish* Can I "paste" the format of a text box to another in a similiar way as I can do with text formats ? I only found out, that I can set a standard for new text boxes. But I need more. *g* -- Andrea Dauwen-Ritter Marketing Support Germany How about you tell us which version of Publisher you are using!!! A baptism in fire!!! "°°MS-Publisher°°" wrote: > How about you tell us which version of Publisher you are using!!! > > A baptism in fire!!! *crawling out of the smoke with brai...

create automessages if user leaves the company
Hi I have the following problem. There is a user which temporary leaves the company and come back to an unspecified date. All mails which sent to this user are forwarded to an other user who take the taks in absence of him. In behalf of security the user is disabled, that means a rule or "out of office assistent" doesn't work to inform customers that this user come back on an unspecified date and the other take his tasks. Is there a possipility to configure an automatic out of office and keep the possipility to forward the mails to the other user in exchange 2k3 ? Thanks...

Combo Box Values
Access 2007 How do I display a first and last name on the form from a combo box? I would like the address, city, and home phone number to fill in the appropriate fields on the form when selecting the name. You refer to the combo box's Column collection in its AfterUpdate event. Let's assume that the first name is in the second column, the last name is in the third column, the address is in the fourth column, the city is in the fifth column and the phone number is in the sixth column. You'd put their values into text boxes using code like: Private Sub cboSelecti...

Multiple Logon Sessions
Hi, one of our users is consistently unable to connect to the exchange server on our Network (Exchange 2003). In the status bar on the bottom right, the message is "disocnnected." Trying to reconnect does not work. He is using Outlook 2003. What seems to be happening is that there are too many logon sessions open at the same time, and thus he is not able to connect. When we restart the Information Store service on the Exchange Server, he is able to connect again. Sometimes this stays for a while, but today all sessions came back within a matter of an hour. Does anyone know wha...

Problem using Match on columns of numbers with leading zeroes formatted as text ???
In my test run, I was using a column of first names in workbook A to check and flag their presence in workbook B, The code given to me by Marcus here worked fine. No problem at all. .. When I switched to the real problem by replacing my columns of data with numbers with leading zeroes, the columns being already formatted as text, the program did not find Matches. .. In both workbooks columns of data, cells have an automatic Excel comment that says : "The number in this cell is formatted as text or preceded by an apostrophe". .. What am I missing here ? Is there a spe...

2008 R2 dpm 2007 setup: SIS-Limited not found?
I'm trying to run the command line to install the SIS component via ocsetup.exe SIS-Limited /quiet /norestart but it tells me it cant find this SIS-Limited component (on 2008 r2 x64).. Any ideas what i need to do to get past this? Thanks It turns out you just need to add the file services role in r2, dont need the manual command.. "markm75g" wrote: > I'm trying to run the command line to install the SIS component via > ocsetup.exe SIS-Limited /quiet /norestart > > but it tells me it cant find this SIS-Limited component (on 2008 r2 x64).. ...

Help! IF function is too limited
Hi! I've created a drop-down list of cities in a cell, under which there are two more cells to be filled out with the address and zip codes corresponding to each of the cities. How can I do this, knowing that the original list is a three-column list made of city-address-zip? -- Ringo ------------------------------------------------------------------------ Ringo's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27502 View this thread: http://www.excelforum.com/showthread.php?threadid=470178 Use VLOOKUP into the original; list using the DV value =VLOOKUP(B...

Q: Rules limit
There is a limit for Exchange Rules on a 32K-size (KB147298) Whether there is a way to change this limit for single users? For me already three users have addressed with such requests :- Thanks Alexander Kenin This is not a configurable limit. It's limited by the size of one RPC packet. "Kenin Alexander" <anonymous@discussions.microsoft.com> wrote in message news:80981636-20EB-4C64-B514-F9BCA635AFE7@microsoft.com... > There is a limit for Exchange Rules on a 32K-size (KB147298). > Whether there is a way to change this limit for single users? > For me already thre...

Control box question
I have a form that opens when you run a query. (example: employee last name) Then when I want to look up another name I want to do it from the form instead of closing the form. So I added a control box to the form to run the query again. The problem is that it opens up a new form, so if I do multiple searches I will have a lot of open forms. So the the question is: Can I set up the control box to run the query and open it in the same form or at least close the last form when it opens a new one? If so how is this done? Thanks Eric How about placing an unbound combo box at t...

Hide a text box
On sheet 3 of my workbook I have created a button (button 62) and a text box (textbox 63). What I want to do is use the button to toggle the text box on and off. By that I want to be able to show the box or hide the box. I have tried to copy code from the MS Visual Basic Help but it did not work, basically because I have no idea what I am doing. Can someone lease help? Orf Bartrop Under my Excel - 2000 I don't think you can hide a text box But I hope Im wrong it would be a good idea Steve On Wed, 09 Aug 2006 05:45:55 +0100, Orf Bartrop <orf@southcom.com.au> = wrote: > On...

Stop Excel form objects being printed
I tried to hiding the Forms objects being printed but would like to have the content printed. Hi Jong, If you are trying to print your form without the objects then Right click the object and go to Properties/Print Object change from true to false. You'll have to do this to every object that you do not want to show up in your print. Hope this helps, Larry -- keithl816 ------------------------------------------------------------------------ keithl816's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=21287 View this thread: http://www.excelforum.com/sho...

Two computers in office used by same user...problems
Seems that my boss insists on using two separate offices and keeps both signed in with multiple items in Outlook all day long. The NK2 file seems to change between the two of them and many times he doesn't have the same contact information simultaneously on both machines. He is very tired of this and so am I??? I would be willing to purchase software to take care of this problem if necessary...I just need to know where to start to keep information identical between the logins on 2 different computers...thanks! Dear Jeannie, i am not sure if this is going to help you, but i think you n...

Copy and move check box (check boxes) with new cell link?
Hi, I have created a document with text, check boxes and IF formulas dependant on the TRUE or FALSE values for each of the check boxes. This was done over time and it is fairly extensive at this point and I would like to duplicate that document on the same sheet about 6 to 8 times below the original. When I copy the document and paste it below the original (text, formulas and check boxes), the references (cell link) for all of the check boxes are linked to the original document (clicking a check box in the copied document add a check mark to both the copied and original check bo...

Limiting Internet Email for Users
I want to stop users from sending Internet email from my Exchange 2000 server. I use it for a company wide mail server. Can anyone help me stop Internet Email. This might help: http://www.msexchange.org/tutorials/MF009.html -- Mark Fugatt Exchange MVP http://www.exchangetrainer.com http://www.msexchange.org "Bob" <anonymous@discussions.microsoft.com> wrote in message news:14B7AF2C-73E3-4601-84F9-3F7B95043B72@microsoft.com... > I want to stop users from sending Internet email from my Exchange 2000 server. I use it for a company wide mail server. Can anyone help me stop...

How can I download a PDF file to Excel
I want to add my lease agreements from a pdf file to Excel data files? How can this be done. Does anyone know. Please Help Thank you In Acrobat, have you tried selecting, cooying, and pasting into Excel? On Thu, 13 Jan 2005 14:13:05 -0800, ssp <ssp@discussions.microsoft.com> wrote: >I want to add my lease agreements from a pdf file to Excel data files? How >can this be done. Does anyone know. Please Help > >Thank you > Thank you - I will try to down load a lease agreement in that manner againb to see if it work - thank you "Myrna Larson" wrote: ...

Text Form Fields
Not sure if I have posted this in the right area, but here goes. I am more familiar with excel so working with forms in word is new to me. I have been tasked with developing a number of forms at work in Word 2003 for posting on our website. A guy who left, and is now not contactable, developed a whole range of forms before he left. He made sure the forms could be filled in online by inserting text editable fields for client use (both the 'greyed out' section and dotted lines can be seen when viewing and completing online and the dotted lines auto-delete as text is inserte...