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]

Join the Label to the Spin Button by grouping them. The spin button will still work as needed. I know, you'll still have to manage the labels, but unless you're changing those on the fly, it doesn't seem to big a deal. You could name the label the same as the spinbutton but add ".txt" to the name.

You can set the range for a spinbutton by using these commands in the Initialize SUB for the form:

SpinButton1.Min = 1
  SpinButton1.Max = 9

 

[Kyle123]

I'd do it like the below, this shows you how to group single form controls into larger components that consist of multiple controls that are added dynamically so don't need adding through the designer. The example below adds 3 SpinComboControls to the userform (though it will work in a frame etc) that consist of spinbuttons that change the value of their associated label.

This reduces drastically the amount of code you have to write so it becomes manageable. You also create re-usable components that may have uses elsewhere, additionally it keeps your userform code clean, each SpinComboControl takes care of updating the label etc and doesn't pollute the userform with this logic.

Class SpinComboControl

Private WithEvents label_ As MSForms.label
Private WithEvents spinbutton_ As MSForms.spinbutton

Private container_  As Object

Public Sub Initialize(container As Object, top As Long, left As Long)
    
    Set spinbutton_ = container.Controls.Add("Forms.Spinbutton.1")
    Set label_ = container.Controls.Add("Forms.Label.1")
    
    With label_
        .left = left
        .top = top
        .Width = 25
    End With
    
    With spinbutton_
        .left = left + label_.Width
        .top = top
    End With
    
End Sub

Public Property Get Value() As Double
    Value = spinbutton_.value
End Property

Public Property Let Value(rhs As Double)
    spinbutton_.value = rhs
End Property

Public Property Let SmallChange(rhs As Long)
    spinbutton_.SmallChange = rhs
End Property

Public Property Let Max(rhs As Long)
    spinbutton_.Max = rhs
End Property

Public Property Let Min(rhs As Long)
    spinbutton_.Min = rhs
End Property

Private Sub spinbutton__Change()
    label_.Caption = spinbutton_.Value
End Sub

Then in the userform:

Dim spinners As Collection

Private Sub UserForm_Initialize()
    
    Dim x As Long
    
    Set spinners = New Collection
    Dim spinner As SpinComboControl
    
    For x = 1 To 3
        Set spinner = New SpinComboControl
        spinner.Initialize Me, 25 * x, 10
        spinner.Max = 50
        spinner.Min = 0
        spinners.Add spinner
    Next x
    
End Sub

 

[My Aswer Is This]

I have been monitoring this thread and I like using Multipages versus numerous Userforms.
But I'm curious how you do this:
You said earlier:

Not exactly; the Left & Right cursor arrows will move from MultiPage Tab to MultiPage Tab.
Do you mean when you click on your keyboard left right arrow keys you can move back and forth from one Multipage page to the next page.

If so show me the code how you have this working. I'm not able to do that.

This really sounds like a very big project your working on.

Are you sure you need hundreds of Option Buttons and other controls?

Or do you think you may like to explain what your overall object is here and maybe we could suggest another way to do things. Just wondering. Not trying to tell you how to do things.

​

 

[Rufus Clupea]

I have been monitoring this thread and I like using Multipages versus numerous Userforms.
But I'm curious how you do this:
You said earlier:

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

Do you mean when you click on your keyboard left right arrow keys you can move back and forth from one Multipage page to the next page.

If so show me the code how you have this working. I'm not able to do that.
​

No coding was required. When the UserForm is running, if/when you click on one of the Page Tabs, the left/right arrow keys move back/forth through the Pages. From there, you can tab(key) to your first TabStop on the Page.

FWIW, I'm using Excel 2016; I don't know if MultiPages function the same way in earlier editions.

 

[Rufus Clupea]

Join the Label to the Spin Button by grouping them. The spin button will still work as needed. I know, you'll still have to manage the labels, but unless you're changing those on the fly, it doesn't seem to big a deal. You could name the label the same as the spinbutton but add ".txt" to the name.

Unfortunately(?), changing some SpinButton Labels is exactly what I have to do. At one time, I split things into 2 Labels per SpinButton; one for a description of what the control represented, and a 2nd for a value (either SpinButton.Value or some value derived from SpinButton.Value). I've since changed that to one Label per SpinButton, with the value at the far right of the string, and either stripping off the Right([Label], 3) characters, or changing them. Labels have the same numerical suffixes as the SpinButtons (which I'm also using as an argument to pass to the Handling/Processing Subs).

 

[Rufus Clupea]

I'd do it like the below....

Thank you, Kyle. It'll take me some time to digest/appreciate your suggestions (and get back with any questions ), but I'm sure they'll be worth it.

 

[My Aswer Is This]

You are correct. I thought I had tried this and it did not work. But when I tried it again today it does work like you said. Maybe I had never clicked on one of the Page tabs to begin with. Thanks. I guess I learned something new today.

No coding was required. When the UserForm is running, if/when you click on one of the Page Tabs, the left/right arrow keys move back/forth through the Pages. From there, you can tab(key) to your first TabStop on the Page.

FWIW, I'm using Excel 2016; I don't know if MultiPages function the same way in earlier editions.

 

[Jeffrey Mahoney]

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?

I have a couple of projects that I had considered complex and large. Nothing near what you have. I used to create more "Form" based applications because I thought they were easier to control and present data. I have changed my mind over the last couple years. I absolutely believe there is more flexibility in creating and maintaining "Worksheet Forms". I do use some forms with listboxes to support my worksheet forms, but mostly, my database control is on the worksheet. I find that I can make a more professional looking form using the inherent formatting features of ActiveX controls and Symbols in cells. Spinners and other control values can be linked directly to cells. Controls can be told to move and resize based on cell width and height so if I rearrange the form by inserting rows or columns, my controls move accordingly.

I'd like to know what other people think about that and if they disagree or agree.


Jeff

 

[My Aswer Is This]

Jeffrey. 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? Or do you use a differnt sheet instead of a different Multipage page?
With a Userform Multipage you can hide and unhide some controls by just clicking on a different Userform Multipage Page.

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

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?

I have a couple of projects that I had considered complex and large. Nothing near what you have. I used to create more "Form" based applications because I thought they were easier to control and present data. I have changed my mind over the last couple years. I absolutely believe there is more flexibility in creating and maintaining "Worksheet Forms". I do use some forms with listboxes to support my worksheet forms, but mostly, my database control is on the worksheet. I find that I can make a more professional looking form using the inherent formatting features of ActiveX controls and Symbols in cells. Spinners and other control values can be linked directly to cells. Controls can be told to move and resize based on cell width and height so if I rearrange the form by inserting rows or columns, my controls move accordingly.

I'd like to know what other people think about that and if they disagree or agree.


Jeff

 

[Kyle123]

Additionally, ActiveX controls on worksheets are asking for trouble - give it a google to see how many people have issues. You'd be much better off using forms controls, or shapes if you want more control over formatting.