Mutiple Event Handling (SpinButton/TextBoxes)

[Rufus Clupea]

Hi Folks,

New User; new to VBA. Retired, working on an app for personal use.
Main problem is, I don't know what I don't know.

I've checked the couple of books I bought (which helped some) and searched online (including here) with mixed results. I'll pare down my examples for brevity/clarity.

I'm working on a UserForm with several TextBox/SpinBox pairs. The initial code (below) works fine for my purposes (the SpinButtons all have .Min. = 1; .Max = 20)

----------------------------------------------
Private Sub SpinButton1_Change()

TextBox1.Value = SpinButton1.Value

End Sub
----------------------------------------------
Private Sub TextBox1_Change()

If TextBox1.Value < SpinButton1.Min Then _
TextBox1.Value = SpinButton1.Min

If TextBox1.Value > SpinButton1.Max Then _
TextBox1.Value = SpinButton1.Max

SpinButton1.Value = TextBox1.Value

End Sub
----------------------------------------------

The problem comes when I try to use a Sub to handle any of the several TextBox changes. Between the few books I have on VBA, and some online searching, I came up with the following Sub, which I thought would work, as it's (to me) essentially the same.

-----------------------------------------------------------------------
Private Sub TextBox1_Change()

ATT_Change AA, 1
TextBox1.Value = AA

End Sub
-----------------------------------------------------------------------
Private Sub ATT_Change(ATT As Integer , i As Integer)

If Controls("TextBox" & i).Value < Controls("SpinButton" & i).Min Then _
Controls("TextBox" & i).Value = Controls("SpinButton" & i).Min

If Controls("TextBox" & i).Value > Controls("SpinButton" & i).Max Then _
Controls("TextBox" & i).Value = Controls("SpinButton" & i).Max

Controls("SpinButton" & i).Value = Controls("TextBox" & i).Value
ATT = Controls("TextBox" & i).Value

End Sub
-----------------------------------------------------------------------

For some reason I can't (yet) discern, clicking any SpinBox either way (from a value of 10) yields a value of 20 (SpinBox.Max) and sticks there.

Editing a TextBox directly yields the same result.

I've obviously fouled up someplace, but I can't find/see it.

Any help/guidance/pointing me in the right direction appreciated. TYA!

 

[DanteAmor]

Try this

Note: This line has to be at the beginning of all your code (general declarations)

Dim wChange As Boolean

Dim wChange As Boolean


Private Sub SpinButton1_Change()
    TextBox1.Value = SpinButton1.Value
End Sub
Private Sub TextBox1_Change()
    ATT_Change 1
End Sub
Private Sub SpinButton2_Change()
    TextBox2.Value = SpinButton2.Value
End Sub
Private Sub TextBox2_Change()
    ATT_Change 2
End Sub
Private Sub SpinButton3_Change()
    TextBox3.Value = SpinButton3.Value
End Sub
Private Sub TextBox3_Change()
    ATT_Change 3
End Sub


Private Sub ATT_Change(i As Integer)
    If wChange = True Then Exit Sub
    wChange = True
    If Val(Controls("TextBox" & i).Value) < Controls("SpinButton" & i).Min Then
        Controls("TextBox" & i).Value = Controls("SpinButton" & i).Min
    End If
    If Val(Controls("TextBox" & i).Value) > Controls("SpinButton" & i).Max Then
        Controls("TextBox" & i).Value = Controls("SpinButton" & i).Max
    End If
    Controls("SpinButton" & i).Value = Controls("TextBox" & i).Value
    wChange = False
End Sub

 

[Norie]

When do you want the sub ATT_Change to be triggered?

Is it when the spin buttons change or the textboxes change?

 

[DanteAmor]

If you have many textboxes

do the following

1. Create a new Class Module name Class1.

2. Put this code in the Class1 module.

Option Explicit
Public WithEvents tbxCustom1 As MSForms.TextBox     'Custom Textbox
Public WithEvents tbxCustom2 As MSForms.SpinButton  'Custom SpinButton
'
Private Sub tbxCustom1_Change()
    Call UserForm1.ATT_Change(Replace(tbxCustom1.Name, "TextBox", ""), "t")
End Sub
Private Sub tbxCustom2_Change()
    Call UserForm1.ATT_Change(Replace(tbxCustom2.Name, "SpinButton", ""), "s")
End Sub

3. Put this code in the userform, to load all the textbox and spinbutton. When you modify any textbox or spinbutton, the macro "ATT_Change" will be activated

Dim wChange As Boolean
Dim colTbxs As Collection 'Collection Of Custom Textboxes
Dim colSpin As Collection 'Collection Of Custom Spinbutton
'
Private Sub UserForm_Initialize()
'
    Dim ctlLoop As MSForms.Control
    Dim clsObj1 As Class1
    Dim clsObj2 As Class1
    '
    Set colTbxs = New Collection
    Set colSpin = New Collection
    For Each ctlLoop In Me.Controls
        If TypeOf ctlLoop Is MSForms.TextBox Then
            Set clsObj1 = New Class1
            Set clsObj1.tbxCustom1 = ctlLoop
            colTbxs.Add clsObj1
        End If
        If TypeOf ctlLoop Is MSForms.SpinButton Then
            Set clsObj2 = New Class1
            Set clsObj2.tbxCustom2 = ctlLoop
            colSpin.Add clsObj2
        End If
    Next ctlLoop
End Sub


Sub ATT_Change(i As Integer, ctrl As String)
    If wChange = True Then Exit Sub
    wChange = True
    If Val(Controls("TextBox" & i).Value) < Controls("SpinButton" & i).Min Then
        Controls("TextBox" & i).Value = Controls("SpinButton" & i).Min
    End If
    If Val(Controls("TextBox" & i).Value) > Controls("SpinButton" & i).Max Then
        Controls("TextBox" & i).Value = Controls("SpinButton" & i).Max
    End If
    If ctrl = "s" Then Controls("TextBox" & i).Value = Controls("SpinButton" & i).Value
    If ctrl = "t" Then Controls("SpinButton" & i).Value = Controls("TextBox" & i).Value
    wChange = False
End Sub

 

[Rufus Clupea]

When do you want the sub ATT_Change to be triggered?

Is it when the spin buttons change or the textboxes change?

Originally, the SpinButton Sub worked fine, as did the TextBox_Change Sub. It's when I tried creating a "generic" (probably not the best/correct term) Sub to handle the TextBox changes for any of the TextBoxes that things went awry. Why clicking the SpinButton changes the value to .Max, I don't know/understand, unless clicking the SpinButton also triggers a change event in the associated TextBox. I don't recall reading that anywhere, but if that's the case, I still don't understand why things work in individual TextBox_Change events (I hope that makes sense).

In the meantime, I'll try the suggested solutions...

 

[Rufus Clupea]

Dante,

First, Thank You again for the help.

Re: Solution 1 (Post #2):
This seems to work adequately for my purposes. Please forgive my "noobeté" (I'm learning VBA as I go, and it's been forty-mumble yrs. since I've coded anything); I'm a little foggy on the purpose/use of the Boolean wChange, and why the Val() function need be called on properties (.Value, .Min, .Max) that are numerical already(?)

I DO like the way you've gotten around passing a second argument to Sub ATT_Change; I'm a big fan of elegance in coding (when I can understand it ).

If I could impose once more, any thoughts on why my original Sub doesn't work as written (where'd I mess up?) so I can learn/understand for the future?

Re: Solution 2 (Post #4):
I'm currently working with 6 paired SpinButton/TextBoxes. I'm afraid I'll have to save this approach until I've learned more, and can understand/appreciate it.

 

[DanteAmor]

In this part:

Private Sub ATT_Change(i As Integer)
    If wChange = True Then Exit Sub
    wChange = True
    If Val(Controls("TextBox" & i).Value) < Controls("SpinButton" & i).Min Then
        Controls("TextBox" & i).Value = Controls("SpinButton" & i).Min
    End If
    If Val(Controls("TextBox" & i).Value) > Controls("SpinButton" & i).Max Then
        Controls("TextBox" & i).Value = Controls("SpinButton" & i).Max
    End If
    Controls("SpinButton" & i).Value = Controls("TextBox" & i).Value
    wChange = False
End Sub

The lines
Controls("TextBox" & i).Value = Controls("SpinButton" & i).Min
Controls("TextBox" & i).Value = Controls("SpinButton" & i).Max

... modify the textbox, this activates the textbox_change event again, when the change event is activated, the ATT_Change Sub routine is run for the second time, but as the variable wChange = True then the routine ends (in the second time), but the primary execution continues.
You can see this process if you execute step by step with F8