你好，我一直在这个Excel用户表单上工作了一个月，现在我几乎完成了。但是我的代码中有一个bug，它没有显示任何错误，但它不允许我更新其余的列。
当我点击“查看列表”按钮时，电子表格中的数据将显示在列表框中。
如果我点击一个值，比如我点击“dfldasfjasldk”，它的行值将显示在ComboBox上，允许用户编辑信息。
图片：
当我点击“更新行”时，唯一更新的列是环境。其他的柱子都不管用。
Private Sub btnDelete_Click()
Dim a As Integer
If MsgBox("Are you sure you want to delete this row?", vbYesNo + vbQuestion, "Yes") = vbYes Then
For a = 1 To Range("A100000").End(xlUp).Row
If Cells(a, 1) = listHeader.List(listHeader.ListIndex) Then
Rows(a).Select
Selection.Delete
End If
Next a
End If
End Sub
Private Sub btnView_Click()
listHeader.RowSource = "A4:H200"
End Sub
Private Sub cmbAdd_Click()
Dim sheet As Worksheet
Set sheet = ThisWorkbook.Sheets("PRESTAGE DB")
nextrow = sheet.Cells(Rows.Count, 1).End(xlUp).Row + 1
sheet.Cells(nextrow, 1) = Me.cmbSchema
sheet.Cells(nextrow, 2) = Me.cmbEnvironment
sheet.Cells(nextrow, 3) = Me.cmbHost
sheet.Cells(nextrow, 4) = Me.cmbIP
sheet.Cells(nextrow, 5) = Me.cmbAccessible
sheet.Cells(nextrow, 6) = Me.cmbLast
sheet.Cells(nextrow, 7) = Me.cmbConfirmation
sheet.Cells(nextrow, 8) = Me.cmbProjects
MsgBox "Data Added!"
End Sub
Private Sub cmbClearFields_Click()
cmbSchema.Text = ""
cmbEnvironment.Text = ""
cmbHost.Text = ""
cmbIP.Text = ""
cmbAccessible.Text = ""
cmbLast.Text = ""
cmbConfirmation.Text = ""
cmbProjects.Text = ""
cmbSearch.Text = ""
End Sub
Private Sub cmbSearch_Change()
x = Sheets("PRESTAGE DB").Range("A" & Rows.Count).End(xlUp).Row
For y = 2 To x
If Sheets("PRESTAGE DB").Cells(y, 1).Text = cmbSearch.Value Then
cmbSchema.Text = Sheets("PRESTAGE DB").Cells(y, 1)
cmbEnvironment.Text = Sheets("PRESTAGE DB").Cells(y, 2)
cmbHost.Text = Sheets("PRESTAGE DB").Cells(y, 3)
cmbIP.Text = Sheets("PRESTAGE DB").Cells(y, 4)
cmbAccessible.Text = Sheets("PRESTAGE DB").Cells(y, 5)
cmbLast.Text = Sheets("PRESTAGE DB").Cells(y, 6)
cmbConfirmation.Text = Sheets("PRESTAGE DB").Cells(y, 7)
cmbProjects.Text = Sheets("PRESTAGE DB").Cells(y, 8)
UserForm1.listHeader.RowSource = "A" + CStr(y) + ": H" + CStr(y)
Exit For
End If
Next y
'Dim x As Long
'Dim y As Long
'x = Sheets("PRESTAGE DB").Range("A" & Rows.Count).End(xlUp).Row
'For y = 2 To x
'If Sheets("PRESTAGE DB").Cells(y, 1).Text = cmbSearch.Value Then
'cmbSchema.Text = Sheets("PRESTAGE DB").Cells(y, 1)
'cmbEnvironment.Text = Sheets("PRESTAGE DB").Cells(y, 2)
'cmbHost.Text = Sheets("PRESTAGE DB").Cells(y, 3)
'cmbIP.Text = Sheets("PRESTAGE DB").Cells(y, 4)
'cmbAccessible.Text = Sheets("PRESTAGE DB").Cells(y, 5)
'cmbLast.Text = Sheets("PRESTAGE DB").Cells(y, 6)
'cmbConfirmation.Text = Sheets("PRESTAGE DB").Cells(y, 7)
'cmbProjects.Text = Sheets("PRESTAGE DB").Cells(y, 8)
'End If
'Next y
End Sub
Private Sub cmbUpdate_Click()
Dim x As Long
Dim y As Long
x = Sheets("PRESTAGE DB").Range("A" & Rows.Count).End(xlUp).Row
For y = 2 To x
If Sheets("PRESTAGE DB").Cells(y, 1).Text = cmbSchema.Value Then
Sheets("PRESTAGE DB").Cells(y, 2) = cmbEnvironment.Value
Sheets("PRESTAGE DB").Cells(y, 3) = cmbHost.Value
Sheets("PRESTAGE DB").Cells(y, 4) = cmbIP.Value
Sheets("PRESTAGE DB").Cells(y, 5) = cmbAccessible.Value
Sheets("PRESTAGE DB").Cells(y, 6) = cmbLast.Value
Sheets("PRESTAGE DB").Cells(y, 7) = cmbConfirmation.Value
Sheets("PRESTAGE DB").Cells(y, 8) = cmbProjects.Value
Exit For
End If
Next y
'Dim x As Long
'Dim y As Long
'x = Sheets("PRESTAGE DB").Range("A" & Rows.Count).End(xlUp).Row
'For y = 2 To x
'If Sheets("PRESTAGE DB").Cells(y, 1).Text = cmbSchema.Value Then
'Sheets("PRESTAGE DB").Cells(y, 2) = cmbEnvironment
'Sheets("PRESTAGE DB").Cells(y, 3) = cmbHost
'Sheets("PRESTAGE DB").Cells(y, 4) = cmbIP
'Sheets("PRESTAGE DB").Cells(y, 5) = cmbAccessible
'Sheets("PRESTAGE DB").Cells(y, 6) = cmbLast
'Sheets("PRESTAGE DB").Cells(y, 7) = cmbConfirmation
'Sheets("PRESTAGE DB").Cells(y, 8) = cmbProjects
'End If
'Next y
End Sub
Private Sub CommandButton5_Click()
listHeader.RowSource = ""
End Sub
Private Sub listHeader_Click()
cmbSchema.Value = UserForm1.listHeader.Column(0)
cmbEnvironment.Value = UserForm1.listHeader.Column(1)
cmbHost.Value = UserForm1.listHeader.Column(2)
cmbIP.Value = UserForm1.listHeader.Column(3)
cmbAccessible.Value = UserForm1.listHeader.Column(4)
cmbLast.Value = UserForm1.listHeader.Column(5)
cmbConfirmation.Value = UserForm1.listHeader.Column(6)
cmbProjects.Value = UserForm1.listHeader.Column(7)
End Sub
Private Sub UserForm_Initialize()
cmbSearch.List = Sheets("PRESTAGE DB").Range("A4:A10000").Value
End Sub您认为是什么原因造成的？文件链接：https://jmp.sh/8cDUORV
1条答案
按热度按时间ujv3wf0j1#
使用RowSource属性将数据加载到ListBox中时，可能会出现一些问题和缺点。如果我是你，我会尽量避免。
记住这一点，请看一下这个演示，我展示了如何使用加载到用户表单中的列表框中的Excel数据进行基本操作（创建，读取，更新，删除）。
也许你可以调整它来满足你的需要。这不是理想的解决方案（例如缺乏错误处理和一些重复的代码），但应该为您指明正确的方向。
访问这个github仓库并下载xlsm文件：
https://github.com/rdiazjimenez/excel-vba-userform-basic-listbox-demo
另外，请阅读readme.md文件以查看一些说明。
以下是在设计包括将Excel数据加载到UserForm中的列表框中的解决方案时应记住的一些提示：
1.尝试将Excel信息存储到结构化的Excel表中（访问此链接以了解更多信息：https://support.office.com/en-ie/article/create-and-format-tables-e81aa349-b006-4f8a-9806-5af9df0ac664）
1.在VBA中的每个模块的顶部使用Option Explicit，以便您可以更好地控制所使用和创建的变量（访问此链接以了解更多信息：https://learn.microsoft.com/en-us/dotnet/visual-basic/language-reference/statements/option-explicit-statement）
1.在UserForm中为控件使用描述性名称（访问此链接以了解更多信息：https://rtmccormick.com/2015/11/23/vba-control-naming-conventions/）