User Form Control Limits

[ImSimple]

I have created a user form with multiple arrays of check boxes and text boxes. I have a code that works great but I have to add a LOT more check boxes and text boxes, 2080 of each, and I am at the end of the apparent limit of height for the form and it is starting to crash excel. Should I not use a user form?

Any Suggestions?
Thanks,
Jacque

 

[strongm]

>2080

Superficially, that suggests a the UI needs a rethink ...

 

[combo]

Superficially, that suggests a the UI needs a rethink ...

Concerning checkboxes, you can use listboxes with multiselect option set to multi and liststyle set to option.
Guess that the user will not see all at once, so specific topic could be splitted between dedicated userforms.

combo

 

[ImSimple]

Thanks for the advice. I am building this for my boss and he wants to be able to see everything. He wants it to be "User-Friendly" meaning he wants it to be simple. I have visual studio now so I am building it as a separate application.
Wish me luck.

 

[fumei]

I am totally with strongm.

" He wants it to be "User-Friendly" meaning he wants it to be simple"

and

"a LOT more check boxes and text boxes, 2080 of each,"

IMO, these are mutally exclusive. I see no way that ANYTHING with 2080 controls can be called "simple".

Frankly, again vis-a-vis strongm, a design rethink seems in order. I have worked with building applications (mainframe and PC) for 40 years and I have NEVER seen anything that truly required that many controls.

Indeed, I DO wish you luck.

"A little piece of heaven
without that awkward dying part."

advertisment for Reese's Peanut Butter Cups (a chocolate/peanut butter confection)

Gerry

 

[DaveInIowa]

I am totally with strongm and fumei.

Create a Word document with 2100 lines; take that to your boss and ask him if that's the type of user interface he wants to navigate to select items in the list.

Can these check/textboxes be grouped into logical sections? If so, maybe you could have a dropdown list where the user could select a section that would trigger loading a multiselect listbox as combo suggested above.

 

[ImSimple]

Well at least I'm not the only one who thinks so. I understand what he is wanting and it kind of makes sense to have everything on one page and scrollable. They can be grouped and I thought about the multi-select dropdowns but it's not what he wanted.

This project is meant to replace an Excel spreadsheet with all of these items which they had been choosing individually and copy pasting into separate worksheets, the end result being a workbook with about 30 worksheets each with anywhere from 20 to 60 of the items on the first worksheet. I have build a template worksheet and the check boxes will copy the template sheet and title according to a corresponding text box for that check box and then copy the text boxes that have their corresponding check boxes checked in the similarly indexed sections of the form to the appropriate new sheet.

Thanks for the advice. I'll let you know how it turns out using VB.

 

[Andrzejek]

a workbook with about 30 worksheets each with anywhere from 20 to 60 of the items on the first worksheet.

I would suggest some kind of data base instead of worksheets. Event MS Access would do a lot better job of keeping all your data. And the retrieval of data is a lot easier with SQL

Just my opinion....

Have fun.

---- Andy

 

[ImSimple]

Oh I 10,000% agree but we don't have Access and he is really set on this producing a workbook in excel because that is what they have always done and because they will be adding data to the worksheets later.

 

[strongm]

>we don't have Access

You don't have to have Access to use a Jet database ...

 

[combo]

Can you consider partly protected excel worksheet(s) instead of userform? It's scrollable and requires relatively less code. It's also easier to expand project if some assumptions change.

combo

 

[ImSimple]

combo,

I tried to put the check boxes directly on the excel worksheet and it just kept crashing. So far I have about 1800 of the Checkbox/Textbox combos on the form. It hasn't crashed but everytime I make even the smallest change, like renaming one of the controls (and obviously I will have to rename every control so that the index/array logic will work), it takes a LONG time. I know this is totally possible. I have seen gigantic forms with tons of checkboxes before.

I'm trying to look on the bright side.
1. I have something to do
2. I am learning
3. I got a new toy

Love the advice and suggestions. Love learning from you guys and talking to people who know what I'm talking about.

Jacque

 

[fumei]

because that is what they have always done "

Sigh....we hear you. This is unfortunately a very very common situation.

Again, good luck.

"A little piece of heaven
without that awkward dying part."

advertisment for Reese's Peanut Butter Cups (a chocolate/peanut butter confection)

Gerry

 

[combo]

I meant usage of unprotected cells instead if textboxes, validation with cell dropdowns or form checkboxes, named ranges to store support lists etc. All under VBA supervision.
In any case, nearly 2k of controls - variables is a real pain, both for the designer and for the reader, so I would try to rearrange the design to limit them, as strongm proposed. If there is a neeed for so detailed information, it is possible to build output report combining more data.

combo