Module, UserForm, & MultiPage Managment

Rufus Clupea · Mar 14, 2019

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...

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)?
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?
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 · Mar 17, 2019

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:

Agreed! Now add the wingding check boxes. You can have a controlling check box at the top to select/deselect all the rows. You can copy a Checked box cell and copy it down to any number of other check box cells to toggle status.

Jeff

My Aswer Is This · Mar 17, 2019

Explain how you do this:
You said:

Agreed! Now add the wingding check boxes

I assume you have to use the font wingdings
But what changes it from checked to un checked

Jeffrey Mahoney said:
Agreed! Now add the wingding check boxes. You can have a controlling check box at the top to select/deselect all the rows. You can copy a Checked box cell and copy it down to any number of other check box cells to toggle status.

Jeff

Jeffrey Mahoney · Mar 17, 2019

When a user double clicks one of the cells in the named range "HighRiskBlind" then this SUB will toggle the wingding character. All my wingding checkboxes are usually in one named range.
* þ = Checked
* o = Not Checked

Code:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
   
  If Not Intersect(Target, Range("HighRiskBlind_hdr")) is Nothing Then
     Cancel = True
     If Target.Value = "o" Then                         'Toggle checkbox
      Target.Value = "þ"
    ElseIf Target.Value = "þ" Or Target.Value = "" Then
      Target.Value = "o"
    End If  
  End If   
    
End Sub

This was the simple code. If you wanted code that also managed a controlling check box, I could post it. This was easier to explain.

My Aswer Is This · Mar 17, 2019

Yes I see how this works. Now if this saves the user any work may be questionable. He would then still have to insert all his Spin Buttons he seems to want to use and any other controls he would need. And then use several sheets instead of numerous MultiPages.
But at least now he will see another possible way to do things.

I personally like UserForms.
But maybe that is because I have never tried your sheet Form approach.

Jeffrey Mahoney said:
When a user double clicks one of the cells in the named range "HighRiskBlind" then this SUB will toggle the wingding character. All my wingding checkboxes are usually in one named range.
* þ = Checked
* o = Not Checked

Code:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) If Not Intersect(Target, Range("HighRiskBlind_hdr")) is Nothing Then Cancel = True If Target.Value = "o" Then 'Toggle checkbox Target.Value = "þ" ElseIf Target.Value = "þ" Or Target.Value = "" Then Target.Value = "o" End If End If End Sub

This was the simple code. If you wanted code that also managed a controlling check box, I could post it. This was easier to explain.

Rufus Clupea · Mar 18, 2019

My Aswer Is This said:
Looks like you know a lot about Excel Vba

I think you may be grossly overestimating my knowledge level!

I'm just learning this stuff, really. I'm guessing it's going to take me a while to dissect Kyle's code from post #12 enough to understand it (which is fine!

).

The concept of object-oriented programming is still new to me, and I don't feel like I have a comfortable handle on it yet (though it's getting better). It kind of freaks me out when I see/read something like:

"Notice that I wrote Cells property, not Cells object or even Cells collection. Although Cells may seem like an object (or a collection), it's really not. .... If this seems strange, don't worry. Even Microsoft appears to be confused about this issue."
— Excel VBA Programming for Dummies

Say Whaaaaa???

Do you have a question for us we have not not answered?

OTTOMH, no, but like the old weather adage goes, stick around—it'll change!
(I'll post as issues arise...

)

Thanks again to all of you!

Rufus Clupea · Mar 18, 2019

My Aswer Is This said:
Do you have a question for us we have not not answered?

One occurred to me overnight (well, really more of an idea to bounce off you folks than a question, but I expect questions to arise)...

Let's see if I can explain this...

Would it make sense to write a single "universal" event handling Sub that would figure out what kind of Control it had been sent, and then (perhaps via Select Case) send the pertinent information to Subs for processing the respective Controls? This would (theoretically) allow me to use one Control naming/numbering scheme rather than different naming/numbering for each flavor of Control.

This is an outline to help convey the idea―not intended as actual code:

Code:

[COLOR=#000080]Option Explicit
Option Base 1[/COLOR]

[COLOR=#006400]'*******************'
'   DECLARATIONS    '
'*******************'[/COLOR]

[COLOR=#000080]Public [/COLOR]Num_Controls [COLOR=#000080]As Long[/COLOR]

[COLOR=#000080]Public [/COLOR]Kontrol_Values(1 To Num_Controls) [COLOR=#000080]As Long[/COLOR]
[COLOR=#006400]'
'-----------------------------------------------[/COLOR]
[COLOR=#000080]Private Sub[/COLOR] UserForm1_Initialize()
    Num_Controls = 3
[COLOR=#000080]End Sub[/COLOR] [COLOR=#006400]'UserForm1_Initialize()[/COLOR]
[COLOR=#006400]'-----------------------------------------------
'   OptionButton[/COLOR]
[COLOR=#000080]Private Sub[/COLOR] Kontrol1_Click()
    Process_Kontrol 1
[COLOR=#000080]End Sub[/COLOR]
[COLOR=#006400]'-----------------------------------------------
'   CheckBox[/COLOR]
[COLOR=#000080]Private Sub[/COLOR] Kontrol2_Click()
    Process_Kontrol 2
[COLOR=#000080]End Sub[/COLOR]
[COLOR=#006400]'-----------------------------------------------
'   SpinButton[/COLOR]
[COLOR=#000080]Private Sub[/COLOR] Kontrol3_Change()
    Process_Kontrol 3
[COLOR=#000080]End Sub[/COLOR]
[COLOR=#006400]'-----------------------------------------------
[/COLOR]
[COLOR=#000080]Private Sub[/COLOR] Process_Kontrol(index [COLOR=#000080]As Long[/COLOR])
    [COLOR=#000080]Select Case[/COLOR] Control
        [COLOR=#000080]Case Is[/COLOR] = "OptionButton"
       [COLOR=#006400] '   Send to Process_OptionButton[/COLOR]
        [COLOR=#000080]Case Is[/COLOR] = "CheckBox"
        [COLOR=#006400]'   Send to Process_CheckBox[/COLOR]
        [COLOR=#000080]Case Is[/COLOR] = "SpinButton"
        [COLOR=#006400]'   Send to Process_SpinButton[/COLOR]
    [COLOR=#000080]End Select
End Sub[/COLOR]

I don't know (yet) how to determine the type of Control via code, but I'm guessing there must be some way, as the VBE knows―it's in the ComboBox at the top of the Properties window (some kind of Read-Only Property?)

By all means, shoot this idea down if it's a bad one, but please explain why, so I can learn from it.

TIA

Kyle123 · Mar 18, 2019

Did you try the code I gave you? The solution you've come up with is actually more complex than the code I posted

My Aswer Is This · Mar 18, 2019

If I understand your code correctly. This code is creating the userform dynamically. Meaning everytime you open the userform it has to rebuild itself. Is that correct?

Kyle123 said:
Did you try the code I gave you? The solution you've come up with is actually more complex than the code I posted

Kyle123 · Mar 19, 2019

Sort of, the userform isn't being created dynamically - the code is just adding certain controls at runtime. Yes, this would run whenever the form is loaded - though you could just use a single instance of the userform and never let it go out of scope.

Additionally, this isn't an all in or nothing approach. If you'd still rather lay out the controls manually, you can still benefit from not duplicating code, with a small tweak, the class will accept existing controls:

Code:

Public WithEvents label_ As MSForms.Label
Public WithEvents spinbutton_ As MSForms.spinbutton

Public Sub Initialize(spinner As MSForms.spinbutton, lbl As MSForms.Label)
    
    Set spinbutton_ = spinner
    Set label_ = lbl

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 all one needs to do in the userform initialize is:

Code:

Dim hndlrs As Collection

Private Sub UserForm_Initialize()
    
    Dim ev As SpinComboControl
    Set hndlrs = New Collection
    
    For x = 1 To 2
        Set ev = New SpinComboControl
        ev.Initialize Me.Controls("Spinbutton" & x), Me.Controls("Label" & x)
        hndlrs.Add ev
    Next x
    
End Sub

This removes the duplication whilst compartmentalising code

Rufus Clupea · Mar 19, 2019

Kyle123 said:
Did you try the code I gave you? The solution you've come up with is actually more complex than the code I posted

I'm sorry, but my VBA skills aren't quite up to where I can follow it yet.

That's not on you; it's just going to take me a bit longer than I guesstimated to figure it out.
------------
Later...

I did put your code into a test program, and I think I got it to run, but when I close the UserForm to see what's going on, the Controls are gone. I'm guessing that this is what My Aswer Is This was talking about(?) when s/he asked,

If I understand your code correctly. This code is creating the userform dynamically. Meaning everytime you open the userform it has to rebuild itself. Is that correct?

As it runs, I'm seeing 3 SpinButtons on the UserForm, indented slightly from the left border. Clicking on the SpinButtons displays a numerical value between the SpinButtons and the left border of the UserForm, but as there isn't much space there, I can't tell if there is (or is supposed to be) anything else displayed. From what I can make out of the code, I think this is correct(?)

Not sure what this line in the UserForm Module does/means:

spinner.Initialize Me, 25 * x, 10

It appears that your code may be able to do what I asked about (it appears quite elegant & adaptable―cool!

), but until I can understand it (again, that's on me―not you

) I'm a tad reluctant to (try to) utilize it.

But back to my question

(paraphrasing),

Would it make sense to write a single "universal" event handling Sub that would figure out what kind of Control it had been sent .... allowing me to use one Control naming/numbering scheme rather than different naming/numbering for each flavor of Control?

Module, UserForm, & MultiPage Managment

Rufus Clupea

Board Regular

Jeffrey Mahoney

Well-known Member

Excel Facts

My Aswer Is This

Well-known Member

Jeffrey Mahoney

Well-known Member

My Aswer Is This

Well-known Member

Rufus Clupea

Board Regular

Rufus Clupea

Board Regular

Kyle123

Well-known Member

My Aswer Is This

Well-known Member

Kyle123

Well-known Member

Rufus Clupea

Board Regular

Similar threads

Forum statistics

Share this page

Module, UserForm, & MultiPage Managment

Board Regular

Well-known Member

Excel Facts

Well-known Member

Well-known Member

Well-known Member

Board Regular

Board Regular

Well-known Member

Well-known Member

Well-known Member

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock