excel - 用户窗体:在文本框中继续运行总计
问题描述
每次在我的用户表单中更新列表框时,我都希望更新文本框值。我有两个用户表单,用户可以在它们之间来回移动值)。我希望每次值在用户表单之间来回移动时更新文本框值。这是我下面的代码。
Private Sub btnMoveLeft_Click()
Dim iCtr As Long
For iCtr = 0 To Me.lstAssigned.ListCount - 1
If Me.lstAssigned.Selected(iCtr) = True Then
Me.lstUnassigned.AddItem Me.lstAssigned.List(iCtr)
lookValue = WorksheetFunction.VLookup(Me.lstUnassigned(iCtr), Sheets("Pivot").Range("A:J"), 10, False)
txtAC.Value = txtAC.Value - lookValue
End If
Next iCtr
For iCtr = Me.lstAssigned.ListCount - 1 To 0 Step -1
If Me.lstAssigned.Selected(iCtr) = True Then
Me.lstAssigned.RemoveItem iCtr
End If
Next iCtr
End Sub
Private Sub btnMoveRight_Click()
Dim iCtr As Long
For iCtr = 0 To Me.lstUnassigned.ListCount - 1
If Me.lstUnassigned.Selected(iCtr) = True Then
Me.lstAssigned.AddItem Me.lstUnassigned.List(iCtr)
lookValue = WorksheetFunction.VLookup(Me.lstAssigned.List(iCtr), Sheets("Pivot").Range("A:J"), 10, False)
txtAC.Value = txtAC.Value + lookValue
End If
Next iCtr
For iCtr = Me.lstUnassigned.ListCount - 1 To 0 Step -1
If Me.lstUnassigned.Selected(iCtr) = True Then
Me.lstUnassigned.RemoveItem iCtr
End If
Next iCtr
End Sub
我目前收到的错误是当它试图抓取lookValue时,错误显示“无法获取列表属性。无效的属性数组索引。” 任何帮助将不胜感激,非常感谢。
解决方案
在很多情况下,最好使用中间变量而不是重复执行相同的操作。在这种情况下,您的问题是Me.lstAssigned.List(iCtr)
检索当前选定的列表项的操作。通过使用中间变量,我能够让您的代码正常工作并从该列表框中删除该项目,而无需第二次循环。
Private Sub btnMoveLeft_Click()
Dim iCtr As Long
Dim selectedValue As Variant
For iCtr = 0 To Me.lstAssigned.ListCount - 1
If Me.lstAssigned.Selected(iCtr) = True Then
selectedValue = Me.lstAssigned.List(iCtr)
Me.lstUnassigned.AddItem selectedValue
lookValue = WorksheetFunction.VLookup(selectedValue, _
Sheets("Pivot").Range("A:J"), 10, False)
txtAC.Value = txtAC.Value - lookValue
Me.lstAssigned.RemoveItem iCtr
End If
Next iCtr
End Sub
Private Sub btnMoveRight_Click()
Dim iCtr As Long
Dim selectedValue As Variant
For iCtr = 0 To Me.lstUnassigned.ListCount - 1
If Me.lstUnassigned.Selected(iCtr) = True Then
selectedValue = Me.lstUnassigned.List(iCtr)
Me.lstAssigned.AddItem selectedValue
lookValue = WorksheetFunction.VLookup(selectedValue, _
Sheets("Pivot").Range("A:J"), 10, False)
txtAC.Value = txtAC.Value + lookValue
Me.lstUnassigned.RemoveItem iCtr
End If
Next iCtr
End Sub
推荐阅读
- python - 为什么命令及其参数必须在 subprocess.Popen 的列表中?
- react-native - CreateMaterialTopTabNavigator 如何添加第三个选项卡
- javascript - 从每个语句中迭代超过 4 个随机响应
- ruby - 使用 RSpec 上传图像在控制器上有意外的类
- node.js - 更新数据库中的现有用户数据
- excel - 在vba中查找下一个序列号
- php - 使用 PHP 向 SQL Server 插入多个插入行的最佳方法
- google-app-engine - 如何在 GCP Pub/sub 中修复来自推送订阅的多条消息
- angular - 防止来自 NGRX 影响的冗余 API 调用
- php - 将行数组中的值传递给另一个变量名?