时间戳

首页 > 解决方案 > 使用用户表单文本框更新数据

excel - 使用用户表单文本框更新数据

问题描述

我有一个用户表单来从一系列表中添加或删除库存。

问题是当来自用户表单的信息仅添加到“已接收”表时，数量单元格未被确认。它已填充，但没有被我现有的表格公式拾取。

我可以到表格并手动输入数量并在数据选项卡上点击全部刷新，数量会正确更新。

我在每张表上都有一个数据透视表设置，以从“已接收”表中获取输入日期、材料和数量，通过用户表单输入的产品显示数量为零。*

我尝试了几种转换器和格式更改，但仍然没有得到结果。

Cdbl、Cint、Cdec、Format()、相关 Excel 表中的更改格式

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

当按下添加按钮时，接收表中的数量单元格被填充。将数据放入表中不会出现这种情况。

问题是公式看不到表单输入该单元格的数据。所有其他单元格数据都会填充，并且可以通过我用来测试它的各种其他公式看到。它只是数量单元格。

标签： excelvba

解决方案

这就是我怀疑正在发生的事情：TextBox用户表单上的输入 es 包含String. 这就是他们所能容纳的一切。您试图在此处将该字符串转换为数字：

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

但是随后您将该Decimal实例传递回TextBox.Value，隐式将其转换回String.

所以，你的想法是对的，但我认为你是在错误的地方做的。尝试在这里投射：

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

请注意，这已经是您正在使用的Me.DateBox：

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

您可能可以删除_AfterUpdate事件处理程序并将文本框值转储到工作表中。根据需要投射它们，并在需要时将格式直接应用于单元格。

推荐阅读