Userform with 1024+ textboxes

[Scarlett]

I am reading in some data from excel into a UserForm. There is a lot
of data organized on many different MultiPages.

When I add more than ~1024 textboxes on my UserForm, I get an "Out of
memory" error when I try to edit the code. I don't think that it is
related directly to just text boxes (if I have 1020 textboxes and 10
checkboxes I get the same error).

I read somewhere that the UserFrom objects are all public? Is this the
cause of the limitation? Is there a way around this? Can I nest one
UserForm within another? Can I increase this limit?

Thanks

 

[Bob Phillips]

How can anyone realistically use a form that has 1024 textboxes.

I think you need to re-design your project.

 

[Jon Peltier]

I've gotten into this problem with around 250 textboxes. Instead of
multipages, make separate forms. Also, if you have more than even a couple
dozen textboxes on a form, I can't imagine it being easy for a user to use.

As Bob suggests, reconsider the design.

- Jon

 

[Scarlett]

How can anyone realistically use a form that has 1024 textboxes.

I think you need to re-design your project.

--
__________________________________
HTH

Bob

With all due respect, some projects require a level of complexity that
you likely have not experienced.

 

[Scarlett]

I've gotten into this problem with around 250 textboxes. Instead of
multipages, make separate forms. Also, if you have more than even a couple
dozen textboxes on a form, I can't imagine it being easy for a user to use.

As Bob suggests, reconsider the design.

- Jon

Jon,
The userform currently has several tabs with an array of textboxes
(24x7) in each one. The user will not be modifying all 168 boxes on
each tab, but will need to selectively alter a few of them. I am
willing to sacrifice allowing the user to alter any of the text boxes
in the "parent" form if I can in at least display the values as a
(24x7) array in tabular form (maybe as labels?) and then have a
separate "edit" button that will open a new userform for them to edit
them there ... upon completing that form the data would be re-
populated back to the "parent" userform.

Does that seem possible?

Scarlett

 

[Jon Peltier]

With all due respect, you have no clue who you're talking to. If Bob
suggested to me that I redesign my project, I would go back to the drawing
board.

Sometimes it is necessary to redesign a project to redistribute the
complexity in such a way as to conform to the constraints of the tools used
in the project.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______

How can anyone realistically use a form that has 1024 textboxes.

I think you need to re-design your project.

--
__________________________________
HTH

Bob

With all due respect, some projects require a level of complexity that
you likely have not experienced.

 

[Dave Peterson]

And as a user, I wouldn't want to see that many textboxes in any project.

Scarlett wrote:

 

[Scarlett]

With all due respect, you have no clue who you're talking to. If Bob
suggested to me that I redesign my project, I would go back to the drawing
board.

Sometimes it is necessary to redesign a project to redistribute the
complexity in such a way as to conform to the constraints of the tools used
in the project.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.http://PeltierTech.com/WordPress/
_______





With all due respect, some projects require a level of complexity that
you likely have not experienced.

I beg to differ. I think everyone has seen a million posts of the type
"why don't you rethink what you are doing?" without any further
explanation, no suggestions, no questions as to why they are trying
such a thing, nothing... that's just isn't helpful, no matter who they
are and it's even worse if they actually know a thing or two on the
subject and haven't helped out.

 

[Scarlett]

And as a user, I wouldn't want to see that many textboxes in any project.

Scarlett wrote:

<<snipped>>

Dave,
I wouldn't want a 1000 textboxes either ... unless I actually needed
to modify a few of them each time. And that's the problem, the user
needs to view all of them and only modify a select few.
Scarlett

 

[Bob Phillips]

I am afraid that I have, many times. Some of my most lucrative commissions
have been in picking up the pieces from such projects when they go awol.

There is a vast difference between complexity and poor design, and I know
enough about how the brain processes information to know that it cannot
process 1024 textboxes, and therefore that is poor design and would benefit
from a re-think.



Bob

How can anyone realistically use a form that has 1024 textboxes.

I think you need to re-design your project.

--
__________________________________
HTH

Bob

With all due respect, some projects require a level of complexity that
you likely have not experienced.

 

[Bob Phillips]

Believe me Scarlett, my suggestion to re-design was an attempt at helping. I
could not offer any deeper insight as I do not know the project in any
detail. But I do know that having that many controls on a form is asking for
trouble. you have experienced one physical problem, but I would venture your
users probably find it very difficult to manage this form and may well be
wasting a lot of time and effort, even making mistakes.

--
__________________________________
HTH

Bob

With all due respect, you have no clue who you're talking to. If Bob
suggested to me that I redesign my project, I would go back to the drawing
board.

Sometimes it is necessary to redesign a project to redistribute the
complexity in such a way as to conform to the constraints of the tools
used
in the project.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.http://PeltierTech.com/WordPress/
_______





With all due respect, some projects require a level of complexity that
you likely have not experienced.

I beg to differ. I think everyone has seen a million posts of the type
"why don't you rethink what you are doing?" without any further
explanation, no suggestions, no questions as to why they are trying
such a thing, nothing... that's just isn't helpful, no matter who they
are and it's even worse if they actually know a thing or two on the
subject and haven't helped out.

 

[Jon Peltier]

In general, I think the reliability limit is on the total number of
controls. I'm not sure 1000 labels would be an improvement on 1000
textboxes.

Your description is now detailed enough for us to imagine alternatives to
your original layout.

Could you use a single 7x24 array of textboxes, and hold in memory a series
of 7x24 arrays? When the user selects which data they want to view, you can
store the old textbox values in the appropriate array and populate the
textboxes with the new values from the selected array. The user will not
notice any difference in the time it takes to switch pages in a multipage
and the time it takes to repopulate the array of textboxes.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______

I've gotten into this problem with around 250 textboxes. Instead of
multipages, make separate forms. Also, if you have more than even a couple
dozen textboxes on a form, I can't imagine it being easy for a user to
use.

As Bob suggests, reconsider the design.

- Jon

Jon,
The userform currently has several tabs with an array of textboxes
(24x7) in each one. The user will not be modifying all 168 boxes on
each tab, but will need to selectively alter a few of them. I am
willing to sacrifice allowing the user to alter any of the text boxes
in the "parent" form if I can in at least display the values as a
(24x7) array in tabular form (maybe as labels?) and then have a
separate "edit" button that will open a new userform for them to edit
them there ... upon completing that form the data would be re-
populated back to the "parent" userform.

Does that seem possible?

Scarlett

 

[Scarlett]

Jon,
That seems like an interesting approach. I will investigate it
further. I will also try another method someone else suggested
elsewhere ... the edit on DblClick.
Thanks

 

[Scarlett]

Bob,
The 168 textboxes on each tab actually represents a matrix of values
with a clear pattern and as a result, the data is quite easy to
digest.
Scarlett