Originally Posted by
onmyway
hi guys, thanks for your diligence!
Do you perhaps want to look at this challenge:
Add multiple filtered row data to ListBox
If that was supposed to be a link, I think it failed.
Not well tested, but this seems to work, blocking non-numeric entries and using a Class instance for each of the five 'child' textboxes. Please test in a junk/throwaway copy of your workbook...
In the Userform's Module:
Option Explicit
'// You could use a collection, but since how many controls we have seems unlikely to be a //
'// mystery, I don't know anything against sizing an array. //
Private cAT(1 To 5) As clsAssociatedTextboxes
Private Sub UserForm_Initialize()
Dim ctrl As MSForms.Control
Dim n As Long
n = 0 '<--- just for clarity in my pea brain...
For Each ctrl In Me.Controls
Select Case ctrl.Name
Case "txtStockSOS1", "txtStockReceive1", "txtStockLost1", "txtStockBroken1", "txtStockWorn1"
n = n + 1
Set cAT(n) = New clsAssociatedTextboxes
Set cAT(n).Parent = Me
Set cAT(n).ThisTextbox = ctrl
Case Else
' do nothing
End Select
Next
End Sub
In a Class named clsAssociatedTextboxes:
Option Explicit
Private ParentForm As Object
Private WithEvents TB As MSForms.TextBox
Private LastPosition1 As Long
Public Property Set Parent(p As Object)
Set ParentForm = p
End Property
Public Property Get Parent() As Object
Set Parent = ParentForm
End Property
Public Property Set ThisTextbox(tt As MSForms.TextBox)
Set TB = tt
End Property
Public Property Get ThisTextbox() As MSForms.TextBox
Set ThisTextbox = TB
End Property
Private Sub TB_Change()
Static LastText As String
Static SecondTime As Boolean
' Credit to Rick Rothstein at:
' http://www.mrexcel.com/forum/excel-q...al-values.html
'// In gist - eliminate non-numerical values from being entered, allow a '+' or '-' sign at //
'// the start and allow one decimal point. //
With ThisTextbox
If Not SecondTime Then
If .Text Like "*[!0-9.+-]*" Or .Text Like "*.*.*" Or .Text Like "?*[+-]*" Then
Beep
SecondTime = True
.Text = LastText
.SelStart = LastPosition1
Else
LastText = .Text
LastPosition1 = .SelStart
End If
End If
End With
SecondTime = False
'// After disallowing non-numeric values, update the control. CDbl() appears necessary to //
'// force adding (vs. concatenation). //
Parent.Controls("txtStockEOS1").Value = _
CDbl(IIf(Not Parent.Controls("txtStockSOS1").Value = vbNullString, Parent.Controls("txtStockSOS1").Value, 0)) + _
CDbl(IIf(Not Parent.Controls("txtStockReceive1").Value = vbNullString, Parent.Controls("txtStockReceive1").Value, 0)) + _
CDbl(IIf(Not Parent.Controls("txtStockLost1").Value = vbNullString, Parent.Controls("txtStockLost1").Value, 0)) + _
CDbl(IIf(Not Parent.Controls("txtStockBroken1").Value = vbNullString, Parent.Controls("txtStockBroken1").Value, 0)) + _
CDbl(IIf(Not Parent.Controls("txtStockWorn1").Value = vbNullString, Parent.Controls("txtStockWorn1").Value, 0))
End Sub
Private Sub TB_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
'// Record cursor position //
LastPosition1 = TB.SelStart
End Sub
Private Sub TB_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
'// Record cursor position //
LastPosition1 = TB.SelStart
End Sub
Hope that helps,
Mark
Reply With Quote
