Number of UserForm Control Objects

[Guest]

Does anyone know if there is a limit to the number of Control Objects someone
can have on a UserForm?

I ran into an "Out of memory (Error 7)" error today after adding in a few
Labels and Textboxes to a project I'm working on. I added in my objects and
went to edit the code behind them and it wouldn't let me add code. I shuffled
my code from the userform into different modules in an effort to eliminate
the number of lines of code and that wasn't working. I finally moved a few
frames that contained a bunch of objects into new userforms and that did the
trick.

Any ideas?

 

[Guest]

When moving the objects, etc., resulted in something working, you were
reinforced for believing the extra forms (or whatever) did the trick. But
maybe not. There were other things, no doubt, you also had to change when
moving these things. So check carefully the code you changed. You may even
have smiply corrected a typo when you did that.

After many mucho years of programming experience, I've seen my share of "out
of memory" and "hung" programs. 99.99% of the time, it was an unintended
infinite loop that just kept cramming something into something else -- or
kept declaring something and declaring it again.

Either way, you will likely find the culprit (and the "limit" if in fact
that is the issue) by putting in breakpoints (or MsgBoxes or debug.Print
commands,...)-- especially in _Event Subs.

I hope this might help in some way.

Jim

 

[Guest]

I didn't change any code because it wouldn't let me. However, I was able to
scroll through the code to see if I had lft something open I had forgot about
at the end of the day yesterday. I didn't find anything to be the culprit.

The only change to the code I made was moving the code from the userform
into a module. I didn't add a call to the module from the userform proceedure
because when I wrote the call, I got that same error "Out of memory" -- and
then the code I typed (or modified) would disappear.

When I tried to run the userform just to see what would happen, I received:
"Compile error in hidden form 'projectinfo'". Very strange, yet a small
setback (I think). I will just create a "popup" that contains the objects I
removed and move on with my project.

Thanks for the info, Jim.

 

[Chip Pearson]

After extensive editing, and especially on large projects, VBA doesn't
properly clean up its internal code storage areas. They get filled with junk
code. It is nothing that you are doing wrong, but rather VBA not cleaning up
after itself. The solution is to export all code out to text files, delete
all the code in the project, and then finally import the code back from the
text files. This causes VBA to start with a "clean slate" and can cure any
number of strange problems. Rob Bovey has a free add-in called "VBA Code
Cleaner" that automates all this down to a two mouse clicks. See
http://www.appspro.com/Utilities/CodeCleaner.htm .

Rob's Code Cleaner is one of the "must have" add-ins for serious
development.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting LLC
www.cpearson.com
(email on the web site)

 

[Guest]

Here's an additional note:

There are various things you can put in a userform module and not in a
code module -- and vice versa.

Plus, as another commenter pointed-out, some problems evaporate once you
export the code to a text file and import it back again. In the case of user
forms, you will have both an frm file and an frx file, usually. Export the
form, and open the frm file in notepad.

If the export/import thing bears no fruit, then open the file and look at
how it is organized. Try to understand it enough that you possibly may
detect something you did not notice just looking at the form as a GUI object.


Another supposedly legal thing you can do in VB is something that does
not make any sense upon closer examination -- but MS made it work anyway.
It's the "Unload Me" statement (or possibly some similar statements). "Me"
is the default for the control or form whose code you are working with. Bugs
related to this type of problem cannot be debugged without high level support
from MS. (Right, it cost me a bundle when it happened to me!) In my case,
Excel itself went "poof!"

I would never use the "Me" reference. One thing is this: what will
happen if the code is cut and pasted and you forget it used the Me thing? It
will attempt to change properties and invoke methods where Me now refers to
something else.

My last clue is the hope you are not dynamically creating controls on the
fly -- especially, making control arrays. This can really be tricky. Make
sure you do not have any control arrays that really do not need to be arrays.


May you have better luck, soon.

-- Jim

-------------------------------------------

 

[Jon Peltier]

Another supposedly legal thing you can do in VB is something that does

not make any sense upon closer examination -- but MS made it work anyway.
It's the "Unload Me" statement (or possibly some similar statements).
"Me"
is the default for the control or form whose code you are working with.
Bugs
related to this type of problem cannot be debugged without high level
support
from MS. (Right, it cost me a bundle when it happened to me!) In my
case,
Excel itself went "poof!"

I would never use the "Me" reference. One thing is this: what will
happen if the code is cut and pasted and you forget it used the Me thing?
It
will attempt to change properties and invoke methods where Me now refers
to
something else.

I'm not sure what you're saying here, or what doesn't make sense. Me is the
keyword for the module containing the code. Me.Hide hides the userform
containing that line of code. Unload Me unloads the userform, which isn't
the most efficient way to unload a form (i.e., from the form's own code),
but it nominally works.

Whenever you cut and paste code, you have to take care with references. 'Me'
is no worse than 'ActiveSheet' or 'Workbooks("Fred.xls")'.

My last clue is the hope you are not dynamically creating controls on
the
fly -- especially, making control arrays. This can really be tricky.
Make
sure you do not have any control arrays that really do not need to be
arrays.

It sure is tricky to make control arrays in VBA, especially since VBA does
not support control arrays.

I would suggest studying a decent VBA resource.

- Jon

 

[Guest]

It sure is tricky to make control arrays in VBA, especially since VBA does
not support control arrays.

I would suggest studying a decent VBA resource.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______

I was just trying to be helpful.

I do not write much in VBA, but you must admit it is in most ways identical
to VB6. I've written about a half a million lines of that stuff. More than
I can remember sometimes. Coding VB is not my main profession anymore.

Decent? Hmmmph. Your remarks were perceived as unfriendly, S.A. I bet you
think you wrote a decent VBA resource, eh? (By the way, you CAN go broke
underestimating the intelligence of your fellow man.)

 

[Peter T]

I have no doubt you were trying to be helpful but, in the nicest possible
way, with the exception of your implied suggestion to the OP to follow up
Chip's advice, your previous suggestions were misleading at best or
incorrect.

I do not write much in VBA, but you must admit it is in most ways identical
to VB6.

VB6 forms and VBA forms are radically different. As Jon said, control-arrays
are not supported in VBA, but that's only one of so many differences.

Your [Jon's] remarks were perceived as unfriendly,

I can't speak for Jon but I didn't interpret his remarks that way at all,
simply as being helpful; both to yourself and to others.

Regards,
Peter T


In VB6 the number of controls is limited to 254 (excl additional controls in
control-arrays). In VBA

 

[Guest]

Hey Chip,

Thanks for the reply. I tried the tool and it did... something, but it
didn't fix my issue. The issue seems to come into play when I put in or
remove a frame that contains 57 controls... or at least that's what print
UserForm1.Controls.Count says in the immediate window. When I add that frame
into it's original position and try to edit the code, it says "Out of
memory". I take it out and it's fine; I can access the code. The main pages'
name is 'projectinfo' - a print projectinfo.controls.count reveals 1155
objects without the other frame. I'm starting to think there's a 1200 count
control object limitation...

 

[Guest]

For the sake of arguement, I'm not generating any objects dynamically or with
an array. I have never been good with arrays so I stay away from them.

I have no doubt you were trying to be helpful but, in the nicest possible
way, with the exception of your implied suggestion to the OP to follow up
Chip's advice, your previous suggestions were misleading at best or
incorrect.

I do not write much in VBA, but you must admit it is in most ways identical
to VB6.

VB6 forms and VBA forms are radically different. As Jon said, control-arrays
are not supported in VBA, but that's only one of so many differences.

Your [Jon's] remarks were perceived as unfriendly,

I can't speak for Jon but I didn't interpret his remarks that way at all,
simply as being helpful; both to yourself and to others.

Regards,
Peter T


In VB6 the number of controls is limited to 254 (excl additional controls in
control-arrays). In VBA

I was just trying to be helpful.

I do not write much in VBA, but you must admit it is in most ways identical
to VB6. I've written about a half a million lines of that stuff. More than
I can remember sometimes. Coding VB is not my main profession anymore.


Decent? Hmmmph. Your remarks were perceived as unfriendly, S.A. I bet you
think you wrote a decent VBA resource, eh? (By the way, you CAN go broke
underestimating the intelligence of your fellow man.)

 

[NickHK]

Whilst VB6 forms have a limit of 255 (256 ?) names of controls, this works
fine in VBA/Forms2:

Private Sub CommandButton1_Click()
Dim i As Long
Dim Lab As msforms.Label

For i = 1 To 5000
With Me.Controls
Set Lab = .Add("Forms.Label.1", "Label" & i)
Lab.Caption = "Number " & i
End With
Next

End Sub

So I doubt the OP problem is directly caused by too many controls, although
I doubt 1200 controls would make a manageable UI.

NickHK

 

[Jon Peltier]

I did not mean to be unfriendly. You admitted and demonstrated unfamiliarity
with VBA, between statements about control arrays and the 'Me' keyword. I
was just hoping to keep the OP on track.

- Jon

 

[Guest]

1208 to be exact. When I put 1209, the code window won't load.
I can manually view the code, but the object/procedure dropdowns at the top
do not work. I think the answer lies in breaking up my multipage and making a
seperate page for each tab. Anyway, there's not 1200 (+/- a few for
Cancel/Finish buttons) controls on the face of one form. They are contained
within a multipage control distributed as needed throughout 9 tabs. It's
really not a big deal because, like I said, I can just break up the
multipage... I just didn't expect to run into a wall like this. Should have
expected there to be a limitation though...

 

[Jon Peltier]

I've heard anecdotal evidence of an apparent behavioral limit of "about 250"
controls per userform. This is similar to VB's hard 256 limit on control
names, but probably unrelated. I had problems with one userform with around
350, but a redesign brought it under 100 and everyone was happy.

- Jon

 

[Guest]

Well, that's good to know, then. I don't think I can break it up enough to
get down to 100/form, but I think just seperating the tabs into their own
userform may eliminate a whole bunch of headaches.

Thanks for that, Jon.