|
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? |
What is the Limit of Text Boxes & Labels a User Form can have?
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? |
What is the Limit of Text Boxes & Labels a User Form can have?
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? |
What is the Limit of Text Boxes & Labels a User Form can have?
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" wrote in message ... 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? |
What is the Limit of Text Boxes & Labels a User Form can have?
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" wrote in message ... 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? . |
What is the Limit of Text Boxes & Labels a User Form can have?
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? |
What is the Limit of Text Boxes & Labels a User Form can have?
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" wrote in message ... 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? . |
What is the Limit of Text Boxes & Labels a User Form can have?
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" wrote in message ... 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? . |
What is the Limit of Text Boxes & Labels a User Form can have?
"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").D esigner 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" wrote in message ... 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" wrote in message ... 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? . |
What is the Limit of Text Boxes & Labels a User Form can have?
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").D esigner 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 ... "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").D esigner 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" wrote in message ... 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" wrote in message ... 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? . |
What is the Limit of Text Boxes & Labels a User Form can have?
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").D esigner 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" wrote in message ... 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" wrote in message ... 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? . . |
What is the Limit of Text Boxes & Labels a User Form can have?
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 ... 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").D esigner 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 ... "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").D esigner 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" wrote in message ... 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" wrote in message ... 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? . |
What is the Limit of Text Boxes & Labels a User Form can have?
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 ... 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").D esigner 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 ... "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").D esigner 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" wrote in message ... 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" wrote in message ... 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? . . |
What is the Limit of Text Boxes & Labels a User Form can have?
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" wrote in message ... 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 ... 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").D esigner 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 ... "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").D esigner 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" wrote in message ... 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" wrote in message ... 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? . . |
What is the Limit of Text Boxes & Labels a User Form can have?
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" wrote in message ... 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 ... 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").D esigner 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 ... "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").D esigner 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" wrote in message ... 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" wrote in message ... 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? . . . |
| All times are GMT +1. The time now is 10:24 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com