他的数据表是这样的: 他的基本需求是在BOM表中,根据图号来选择不同的配件,也就是说,在物料清单中,有相同图号的记录,他希望能点一下编码,就弹出所有图号与之相同的记录,供选择输入。根据编码匹配表1数据,如果数据源有相同图号重名,就弹出几个不同编码供选择 他这个需求表述得也不是很清楚,在论坛上来来回回说了好几遍,还是没怎么整明白。有人把这个物料编码做了一个下拉列表,选择以后再把其他列的数据带出来,但我觉得这样好像是差点意思,光看编码我们可能搞不清是什么配件。我觉得应该要把相关信息都列出来,如下图所示,这样选择起来就很直观了:基本思路: 1、运用工作表SelectionChange事件,点中编码弹出ListBox。2、循环“源”工作表,查找“图号”与选中单元格所在行对应的图号相同的记录,添加表ListBox中。3、双击ListBox记录,填入当前行,退出ListBox。1、在“BOM”表里,SelectionChange事件: Dim currRowPrivate Sub Worksheet_SelectionChange(ByVal Target As Range) Dim ws As Worksheet, lastRow As Long Dim picNumber As String Set ws = ThisWorkbook.Sheets("源") If Target.Row > 13 And Target.Column = 3 Then currRow = Target.Row picNumber = Me.Cells(currRow, "L").Value With Me.ListBox1 .Clear .Visible = True .Top = Target.Top + Target.Height .Left = Target.Left .ColumnCount = 5 .ColumnWidths = Me.Cells(currRow, 3).MergeArea.Width & ";" _ & Me.Cells(currRow, 4).MergeArea.Width & ";" _ & Me.Cells(currRow, "H").MergeArea.Width & ";" _ & Me.Cells(currRow, "L").MergeArea.Width & ";" _ & Me.Cells(currRow, "O").MergeArea.Width .Width = Me.Cells(currRow, 3).MergeArea.Width + _ Me.Cells(currRow, 4).MergeArea.Width + _ Me.Cells(currRow, "H").MergeArea.Width + _ Me.Cells(currRow, "L").MergeArea.Width + _ Me.Cells(currRow, "O").MergeArea.Width End With With ws lastRow = .UsedRange.Rows.Count k = 0 For i = 2 To lastRow If .Cells(i, 5) = picNumber Then ListBox1.AddItem For j = 2 To 6 ListBox1.List(k, j - 2) = .Cells(i, j) Next k = k + 1 End If Next End With With ListBox1 .Height = 40 + (.ListCount - 1) * 10 End With Else Me.ListBox1.Visible = False End IfEnd Sub (1)line1,定义一个公众变量currRow ,当前行号,便于写入数据时引用。(2)line7,把当前单元格的行号赋值给currRow。(3)line8,给变量picNumber(图号)赋值。(4)line10~14,清除Listbox内容,显示Listbox,设置Listbox的顶端与左边位置,在当前单元格下方。(5)line15,设置ListBox的栏目宽度与对应单元格的宽度相同。(6)line16,设置Listbox的宽度为所有栏目宽度之和,这里直接把上面的代码复制下来改改,使用了工作表单元格的宽度;应该还可以取得ListBox各栏目的宽度值相加。(7)line26~38,循环“源”表,把图号相同的记录添加到ListBox中。(8)line39~41,设置ListBox的高度,每多一条记录增加10,这里根据需要可以调整。 2、在“BOM”表里,ListBox1_DoubleClick事件:Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean) Dim currListRow As Integer With Me.ListBox1 currListRow = .ListIndex Me.Cells(currRow, 3) = .List(currListRow, 0) Me.Cells(currRow, 4) = .List(currListRow, 1) Me.Cells(currRow, "H") = .List(currListRow, 2) Me.Cells(currRow, "L") = .List(currListRow, 3) Me.Cells(currRow, "O") = .List(currListRow, 4) .Visible = False End WithEnd Sub 代码解析:双击ListBox,把点击的记录写入工作表,这里也可以用单击,不过我觉得单击有点太快,所以用双击,看得清楚一点。
|