Rufus Clupea
Board Regular
- Joined
- Feb 11, 2019
- Messages
- 85
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!
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!
