Trimming down a Userform without losing functionality

[liquidmettle]

Hello Everyone!

I'd like to troubleshoot how best to get a userform to do what I want while not destroying system resources in the process (I received several out of memory warnings when I initially built this, and trimmed it back so that I still open it). Right now I only have enough to show you my proof of concept.

What I'm trying to achieve: I would like to allow for my users to enter potential data for every day of the year (whether they use all the available space or not is up to their individual project needs). As such I have a total of 2 Multipage tabs.

1st Multipage:

Has 5 pages (which could go down to 4) corresponding to 5 years. On each page, I had initially replicated another multipage tab (so I guess I technically have 6, with 5 multipages housed within another one).

2nd-6th Multipage:

Has 12 Pages (one for each month). Each page has two text boxes for each day (for a numerator and denominator) as well as a label for the day. This equates to 366 days (counting leap year).

What this means in numbers: just within the multipages, there is a total of 5,491 textboxes/labels. That's huge!

I understand that I can probably use tabstrip so that there are only 1098 textboxes/labels within the tabstrip, but I would need to learn how to make each strip save as soon as you switch to another (or save when you close the whole user-form), as well as switch references.

The multipage is preferable because it won't require entered data to be saved between years if someone enters multiple years at once. It would only require the write-to-sheet option when the overall userform is closed (via a command button).

Does anyone have thoughts/recommendations on how to optimize this userform without sacrificing what I want to do?


1.JPG

I've have one other thought occur to me: Having the data entry part be a second userform launched from the first. If we go this route, it would certainly cut down on the total userform size, but I would want to make it load within the first userform, instead of as a separate window. You'll probably tell me this is more easily accomplished with a tabstrip, but I thought I would throw it out there.

Thanks to everyone in advance!

[Kyle123]

I'd have 62 textboxes (2 for each month), then use toggle buttons / tabstrips for the month and year. I'd then persist changes on each tab change - seems much more lightweight and manageable

I may even go further than that and display a calendar control where they click the date and input the numbers - that way you need 2 text boxes and 1 calendar

[liquidmettle]

Hi Kyle,

Thank you for the insights!

I think I understand your first suggestion but I am definitely not familiar with implementing a calendar control. Do you know of any good resources off the top of your head before I do my own search?


I should have mentioned too, and tell me if this is still possible with your suggestions, that I only plan to show the user monthly totals. While data is entered for any date they want to enter it for, I was going to have each month sum its numerators and denoms and then show the statistics on a monthly basis. Is that still possible with the toggle/tab strip approach? Would I just approach it where when they hit the command button to save all changes, it runs the sums from the hidden cells that correspond to each day? Actually that sounds pretty feasible now that I've written it out.

Your thoughts?

[Kyle123]

That would work for a calendar control, have a look at the last workbook posted by lewis in this thread:http://www.excelforum.com/excel-prog...ted-sheet.html

[liquidmettle]

Awesome, thanks Kyle! I'll look this over and see if I have time to tinker today. I'm out of town next week so this thread might collect a little dust in the meantime, but I'll let you know (and probably ask more questions) about what approach seems to work.