1、支持單元格點擊出現彈框進行選擇
支持模多次模糊查詢
Private Sub CommandButton1_Click()
Call vehicle_查詢
End SubPrivate Sub Worksheet_Activate()Call vehicle_取出車架號和公司名稱 '取出不重復的車架號
' Sheet13.ComboBox1.Visible = False '車架號顯示
' Sheet13.ComboBox2.Visible = False '公司名字顯示
End SubPrivate Sub ComboBox2_Change() '進行combox的篩選On Error Resume Next
' Call vehicle_取出車架號和公司名稱Dim n, i, jDim arr(), ar()With Sheet13.ComboBox2ar = Application.Transpose(Application.Transpose(.List))If .Text <> "" Thenfind_t = .Textnumber_t = .ListCount - 1For n = 0 To number_tIf InStr(.List(n), .Text) > 0 Then' Debug.Print .List(n)ReDim Preserve arr(i)arr(i) = .List(n)i = i + 1
' Sheet13.ComboBox2.Activate
' If Len(.Text) = 0 Then
' Call vehicle_取出車架號和公司名稱
' MsgBox "測試"
' End IfElse:.List = ar
' Call vehicle_取出車架號和公司名稱
' arr = Sheet13.ComboBox2.List
' ' .RemoveItem (n)
' Debug.Print .List(n)End IfNextEnd If.List = arrEnd With
End Sub
Private Sub ComboBox1_Change() '進行combox的篩選On Error Resume Next' Call vehicle_取出車架號和公司名稱Dim n, i, jDim arr()Sheet13.Range("X3").Value = "=IF(O" & i + 3 & "=" & """" & "前置" & """" & ",F" & i + 3 & ",IF(O" & i + 3 & "=" & """" & "后置" & """" & ",DATE(YEAR(F" & i + 3 & "),MONTH(F" & i + 3 & ")+1,DAY(F" & i + 3 & ")),F" & i + 3 & "))" '生成公式Sheet13.Range("Y3") = Format(Sheet13.Range("X3").Value, "yyyy年mm月")
' Sheet13.Range("Y3").NumberFormat = "YYYY年MM月" '轉為數值格式Sheet13.Range("Z3").Value = "=DATEDIF(F" & i + 3 & ",G" & i + 3 & "," & """" & "m" & """" & ")+" & "1" '生成公式
' Sheet13.Range("Z3").Value = "=X3"Sheet13.Range("AA3").Value = "1"Sheet13.Range("AB3").Value = "=N3"With Sheet13.ComboBox1If .Text <> "" Thenfind_t = .Textnumber_t = .ListCount - 1For n = 0 To number_tIf InStr(.List(n), .Text) > 0 Then' Debug.Print .List(n)ReDim Preserve arr(i)arr(i) = .List(n)i = i + 1Else:
' Call vehicle_取出車架號和公司名稱
' ' .RemoveItem (n)
' Debug.Print .List(n)End IfNextEnd If.List = arrEnd WithEnd SubPrivate Sub Worksheet_SelectionChange(ByVal Target As Range)Call vehicle_取出車架號和公司名稱 '取出不重復的車架號If Target.Column = 10 And Target.Row = 3 ThenWith Sheet13.ComboBox1.Visible = True.Width = Target.Width + 3.Height = Target.Height + 3.Left = Target.Left + 1.Top = Target.Top + 25' .Clear.Activate.AutoSize = False.Text = "".DropDown' .AddItem "重慶渝運通冷鏈物流有限公司"End WithElseIf Target.Column = 5 And Target.Row = 3 ThenWith Sheet13.ComboBox2.Visible = True.Width = Target.Width + 3.Height = Target.Height + 3.Left = Target.Left + 1.Top = Target.Top + 25.Activate.AutoSize = False.Text = "".DropDownEnd WithElseSheet13.ComboBox1.Visible = False '車架號顯示Sheet13.ComboBox2.Visible = False '車架號顯示End If
End Sub
Private Sub ComboBox1_Click() '車架號
' Selection.Value = Sheet13.ComboBox1.TextSheet13.Range("j3") = Mid(Sheet13.ComboBox1.Text, InStr(1, Sheet13.ComboBox1.Text, ".") + 1, Len(Sheet13.ComboBox1.Text))Sheet13.ComboBox1.Visible = False '車架號顯示Call vehicle_查詢
End Sub
Private Sub ComboBox2_Click() '公司嗎
' Selection.Value = Sheet13.ComboBox1.Text
' MsgBox "測試" & Sheet13.ComboBox2.TextSheet13.Range("E3") = Mid(Sheet13.ComboBox2.Text, InStr(1, Sheet13.ComboBox2.Text, ".") + 1, Len(Sheet13.ComboBox2.Text))Sheet13.ComboBox2.Visible = False '車架號顯示End Sub2、單元格雙擊事件，雙擊談成下拉框
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) '雙擊事件If Target.Column = 5 And Target.Row = 3 Then
' Sheet13.ComboBox2.Visible = FalseSheet13.Range("E3") = ""Sheet13.ComboBox2.ActivateSheet13.ComboBox2.DropDownSheet13.Range("E3").SelectSheet13.Range("E3") = Sheet13.ComboBox2.TextEnd IfEnd Sub3、支持下拉和模糊查詢comobox
Private Sub ComboBox2_Change() '進行combox的篩選On Error Resume NextDim n, i, jDim arr()With Sheet13.ComboBox2If .Text <> "" Thenfind_t = .Textnumber_t = .ListCount - 1For n = 0 To number_tIf InStr(.List(n), .Text) > 0 Then' Debug.Print .List(n)ReDim Preserve arr(i)arr(i) = .List(n)i = i + 1Else:
' ' .RemoveItem (n)
' Debug.Print .List(n)End IfNextEnd If.List = arrEnd With
End Sub4、
