|
双击黄色列的任一单元格,出现窗体,在窗体搜索框中输入单元名称或者类别关键字,自动筛选后双击该名称输入到指定单元格!!求助在该代码中插入查询语句,谢谢!附件可下载测试
- Dim my()
- Dim arrRow() As Long
- Private Sub CommandButton1_Click() '
- Dim endrow As Long
- If Me.TextBox1 = "" Or Me.TextBox2 = "" Then MsgBox "代码,名称不能为空": Exit Sub
- Set T1 = Me.TextBox1: Set T2 = Me.TextBox2
- If Application.CountIf(Sheet4.Range("B:B"), T2) > 0 Then
- MsgBox T2 & "----此单位已存在!": Exit Sub
- End If
-
- With Sheet4
- endrow = .Cells(.Rows.Count, "A").End(xlUp).Row
- endrow = endrow + 1
- .Cells(endrow, "A") = T1
- .Cells(endrow, "B") = T2
- End With
- Me.TextBox1 = "": Me.TextBox2 = ""
- Call UserForm_Initialize
- MsgBox "增加成功"
- End Sub
- Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
- If ListBox1.ListIndex <= 0 Then Exit Sub
- rtnRow = arrRow(ListBox1.ListIndex)
- Unload Me
- End Sub
- Private Sub UserForm_Initialize()
- Call SetListBox
- End Sub
- Sub SetListBox()
- Dim wIdx As Long
- Dim endrow As Long
- Dim temp()
- Dim i As Long, j As Long
- Erase my
- Erase arrRow
- ListBox1.Clear
- w = ""
- With ListBox1
- .ColumnCount = 2 '设置列数
- For j = 10 To 11
- w = w & Sheet4.Cells(1, j).Width & ";"
- Next
- w = Left(w, Len(w) - 1)
- .ColumnWidths = w
- .ColumnHeads = False '是否显示列标题
- a = Sheet4.Range("A" & Rows.Count).End(xlUp).Row
- If a < 2 Then a = 2
- ReDim Preserve my(1 To 2, 1 To 1)
- my(1, 1) = Sheet4.Range("A1") '
- my(2, 1) = Sheet4.Range("B1") '
- b = 1
- For i = 2 To a
- For j = 10 To 11
- b = b + 1
- ReDim Preserve my(1 To 2, 1 To b)
- my(1, b) = Sheet4.Range("A" & i)
- my(2, b) = Sheet4.Range("B" & i)
- wIdx = wIdx + 1
- ReDim Preserve arrRow(1 To wIdx)
- arrRow(wIdx) = i
- Exit For
- Next
- Next
- ReDim temp(1 To b, 1 To 2)
- For i = 1 To b
- For j = 1 To 2
- temp(i, j) = my(j, i)
- Next
- Next
- ListBox1.List() = temp
- End With
- End Sub
复制代码
|
|