excel - 为什么此列表框仅显示电子表格的第 1 2 行,并且在数据输入后不更新以显示其他行?
问题描述
因此,我最近设法弄清楚如何根据选择的组合框的值最终将数据添加到多个工作表中。
例如工作表 PL531e、PL931 和 PL968。如果您从组合框中选择 PL931,将您的数据输入到文本框中并单击“保存数据”按钮,它会将输入的数据添加到工作表“PL931”,反之亦然,如果您选择 PL968 或 PL531e,它将保存输入的数据直接进入相关工作表。
我的用户窗体底部有一个列表框,它目前仅在第一 2 行(标题和第一行数据)中显示数据。
如果工作表中没有数据并且我单击“保存数据”按钮,列表框将自动更新并显示我输入的数据;但是,如果我决定添加其他数据,它将不会显示任何其他行条第 1 行和第 2 行,其中第 1 行是标题,第 2 行是第一行数据。
我知道数据正在保存到电子表格中,因为当我关闭表单本身并查看相关电子表格时,数据就在那里,只是列表框不想显示比第一个两个更多的行。
谁能向我解释如何修改我的代码来解决这个问题(见下面的代码)?
注意:请忽略代码的布局和混乱,我更喜欢先让代码工作,然后再寻找更有效的替代品。
Option Explicit
Dim iExit As VbMsgBoxResult
'Dim updateRow As Integer
Private Sub ComboBox1_Change()
Dim iRow As Long
iRow = [Counta(Overview!D:D)]
With DataEntry
.TestNo.Value = ""
.NeuronID.Value = ""
.DateCode.Value = ""
.TextBox4.Value = ""
.TextBox5.Value = ""
.TextBox6.Value = ""
.TextBox7.Value = ""
.TextBox8.Value = ""
.TextBox9.Value = ""
.TextBox10.Value = ""
.TextBox11.Value = ""
.TextBox12.Value = ""
.TextBox13.Value = ""
.TextBox14.Value = ""
.TextBox15.Value = ""
.TextBox16.Value = ""
.TextBox17.Value = ""
If ComboBox1.Value = "PL531e" Then
TextBox9.Visible = True
TextBox10.Visible = True
TextBox11.Visible = True
TextBox12.Visible = True
TextBox13.Visible = True
TextBox14.Visible = True
TextBox15.Visible = True
TextBox16.Visible = True
TextBox17.Visible = True
.lstDatabase.ColumnCount = 17
.lstDatabase.ColumnHeads = True
.lstDatabase.ColumnWidths = "50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50"
If iRow > 1 Then
.lstDatabase.RowSource = "PL531e!A1:Q" & iRow
Else
.lstDatabase.RowSource = "PL531e!A2:Q2"
End If
Label10.Caption = Sheets("PL531e").Cells(1, 9)
Label11.Caption = Sheets("PL531e").Cells(1, 10)
Label12.Caption = Sheets("PL531e").Cells(1, 11)
Label13.Caption = Sheets("PL531e").Cells(1, 12)
Label14.Caption = Sheets("PL531e").Cells(1, 13)
Label15.Caption = Sheets("PL531e").Cells(1, 14)
Label16.Caption = Sheets("PL531e").Cells(1, 15)
Label17.Caption = Sheets("PL531e").Cells(1, 16)
Label18.Caption = Sheets("PL531e").Cells(1, 17)
ElseIf ComboBox1.Value = "PL931" Then
TextBox9.Visible = True
TextBox10.Visible = True
TextBox11.Visible = True
TextBox12.Visible = True
TextBox13.Visible = False
TextBox14.Visible = False
TextBox15.Visible = False
TextBox16.Visible = False
TextBox17.Visible = False
.lstDatabase.ColumnCount = 13
.lstDatabase.ColumnHeads = True
.lstDatabase.ColumnWidths = "50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50"
If iRow > 1 Then
.lstDatabase.RowSource = "PL931!A1:M" & iRow
Else
.lstDatabase.RowSource = "PL931!A2:M2"
End If
Label10.Caption = Sheets("PL931").Cells(1, 9)
Label11.Caption = Sheets("PL931").Cells(1, 10)
Label12.Caption = Sheets("PL931").Cells(1, 11)
Label13.Caption = Sheets("PL931").Cells(1, 12)
Label14.Caption = ""
Label15.Caption = ""
Label16.Caption = ""
Label17.Caption = ""
Label18.Caption = ""
ElseIf ComboBox1.Value = "PL968" Then
TextBox9.Visible = True
TextBox10.Visible = True
TextBox11.Visible = False
TextBox12.Visible = False
TextBox13.Visible = False
TextBox14.Visible = False
TextBox15.Visible = False
TextBox16.Visible = False
TextBox17.Visible = False
.lstDatabase.ColumnCount = 10
.lstDatabase.ColumnHeads = True
.lstDatabase.ColumnWidths = "50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50"
If iRow > 1 Then
.lstDatabase.RowSource = "PL968!A1:J" & iRow
Else
.lstDatabase.RowSource = "PL968!A2:J2"
End If
Label10.Caption = Sheets("PL968").Cells(1, 9)
Label11.Caption = Sheets("PL968").Cells(1, 10)
Label12.Caption = ""
Label13.Caption = ""
Label14.Caption = ""
Label15.Caption = ""
Label16.Caption = ""
Label17.Caption = ""
Label18.Caption = ""
ElseIf ComboBox1.Value = "PN410X" Then
TextBox9.Visible = True
TextBox10.Visible = True
TextBox11.Visible = False
TextBox12.Visible = False
TextBox13.Visible = False
TextBox14.Visible = False
TextBox15.Visible = False
TextBox16.Visible = False
TextBox17.Visible = False
.lstDatabase.ColumnCount = 10
.lstDatabase.ColumnHeads = True
.lstDatabase.ColumnWidths = "50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50"
If iRow > 1 Then
.lstDatabase.RowSource = "PN410X!A1:J" & iRow
Else
.lstDatabase.RowSource = "PN410X!A2:J2"
End If
Label10.Caption = Sheets("PN410X").Cells(1, 9)
Label11.Caption = Sheets("PN410X").Cells(1, 10)
Label12.Caption = ""
Label13.Caption = ""
Label14.Caption = ""
Label15.Caption = ""
Label16.Caption = ""
Label17.Caption = ""
Label18.Caption = ""
ElseIf ComboBox1.Value = "PN510" Then
TextBox9.Visible = False
TextBox10.Visible = False
TextBox11.Visible = False
TextBox12.Visible = False
TextBox13.Visible = False
TextBox14.Visible = False
TextBox15.Visible = False
TextBox16.Visible = False
TextBox17.Visible = False
.lstDatabase.ColumnCount = 8
.lstDatabase.ColumnHeads = True
.lstDatabase.ColumnWidths = "50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50"
If iRow > 1 Then
.lstDatabase.RowSource = "PN510!A1:H" & iRow
Else
.lstDatabase.RowSource = "PN510!A2:H2"
End If
Label10.Caption = ""
Label11.Caption = ""
Label12.Caption = ""
Label13.Caption = ""
Label14.Caption = ""
Label15.Caption = ""
Label16.Caption = ""
Label17.Caption = ""
Label18.Caption = ""
ElseIf ComboBox1.Value = "GL100" Then
TextBox9.Visible = True
TextBox10.Visible = True
TextBox11.Visible = False
TextBox12.Visible = False
TextBox13.Visible = False
TextBox14.Visible = False
TextBox15.Visible = False
TextBox16.Visible = False
TextBox17.Visible = False
.lstDatabase.ColumnCount = 10
.lstDatabase.ColumnHeads = True
.lstDatabase.ColumnWidths = "50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50"
If iRow > 1 Then
.lstDatabase.RowSource = "GL100!A1:J" & iRow
Else
.lstDatabase.RowSource = "GL100!A2:J2"
End If
Label10.Caption = Sheets("GL100").Cells(1, 9)
Label11.Caption = Sheets("GL100").Cells(1, 10)
Label12.Caption = ""
Label13.Caption = ""
Label14.Caption = ""
Label15.Caption = ""
Label16.Caption = ""
Label17.Caption = ""
Label18.Caption = ""
End If
End With
End Sub
Private Sub CommandButton1_Click()
Dim sh As Worksheet
Dim AddNew As Range
If ComboBox1.Value = "PL531e" Then
Set sh = ThisWorkbook.Sheets("PL531e")
ElseIf ComboBox1.Value = "PL931" Then
Set sh = ThisWorkbook.Sheets("PL931")
ElseIf ComboBox1.Value = "PL968" Then
Set sh = ThisWorkbook.Sheets("PL968")
ElseIf ComboBox1.Value = "PN410X" Then
Set sh = ThisWorkbook.Sheets("PN410X")
ElseIf ComboBox1.Value = "PN510" Then
Set sh = ThisWorkbook.Sheets("PN510")
ElseIf ComboBox1.Value = "GL100" Then
Set sh = ThisWorkbook.Sheets("GL100")
End If
Set AddNew = sh.Range("A6536").End(xlUp).Offset(1, 0)
AddNew.Offset(0, 0).Value = TestNo.Text
AddNew.Offset(0, 1).Value = NeuronID.Text
AddNew.Offset(0, 2).Value = DateCode.Text
AddNew.Offset(0, 3).Value = TextBox4.Text
AddNew.Offset(0, 4).Value = TextBox5.Text
AddNew.Offset(0, 5).Value = TextBox6.Text
AddNew.Offset(0, 6).Value = TextBox7.Text
AddNew.Offset(0, 7).Value = TextBox8.Text
AddNew.Offset(0, 8).Value = TextBox9.Text
AddNew.Offset(0, 9).Value = TextBox10.Text
AddNew.Offset(0, 10).Value = TextBox11.Text
AddNew.Offset(0, 11).Value = TextBox12.Text
AddNew.Offset(0, 12).Value = TextBox13.Text
AddNew.Offset(0, 13).Value = TextBox14.Text
AddNew.Offset(0, 14).Value = TextBox15.Text
AddNew.Offset(0, 15).Value = TextBox16.Text
AddNew.Offset(0, 16).Value = TextBox17.Text
End Sub
Private Sub RefreshListBox()
Me.lstDatabase.Clear
Dim ListRange As Range
End Sub
Private Sub reset_Click()
iExit = MsgBox("Confirm if you want to exit", vbQuestion + vbYesNo, "Data Entry Form")
If iExit = vbYes Then
Unload Me
End If
End Sub
Private Sub UserForm_Initialize()
Label1.Caption = Sheets("Overview").Cells(2, 1)
ComboBox1.List = [Products!A2:A7].Value
Label2.Caption = Sheets("Overview").Cells(4, 1)
Label3.Caption = Sheets("Overview").Cells(4, 2)
Label4.Caption = Sheets("Overview").Cells(4, 3)
Label5.Caption = Sheets("Overview").Cells(4, 4)
Label6.Caption = Sheets("Overview").Cells(4, 5)
Label7.Caption = Sheets("Overview").Cells(4, 6)
Label8.Caption = Sheets("Overview").Cells(4, 7)
Label9.Caption = Sheets("Overview").Cells(4, 8)
TextBox9.Visible = False
TextBox10.Visible = False
TextBox11.Visible = False
TextBox12.Visible = False
TextBox13.Visible = False
TextBox14.Visible = False
TextBox15.Visible = False
TextBox16.Visible = False
TextBox17.Visible = False
ComboBox1.ListIndex = 0
End Sub
解决方案
修复了代码 - 结果我需要添加以下行
iRow = [Counta(SheetName!A:A)]
所述线的位置位于此处:
Private Sub ComboBox1_Change()
Dim iRow As Long
With DataEntry
If ComboBox1.Value = "PL531e" Then
TextBox9.Visible = True
TextBox10.Visible = True
TextBox11.Visible = True
TextBox12.Visible = True
TextBox13.Visible = True
TextBox14.Visible = True
TextBox15.Visible = True
TextBox16.Visible = True
TextBox17.Visible = True
Label10.Caption = Sheets("PL531e").Cells(1, 9)
Label11.Caption = Sheets("PL531e").Cells(1, 10)
Label12.Caption = Sheets("PL531e").Cells(1, 11)
Label13.Caption = Sheets("PL531e").Cells(1, 12)
Label14.Caption = Sheets("PL531e").Cells(1, 13)
Label15.Caption = Sheets("PL531e").Cells(1, 14)
Label16.Caption = Sheets("PL531e").Cells(1, 15)
Label17.Caption = Sheets("PL531e").Cells(1, 16)
Label18.Caption = Sheets("PL531e").Cells(1, 17)
iRow = [Counta(PL531e!A:A)]
.lstDatabase.ColumnCount = 17
.lstDatabase.ColumnWidths = "50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50"
If iRow > 1 Then
.lstDatabase.RowSource = "PL531e!A1:Q" & iRow
Else
.lstDatabase.RowSource = "PL531e!A2:Q2"
End If
此外,我必须使用以下代码添加 Sub reset():
Sub reset()
Dim iRow As Long
With DataEntry
If ComboBox1.Value = "PL531e" Then
iRow = [Counta(PL531e!A:A)]
If iRow > 1 Then
DataEntry.lstDatabase.RowSource = "PL531e!A1:Q" & iRow
Else
DataEntry.lstDatabase.RowSource = "PL531e!A2:Q2"
End If
添加此内容后,对问题进行了排序,我还发现了一个问题,该问题涉及列表框中的标题显示列标题名称本身,例如列 A、B、C 等,而不是 TestNo、NeuronID、日期代码等。我解决了这个只需删除以下代码行:
.lstDatabase.ColumnHeads = True
不会粘贴所有已修复的整个代码,因为这些基本修复实际上是复制/粘贴在其相关部分中,而不是 PL531e 其 PL931、PL968 等。
编辑:根据@Tim Williams 的建议,Me.Controls("Textbox" & i) 我最终使用 Me.Controls 和标记属性将Textbox9.Visible = True 等部分更改为以下内容:
Dim oneControl As MSForms.Control
For Each oneControl In Me.Controls
If TypeName(oneControl) = "TextBox" Then
With oneControl
Select Case .Tag
Case "typeA"
.Visible = True
.Value = ""
Case "typeB"
.Visible = Not (ComboBox1.Value = "PN510")
.Value = ""
Case "typeC"
.Visible = ((ComboBox1.Value = "PL931") Or (ComboBox1.Value = "PL531e"))
.Value = ""
Case "typeD"
.Visible = (ComboBox1.Value = "PL531e")
.Value = ""
End Select
End With
End If
Next oneControl
推荐阅读
- javascript - Vue DOM 元素 scrollTop 不可访问
- teamcity - 重新触发构建链并获取更改的源
- unity3d - Unity:是否有 2D 等效于动画图层上使用的头像蒙版?
- javascript - 无法在 Rails 应用程序的回调中声明变量
- javascript - 将图像名称传递给vue组件时找不到图像
- python - Flask SqlAlchemy 无法获得不同的值
- elasticsearch - Elasticsearch 写入队列分布不均
- visual-studio - 如何在 Visual Studio Debug 中查看发送到服务的请求数据?
- ios - 每次构建项目时如何避免通用框架的构建和脂溢?
- visual-studio - 如何将当前日期与包含一系列日期的列的表进行比较,并将关联值用作变量?