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

 

[Jeffrey Mahoney]

Let me address question number 1 before moving on. I need to ask questions so I can understand if we can change the style a little. I realize that your project is large and I may ask you to post some code, but lets see if we can find a solution.

Have you created SUBs on the userform1 module that support the Form that are NOT event based? Event based SUBs are routines that respond to events on the form. These usually start with Private. Like: Private Sub UserForm_Initialize(). Any Non event based SUBs can be moved to a standard module and can be called like: Module1.ThisIsAMacro

Jeff

 

[Jeffrey Mahoney]

Question 1 related.

You can also call SUBs in other modules to run a lot of your code. You don't have to keep all the code in the Event SUBs.

 

[Jeffrey Mahoney]

Sorry for the multiple posts. How many controls do you have on your multipage form? Not exactly, just a good guess.

 

[Jeffrey Mahoney]

Question 2.

Sounds like you want a keystroke that allows you to scroll through the tabs on the Multi-Page form. Like in a browser, you can press Ctrl-PageUp and Ctrl-PageDn to do that. With one problem. Any controls like listboxes that allow you to page down through the list will interrupt that. Bummer. I haven't seen a solution unless you want to add a KEYDOWN event to every listbox control. Then you would have to look on how to check the Control Key Press.

Maybe others have some code for that.

Jeff

 

[Kyle123]

It sounds like you’re doing something wrong, there shouldn’t ever be a need to come anywhere close to those limits.

At a guess, you have a lot of repetition of code and it sounds like you have sets of related controls that are then repeated. That sounds like you want a loop and some class modules to remove the repetition. However, you need to explain what you are trying to do, how your forms are made up and really share some code so that we can give you some direction.

 

[Rufus Clupea]

.... Have you created SUBs on the userform1 module that support the Form that are NOT event based? ....

Jeff

Yes, those were the Procedures/Subs I moved out to a Module I named "Processing". I have another Module for Declarations & Functions. Problem is, many of the Subs in Processing still affect properties of the UserForm Objects.

How many controls do you have on your multipage form? Not exactly, just a good guess.

Lots. ~100 Option Buttons (Framed in groups of 2-14), ~70 SpinButtons (so far), ~70+ Checkboxes. These occupy the first 9 pages of the MultiPage. I'm expecting another couple hundred SpinButtons (not written yet) with ~40 per page, for another 6(?) pages. I'm guesstimating ~400-500 controls in all. Most all have a single line (or 2) between the Sub & End Sub headers, sending them to common Procedures in the Processing Module.

All this is, of course, my own doing. Before I realized that there were limits to Module size, I just referenced all the objects directly (e.g. OptionButton5.Value, CheckBox71.Value, Label95.Caption). After moving stuff to Processing, I had to change those references to UserForm1.OptionButton5.[property], etc. Of course I'm (now) using WITH Statements, and adding periods (.) before every Object reference, but I was hoping there was some global statement I could put at the beginning of a module that might avoid all that.

Question 2.

Sounds like you want a keystroke that allows you to scroll through the tabs on the Multi-Page form.

Not exactly; the Left & Right cursor arrows will move from MultiPage Tab to MultiPage Tab.

Not everything is on the MultiPage. Some items that need to be visible at all times are outside the Multi. A user will typically tab(key) through those first, then tab(key) onto the MultiPage. On the first few pages, OptionButtons are arranged in Frames, with only default values assigned as TabStops (user can still make other choices via mouse or Up/Dn keys. After tab(key)ing through a page, the next tab(key) sends the cursor back to the first TabStop on the UserForm (which is not on the MultiPage). User must click on the next Page Tab, then tab(key) to the first option on that new page. If I could get the program to tab(key) directly from the last TabStop on a page to the first TabStop on the next page, it would be much easier for users to navigate. I understand this may not be possible (I've never seen it in any "Wizard"—there always seem to be "Next/Previous Page" Command Buttons) but I had to ask; it's sometimes difficult (especially for the uninitiated) to see/figure out exactly where the cursor is (on the MultiPage or off of it).

 

[Rufus Clupea]

It sounds like you’re doing something wrong, there shouldn’t ever be a need to come anywhere close to those limits.

I don't disagree. Please see my response to Jeffrey above regarding the size of this monster. This project was virtually impossible (for me) to do a top-down outline to work from—just too involved with too many variables affecting each other in odd ways. (Yeah, I know—that's exactly why a top=down outline would be helpful... ) It was (for me) easier to break it up into sections, get each one working, and then go back and optimize. A lot of that is done now, but there are still hundreds of references to update/debug (not to mention my learning more as I go—I'm kinda learning VBA on the fly, 40 years after learning BASIC on a C-64 (and a college course in Pascal/structured programming).

 

[Jeffrey Mahoney]

The only other thing I could suggest is that you create individual Forms instead of a multipage form. Maybe use buttons that look like tabs at the top to open and close the individual forms. A frame around the form that looks like a multi-page could work. I have done that before.

Without looking at the code, I wouldn't be able to make any more suggestions.

I'm guesstimating ~400-500 controls in all

That is a lot

This was going to by my next suggestion

Most all have a single line (or 2) between the Sub & End Sub headers, sending them to common Procedures in the Processing Module.

 

[Rufus Clupea]

I appreciate the feedback from both of you. I suspected some bullet-biting might be unavoidable, but unless I ask...

Any thoughts/ideas on a way to add/combine a Label with a SpinButton (or add a .Caption property to a SpinButton)? I have some Subs that get the job done, but given the sheer number of SpinButtons I'm working with... Y'know...

The Checkboxes & OptionButtons were pretty simple—they're either True or False. OTOH, the SpinButton values have different ranges—some >= 0, some <= 0, and some both, with different .SmallChange increments (or effects of those increments).