Module, UserForm, & MultiPage Managment

[Rufus Clupea]

I'm working on a fairly large (for me) project, using one (main) UserForm with a MultiPage. I was working under the mistaken belief that,

"A single VBA module can store any number of Sub procedures, Function procedures, and Declarations."
—Excel VBA Programming for Dummies

So most everything went into the UserForm Module. Things were going pretty well until I noticed the "fine print",

"Well, there is a limit—about 64,000 characters per module....the solution is simple: Just insert a new module."
—Ibid.

Ummm... right.

When I saw that qualifier, I copied my code into Word for a quick character count—it was something over 75,000 characters, and I knew I was only about 1/3 to 1/2 way along, so I split things into several modules, believing—as the book said—it would be "simple". That's where....

So I'm now engaged in probably one of many major re-writes (which is fine—I expected this ) Good time, methinks, to ask a few questions that have been collecting...

1. Is there any way of (globally?) telling modules that they're all part of the same project, and avoiding having to re-code everything with a "UserForm1." prefix (I'm using WITH-END WITH, but there's a LOT of stuff to account for)?
2. Is there a way to tab (key) directly from the last TabStop on one page of the MultiPage to the first TabStop on the next page without manually clicking on the MultiPage tab and the first TabStop on each subsequent page?
3. CheckBoxes have Captions; OptionButtons have Captions. Is there an easy way to combine a SpinButton with a Label (thereby giving it a "Caption"), or must I create a whole new object? (Not sure exactly how to do that yet, but willing to learn!)

TYA

https://www.youtube.com/watch?v=BxacATCHrpo

 

[Rufus Clupea]

Rufus,

Just for future reference, with all the controls that you currently have on one form, how efficient does it run? Does it take time to load? Does it bog down when you change a control? Have you noticed abnormal wait times?

Mmmmm... Keeping in mind I currently have something less than 300 controls with another ~220 to go, let's say it runs more efficiently with every iteration/rewrite as I learn more.

"Boggage" is noticeable, but just slightly, and not (IMO) significant. Doesn't affect functionality.

It takes no perceptible time to load; pretty much boom—it's there. Initialization is quick, as I've entered text directly into the .Caption properties rather than reading them in (which has also helped as I've moved some things around). There is only a smattering of relatively small arrays to read in off of Sheet1. There are 3 ComboBoxes with a dozen or so items each, but I've entered those directly in the code via ComboBox1.AddItem.

I have not noticed any wait times (yet). Even the tally Subs (Looping through each type of control, and creating lists of selected options with their respective values) seem to go pretty quickly.

I expect this may change when I add in those last couple hundred SpinButtons, but I keep tinkering with things as I go.

I can't really opine on the ActiveX question as I haven't done much with that. I tried it when I first began this project, but quickly switched over to the UserForm.

I didn't mention it before, but my main UserForm is full-screen. My intent is for the end user to never have to look at/see/be confused by the Spreadsheet until reviewing/printing the final product.

 

[Rufus Clupea]

I have never built a UserForm or even a sheet with hundreds of controls.

I encourage you to try it, even if just to play around a bit. I got creative with an "optional" page on my MultiPage. It appears blank except for one Checkbox with a question. If the user responds in the affirmative (checks the box) the page fills in with Frames and more controls and output. I used the .Visible and .Enable properties, and re-sized a Frame "on the fly" to expose the additional controls.

 

[Jeffrey Mahoney]

When you say Worksheet Form. Do you mean a Worksheet that is designed to look like a Userform. With Activex controls? If so if you have hundreds of controls and want a way to only see some of those controls at a given time how do you do that with a Worksheet Form?

Yes, It looks like a form. I hide all the rows and columns I don't need and restrict navigation. I create checkboxes with Wingding symbols 254 and 111 and worksheet events for click and double click. I have buttons that take users to other sheets that also have forms. The buttons hide and unhide sheets accordingly. To hide a control on a worksheet:

SpinButton1.Visible = False

To hide sections of the form I can hide rows or columns. I can insert a background image and control which cells show or hide the background; usually a solid color, but sometimes an image related to my client. I've learned how to automatically outline a group of cells with cool shadows so the area looks offset from the sheet. I've learned how to add pop up text to any control so when a user mouses over it displays context sensitive help.

I tend to think that forms for entering data should be on one screen. If the data you are showing has hundreds or thousands of rows, then why not use the Freeze panes feature and have your controls above (built in list box). If I have more than a screen full of controls, then I ask myself if another sheet is necessary. Of course, if it is intended to be printed or a PDF is going to be created, then it remains as one sheet. Using Excel for creating interactive printable forms and saving as a PDF is much easier and cheaper than trying to create a fillable PDF.

One of the reasons I choose to use worksheet forms is that Cells are actually built in controls that I don't have to create. I can interact with the values of cells in a much more user friendly manner. I can format them in bulk much easier. Protected formulas in cells are more flexible than trying to fill list boxes and text boxes. Combo boxes work directly with lists and dynamic named ranges. I can add insert comments in cells for descriptions of what to enter.

One of my projects is a Lockout / Tagout application for Refineries. It has over a dozen forms all controlled by Ribbon menus, buttons, and worksheet events. The forms are not only for controlling how data is entered, but also to be printed for use out in the field. The worksheet tabs are there for the user to navigate between all the visible forms. Navigation between sheets is simple. I have context sensitive Ribbon menus designed to show only what is needed for that form. If I created this as a Form based application, I probably would have had to create over 400 hundred controls. I probably have only a couple dozen ActiveX controls. It can print the Lockout tags onto special environmentally protected tags for warning people not to engage any hazardous equipment.

One of my projects was designed for a company to track their inventory for client orders using barcode scanners and importing SAP reports. One worksheet for scanning and showing completion results, and many worksheets for displaying details about each order and the remaining unscanned items. Again, all controlled by worksheet events, buttons, and Ribbon menus. They reduced the inventory time and personnel from 8 hours and 4 people to 3 hours and two people; every single night.

My apologies for the going on so long about my past projects. I was just trying to convey that my methods are productive and financially rewarding.

Jeff

 

[My Aswer Is This]

I have built and used UserForms with Multipages with as many as maybe 100 controls but not hundreds which may mean 300 or more. With just one hundred all works well but I'm not sure about if you use 500 or 600 hundred. And then what does slow and fast mean? Some users think .005 milliseconds is fast and other users think .005 milliseconds is slow.

I encourage you to try it, even if just to play around a bit. I got creative with an "optional" page on my MultiPage. It appears blank except for one Checkbox with a question. If the user responds in the affirmative (checks the box) the page fills in with Frames and more controls and output. I used the .Visible and .Enable properties, and re-sized a Frame "on the fly" to expose the additional controls.

 

[Rufus Clupea]

Jeff,

Don't apologize—that all sounds fascinating!

I create checkboxes with Wingding symbols 254 and 111

Gotta ask... Why did you do that instead of using ActiveX CheckBoxes?

 

[Rufus Clupea]

I have built and used UserForms with Multipages with as many as maybe 100 controls but not hundreds which may mean 300 or more. With just one hundred all works well but I'm not sure about if you use 500 or 600 hundred. And then what does slow and fast mean? Some users think .005 milliseconds is fast and other users think .005 milliseconds is slow.

Ahh... sorry, misinterpreted.

I know what you mean about speed being relative. I generally think more in terms of, "Will it function acceptably to an average user?" (whatever that means ) I don't really know how it'll do with another couple hundred SpinButtons, but I'll find out! If not acceptable, and I can't find a way to fix it, I'll just have to break it down into smaller pieces; I've already thought of ways to do that, but I'd like to try it as one piece first.

 

[My Aswer Is This]

Thanks for your reply. I hope you realise we are just here trying to help you not trying to twist your arm to do things like we may think. Looks like you know a lot about Excel Vba and glad to see you know about Userforms and Multipages. So lets see. Do you have a question for us we have not not answered?

Ahh... sorry, misinterpreted.

I know what you mean about speed being relative. I generally think more in terms of, "Will it function acceptably to an average user?" (whatever that means ) I don't really know how it'll do with another couple hundred SpinButtons, but I'll find out! If not acceptable, and I can't find a way to fix it, I'll just have to break it down into smaller pieces; I've already thought of ways to do that, but I'd like to try it as one piece first.

 

[Kyle123]

Well I reckon you’re all *******s putting that many controls on a short or userform manually, you’ve all got more patience than me

 

[Jeffrey Mahoney]

Rufus,

Gotta ask... Why did you do that instead of using ActiveX CheckBoxes?

Checkboxes are not sizable (the box itself, the text is ok). I sometimes use hundreds of checkboxes or Option buttons. That many controls are hard to keep in line with the other form items. Creating that many controls is a PITA! My database style method allows the user to delete rows of data. I can NOT relate a particular check box to a particular record or row. With wingding symbols that look like a checkbox or empty checkbox I can:
* resize the box to any font size
* conditional format the color to indicate the need for a response, or even grey it out to show that no response is needed
* create as many as I want by simply copying them down the column
* turn them all on or off easier
* because they are tied directly to the row I can associate them with a particular record
* I can check status using a formula AND VBA

Jeff

 

[My Aswer Is This]

I also use sheet event scripts also like Double click to perform tasks.
A lot easier then having many controls on a sheet.

Double click a certain cell or range of cells and Presto a script can be run to do almost any task.

And this can be done with very limited amount of code:

For example Double click on Range("A1") to delete Row(1)
Double click on Range("A45") to delete or hide Row(45) all this can be done with hundreds of rows with only about 5 lines of code

But then you may already know all this.