Have the following code:
Userform: UserForm1
Dim Coll_txtBoxItems As Collection
Private Sub UserForm_Initialize()
Dim txtBoxQuantity As MSForms.TextBox
Dim txtBoxUnitPrice As MSForms.TextBox
Set Coll_txtBoxItems = New Collection
For i = 0 To 4
Set txtBoxQuantity = Me.Controls.Add("Forms.TextBox.1", "txtBoxQuantity" & i + 1, True)
With txtBoxQuantity
.Top = 620 + (i * 22)
.Width = 72
.Height = 18
.Left = 6
.ZOrder (0)
.SpecialEffect = fmSpecialEffectFlat
.TextAlign = fmTextAlignRight
.BackColor = &HFFFF&
.BorderStyle = fmBorderStyleSingle
.Value = "0.00"
End With
Set clsObject = New clsObjHandler
Set clsObject.SetTextBox = txtBoxQuantity
Coll_txtBoxItems.Add clsObject
Set txtBoxUnitPrice = Me.Controls.Add("Forms.TextBox.1", "txtBoxUnitPrice" & i + 1, True)
With txtBoxUnitPrice
.Top = 620 + (i * 22)
.Width = 78
.Height = 18
.Left = 558
.ZOrder (0)
.SpecialEffect = fmSpecialEffectFlat
.TextAlign = fmTextAlignRight
.BackColor = &HFFFF&
.BorderStyle = fmBorderStyleSingle
.Value = "$0.00"
End With
Set clsObject = New clsObjHandler
Set clsObject.SetTextBox = txtBoxUnitPrice
Coll_txtBoxItems.Add clsObject
End Sub
Class: clsObjHandler
Option Explicit
Private WithEvents pTxtBox As MSForms.TextBox
Public Property Get txtBox() As MSForms.TextBox
Set txtBox = pTxtBox
End Property
Public Property Set SetTextBox(ByRef txtBox As MSForms.TextBox)
Set pTxtBox = txtBox
End Property
Private Sub ptxtBox_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
Dim i As Integer
Dim ctrl_i As Integer
Dim ctrl As Control
Dim ctrl_name As String
Dim Quantity As Double
Dim UnitPrice As Currency
If KeyCode = 13 Then
ctrl_name = Mid(pTxtBox.Name, 1, Len(pTxtBox.Name) - 1)
If ctrl_name = "txtBoxQuantity" Then
pTxtBox.Value = Format(CDbl(pTxtBox.Value), "0.00")
Quantity = pTxtBox.Value
ctrl_i = Mid(pTxtBox.Name, Len(pTxtBox.Name), 1)
For i = 0 To UserForm1.Controls.Count - 1
Set ctrl = UserForm1.Controls.Item(i)
If ctrl.Name = "txtBoxUnitPrice" & ctrl_i Then
UnitPrice = ctrl
Exit Sub
End If
Next i
End If
End Sub
Have 5 lines of
Quantity Unit Price Item Price
Name of textbox fields on each line are:
txtBoxQuantity1 txtBoxUnitPrice1 txtBoxItemPrice1
txtBoxQuantity2 txtBoxUnitPrice2 txtBoxItemPrice2
txtBoxQuantity3 txtBoxUnitPrice3 txtBoxItemPrice3
txtBoxQuantity4 txtBoxUnitPrice4 txtBoxItemPrice4
txtBoxQuantity5 txtBoxUnitPrice5 txtBoxItemPrice5
Am using Keydown Event to determine when the user hits the Return key so that the Quantity value isn't used until the full value has been entered. Can't figure out how to get UnitPrice from the Control Collection so that I can then multiple the Quantity * Unit Price for the Row to calculate Item Price for the Row.
Thanks
Bookmarks