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
2657 Views

Similar Articles

[PageSpeed] 21

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:

How can I drop a flyer from Publisher into PowerPoint as a slide?
I have created a flyer in Publisher but I now need to drop it into PowerPoint as a slide, is this possible? No, not really. You could open up both instances, Powerpoint and Publisher, and then drag and drop/copy and paste from the Publisher window to the Powerpoint window, the reformat as needed. -- Brian Kvalheim Microsoft Publisher MVP http://www.publishermvps.com This posting is provided "AS IS" with no warranties, and confers no rights. "melann" <melann@discussions.microsoft.com> wrote in message news:58EE1229-ED29-4A5F-B4D6-BDE863876DB0@microsoft.com... &...

Volume logging to user
Is there any way to determine which users are getting all the email in an exchange organization? On Apr 28, 2:52 pm, talkinggoat <johnmclaren...@yahoo.com> wrote: > Is there any way to determine which users are getting all the email in > an exchange organization? I found a way to accomplish my goal by opening the performance log file in Excel. It isn't EXACTLY what I wanted, but it will work. I was looking for something that could display graphs and separate the emails according to volume, that way I would have an easier time sorting out and blocking spammers. If anyone has ...

can't send anything in Outlook 2002
I just sent a post asking for help because I can no longer send email with attachments. After sending in the post, I went back to Outlook and clicked on the Send/Receive button just to check if I had any new mail. I got the same message: "The operation failed". So I am totally out of business with Outlook. How to solve the problem? Marilyn Did it work before? Did you change anything in yr settings? Are you sure your internet mail account settings are valid (correct username, pw, smtp/pop3 servers)? ------------ "Marilyn Fogelquist" <fogel@cablespeed........

How can Journal be used if Project is not installed, or on the net
How can Journal be used if Project is not installed, or on the network -- Rusty Nichols Network Support Technician The Journal folder works without Project being installed at all. It's an integral part of Outlook. What functionality are you seeking? "Rusty" <w_r_nichols_iii@yahoo.com> wrote in message news:7EA79673-AE35-44D2-A09E-EBF73E7EC414@microsoft.com... > How can Journal be used if Project is not installed, or on the network How can Journal potentially affect the Exchange server, and I would like more information on Journal for a Outlook foundation class? ...

Dates in a form for filtering Report query
I have a form "Period" with two text boxes. One for startDate and one for EndDate. I want to use that form to limit the query for my Report by the dates. However, when I refer to the Form it does not seem to understand it is a date? I use the following statement in my query: SELECT Opphold.CheckIn FROM Opphold WHERE (((Opphold.CheckIn)<=[Forms]![Perioder].[txtStartDate])); I also tried to convert it to a date like the following: CDate(<=[Forms]![Perioder].[txtStartDate]))) but that did not work? What should I do in order to the query to read the condition or dat...

cmbo box on subform fills fields on form when chkbx is yes
I have a multi-tabbed form that details bid data ranging from pricing to contact info to departmental notes. Page two has a subform listing general contractors to whom we bid. One bid may have 10-15 contractors listed. When a purchase order is issued from one of these contractors I select a checkbox indicating that GC. I would like the opening page on this tabbed form to show the address and contact info for that GC when the checkbox is yes. Is this possible? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200803/1 On Mar 17, 3:04=A0p...

Wrapping clipart around text
When i word i can wrap my text around my pictures is there any way of doing the same in Publisher 2000. -- Thank you for reading my post. Hopefully you can answer my querie On the toolbar there is a hand holding a script. This is the wrap tool. Select the object, select the tool. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Shazza" <shazzalowe@hotmail.co.uk> wrote in message news:474A689F-DC18-4545-A73B-1E2CDDB34633@microsoft.com... > When i word i can wrap my text around my pictures is there any way of d...

allowing or disallowing changing the state of check box
Hi, what's the best moment to check if the user has right to change the state of check box and then to allow it or cancel it. I've tried with "before update" event procedure. It seems to work but after, when I press anything else on the form event procedure is repeating? Any help? thanx alekmil One addition: The problem occurs (repeating) when Cancel = True was applied previosly. "alekm" wrote: > Hi, > what's the best moment to check if the user has right to change the state of > check box and then to allow it or cancel it. I'...

How can I tell if our GP 9.0 is updated to the latest SP and patch
We recently upgraded our GP to 9.0, but I'm not sure we are at SP1 or the latest patches have been installed. Is there a way to tell from the versions numbers displayed in the "About Microsoft Dynamics GP" box? Or, maybe asked a different way, if at the latest updates, what should the Dexterity and GP 9.0 version numbers be at? hello, You know if it's Dynamics GP SP1 when you see that: Dynamics GP - 9.00.0259 Dexterity - 9.00.0068.0 rgds, timber On Oct 6, 6:35 am, dcnjoe60 <dcnjo...@discussions.microsoft.com> wrote: > We recently upgraded our GP to 9.0, bu...

How Can I Tell What Button Was Just Clicked?
I have a bunch of images on the sheet that calls ONE function. Is there a way I can tell which button was clicked within the function? I'd hate to make a function for each image. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ See one technique at your other thread. kenji4861 wrote: > > I have a bunch of images on the sheet that calls ONE function. > > Is there a way I can tell which button was clicked within the > function? > > I'd h...

Does anyone know where I can download EXCEL.MSI?
I am missing my Excel 2002 CD, and can't install Office Update Service Pack 3 without this file. ...

Money 2002 Can't seem to download sucessfully
I have been trying for 10 days now to download banking info from my various accounts into Money 2002. Before it worked, now for some reason it is showing that it is downloading, but doesn't import into the Money program. What happened? I created a Passport to work online, but that is the only thing that has changed on my end. Can someone please help me!!?!?!?!??! >-----Original Message----- >I have been trying for 10 days now to download banking >info from my various accounts into Money 2002. Before it >worked, now for some reason it is showing that it is >dow...

How can I instantly delete emails? I get a strikethrough + purge
...

To increase the upload file limit ONLY for one site collection
Hi, I have more than 1900 site collections on a web application. I know I can modify the upload file limit thru the CA or directly on the Web config file. However, I do not want to change the setting for all the site collections. I need to be able to change the setting only for a particular site collection. Is there anyway I can add manualy that entry in the web config file. Has anyone tried? Is this possible? Thanks in advance, Antonio ...

connect a form to excel database
I have a database in excel which lists our stock and prices etc. I would like to be able to enter a stock number on an invoice that will retrieve the details and price from my excel database. Can this be done? Excel has built in option--try data|form. If that doesn't quite work, but it's close, John Walkenbach has an enhanced dataform at: http://j-walk.com/ss/dataform/index.htm And if you want to design your own, Debra Dalgleish has a get started with userforms: http://contextures.com/xlUserForm01.html Kathy wrote: > > I have a database in excel which lists our stock and p...

Combo Box Value List/Query Criteria
Hi Folks - I have a combo box with a value list of Option1, Option2 and ALL. I pass this combo box info into a query. Here's a sample of the query criteria: IIf([Forms]![frmSwitchboard]![cboCaseType]="all",Is Null,[Forms]![frmSwitchboard]![cboCaseType]) In other words, if the combo box selection is ALL, then display all records, otherwise use selected option. The above criteria does not work. Any suggestions? Thanks. Michael Michael, Try... (IsNull is an operator, not a value) IIf([Forms]![frmSwitchboard]![cboCaseType]="All", Null,[Forms]![frmSwitchboar...

Can't do a @ in TO: section
Hi! My problem is simple, I cannot do a @ when im trying to put an email adress in the TO: section HELP please... Why? What happens when you try -- Peter Please Reply to Newsgroup for the benefit of others Requests for assistance by email can not and will not be acknowledged. "Nico" <Nico@discussions.microsoft.com> wrote in message news:971A2D8D-FBD4-4F57-830B-567304F30A38@microsoft.com... > Hi! > > My problem is simple, I cannot do a @ when im trying to put an email adress > in the TO: section HELP please... ...

Add New Record Using Combo Box
I am using 2 different tables: Employee Info and Trip Details. I need to have a blank form open to enter new information, but I would like a combo box to choose the employees name, as there are over 300 employees. Ideally, the first name, last name, and DOB would be populated, but the rest of the information would still be blank to add new data. I could then just hit a button to open the form again to add information about a different employee. I am having a problem doing this and would greatly appreciate if someone could let me know how to accomplish this. Thanks. On Wed, 21 Mar 2007 17:26:03...

How to turn off auto save on forms?
Hi All, I need to be able to have the database not auto save when entering in new information on a form. I would like for there to be a prompt for them to save the information. As of now when you enter in new information and close the form it auto saves, and this is what I am trying to avoid. Can anyone please help with this? Thanks On Jan 7, 8:14 am, Michelle <Miche...@discussions.microsoft.com> wrote: > Hi All, > > I need to be able to have the database not auto save when entering in new > information on a form. I would like for there to be a prompt for them to &...

Convert Date to Text
I want to use a date field to produce an invoice number. Can anyone tell me how to enter a date in a date formatted field e.g. 31/12/06 and have "061231" appear in another cell, but formatted as text? (It needs to be text because my next stage will be to add the first three characters of the company name [concatenate], which I'm OK with. Thanks Jeff Jeff, try this: ="ABC"&TEXT(A1,"yymmdd") where your date is in cell A1 and your company initials are ABC - you could get these from another cell, eg B1, so the formula becomes: =B1&TEXT(A1,&qu...

popup box message; "Publisher cannot open the file"
saved as a .pub file but unable to open. popup box message "Publisher cannot open the file" What version Publisher do you have? Do you know what version Publisher was used to create the file? -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "stature" <stature@discussions.microsoft.com> wrote in message news:0AD1D565-743D-478D-95DD-5C5358400272@microsoft.com... > saved as a .pub file but unable to open. popup box message "Publisher cannot > open the file" ...

filling a field in a continuous form with a field from a table
I have a form with the fields: date, client, atty, time, billable and description. All these fields update the Time Sheet Table. I want the atty field to automatically take the attorney's name from the table Attorney which contains one field called Atty. So for I can only figure out how to use a combo box to chose the Attorney table field. You could set the default value of the control using DLookup(). =DLookup("Atty","tblAttorney") This could be a locked combo box that displays whatever value you want or can derive from tblAttorney. -- Duane H...

Some lines of text in Excel field do not wrap text. Please help.
Within a field with the wrap text checked, some of the lines of text do not wrap properly in the column. How can I correct this? Please help. Thank you. ...

Why can't I see fonts in color as I compose in Office 2003?
My new computer arrived preloaded with Office 2003 - a fair jump from Office 97 - but I am unable to view fonts in color as I compose. This is also the case in Outlook Express 6. Print Preview works in Publisher, Word, etc. but email is impossible! I can only be sure of what I'm sending if I send to myself first. Can anyone help? TIA Judy Outlook Express is not an Office product. Look at the internal settings in Outlook Express. (Options). You might try installing Office 2003 via the control panel, there well could be elements that your computer company did not install. Scroll...

Accessing sub forms
Dear Access Experts, I'm using Access 2003 and I'm having the following problem. I have a form (called MainForm) that contains 2 sub forms (called SubForm1 and SubForm2). Both SubForm1 and SubForm2 are datasheet views. When the selected row changes in SubForm1, I need to change the RecordSource property in SubForm2. The row changed event I'm capturing in the Form_Current() sub on SubForm1. I thought I would be able to access SubForm2 from SubForm1 with something along the lines of: Private Sub Form_Current() Me.Parent!Subform2.Form.RecordSource = "select * from mySubT...