Update data with Userform textbox

Question

I have a userform to add or remove inventory from a series of tables.

The issue is when the info from the userform is added to ONLY the "Received" table the quantity cell is not acknowledged. It is populated but is not picked up by the table formulas I have in place.

I can got to the table and manually type the quantities in and hit refresh all on the data tab and the quantities update correctly.

I have a pivot table setup on each of these sheets to get the dates of entry, material, and quantity from the "Received" table the product that was entered via the userform shows a zero for the quantity.*

I have tried several converters and format changes but I am still not getting results.

Cdbl, Cint, Cdec, Format(), Changed formats in the Excel Table in question

Private Sub Add_Button_Click()
MsgBox "Are You Sure You Want To Add To Inventory?"
Unload Me
Transaction_Form.Show

Dim the_sheet As Worksheet
Dim table_list_object As ListObject
Dim table_object_row As ListRow

Set the_sheet = Sheets("MATERIALS REC.")
Set table_list_object = the_sheet.ListObjects(1)
Set table_object_row = table_list_object.ListRows.Add

table_object_row.Range(1, 1).Value = CDate(Me.Date_Box)
table_object_row.Range(1, 2).Value = Me.Material_Code_Box
table_object_row.Range(1, 4).Value = Me.Transaction_Qty_Box
table_object_row.Range(1, 5).Value = Me.Mat_Price_Box
table_object_row.Range(1, 6).Value = Me.cboSupplier_List
table_object_row.Range(1, 7).Value = Me.Order_Date_Box

End Sub


Private Sub cboSupplier_List_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
HookListBoxScroll Me, cboSupplier_List
End Sub


Private Sub Date_Box_AfterUpdate()
On Error Resume Next
Me.Date_Box.Value = CDate(Me.Date_Box)
End Sub


Private Sub Mat_Price_Box_AfterUpdate()
On Error Resume Next
Me.Mat_Price_Box.Value = Format(Me.Mat_Price_Box.Value, "$####.##")
End Sub


Private Sub Material_Code_Box_Change()
On Error Resume Next
Me.Material_Code_Box.Value = StrConv(Me.Material_Code_Box.Value, vbUpperCase)
End Sub

Private Sub Next_Button_Click()
MsgBox "The Transaction Form Will Be Closed"
Unload Me
End Sub


Private Sub Order_Date_Box_AfterUpdate()
On Error Resume Next
Me.Order_Date_Box.Value = CDate(Me.Order_Date_Box)
End Sub    


Private Sub Overide_Button_Click()
MsgBox "Are You Sure You Want To Overide"
rspn = InputBox("Enter Password")
If rspn <> "ENVenv11" Then
MsgBox "Wrong password"
End If

Unload Me
Transaction_Form.Show

Dim the_sheet As Worksheet
Dim table_list_object As ListObject
Dim table_object_row As ListRow

Set the_sheet = Sheets("MATERIAL USAGE")
Set table_list_object = the_sheet.ListObjects(1)
Set table_object_row = table_list_object.ListRows.Add

table_object_row.Range(1, 1).Value = Me.Date_Box
table_object_row.Range(1, 4).Value = Me.Material_Code_Box
table_object_row.Range(1, 6).Value = Me.Transaction_Qty_Box
table_object_row.Range(1, 3).Value = Me.Process_Box
table_object_row.Range(1, 2).Value = Me.Customer_Box

End Sub


Private Sub Today_Button_Click()
Date_Box.Value = Date    
End Sub


Private Sub Transaction_Qty_Box_AfterUpdate()
On Error Resume Next
Me.Transaction_Qty_Box.Value = CDec(Me.Transaction_Qty_Box.Value)
End Sub


Private Sub Use_Button_Click()
MsgBox "Are You Sure You Want To Remove From Inventory"
Unload Me
Transaction_Form.Show

Dim the_sheet As Worksheet
Dim table_list_object As ListObject
Dim table_object_row As ListRow

Set the_sheet = Sheets("MATERIAL USAGE")
Set table_list_object = the_sheet.ListObjects(1)
Set table_object_row = table_list_object.ListRows.Add

table_object_row.Range(1, 1).Value = Me.Date_Box
table_object_row.Range(1, 4).Value = Me.Material_Code_Box
table_object_row.Range(1, 6).Value = Me.Transaction_Qty_Box
table_object_row.Range(1, 3).Value = Me.Process_Box
table_object_row.Range(1, 2).Value = Me.Customer_Box

End Sub


Private Sub UserForm_Click()

End Sub


Private Sub UserForm_Initialize()
'Populate Supplier combo box.

Dim rngSupplier As Range
Dim ws As Worksheet

Set ws = Worksheets("Lookup_Lists")

For Each rngSupplier In ws.Range("Suppliers")
Me.cboSupplier_List.AddItem rngSupplier.Value
Next rngSupplier

End Sub

When the add button is pushed the quantity cell in the Received table is populated. This happens no issues with it putting data in the table.

The issue is the formulas do not see the data that the form enters into that cell. All other cell data populates and can be seen by various other formulas I have used to test it. It is just the quantity cell.

Answer 1

Here's what I suspect is happening: The input TextBoxes on your userform contain String. That's all they can ever contain. You're attempting to cast that string as numeric here:

Private Sub Transaction_Qty_Box_AfterUpdate()

    On Error Resume Next
    Me.Transaction_Qty_Box.Value = CDec(Me.Transaction_Qty_Box.Value)

    'Confirm my suspicions:
    MsgBox TypeName(Me.Transaction_Qty_Box.Value)
End Sub

But then you're passing that Decimal instance back into the TextBox.Value, which implicitly converts it back to String.

So, you've got the right idea, but you're doing it in the wrong place, I think. Try casting here instead:

table_object_row.Range(1, 6).Value = CDec(Me.Transaction_Qty_Box)

Note that this is already what you're doing with the Me.DateBox:

table_object_row.Range(1, 1).Value = CDate(Me.Date_Box)

You can probably remove the _AfterUpdate event handlers and just dump the textbox values to the sheet. Cast them as needed, and apply formatting directly to the cell if needed.