2個條件的VBA

如何在表單內輸入總表C欄"客戶名稱"裡的關鍵字，然後在ListBox1裡出現有關關鍵字裡的姓名，點選後，在ListBox2裡會出現符合姓名的歷史資料日期(總表B欄)，點選後，按確認，轉寫符合這2項的資料，在報價列印的B7:G30
依序往下排列。請教先進老師們不另指教!

20120530.rar (365.32 KB)

樓主熱帖

GBKEE

超級版主 Rank: 8 Rank: 8

帖子: 5923
主題: 13
精華: 1
積分: 5986
點名: 0
作業系統: win10
軟體版本: Office 2010
閱讀權限: 150
性別: 男
來自: 台灣基隆
註冊時間: 2010-5-1
最後登錄: 2022-1-23

2017 雞年全勤獎章 2016猴年全勤獎章 2015羊年全勤獎章 2014馬年全勤獎章 2013蛇年全勤獎章 2012龍年全勤獎章論壇貢獻勳章熱烈參與勳章 2011兔年全勤獎章

2^#

發表於 2012-6-1 09:03 | 只看該作者

回復 1# man65boy
ListBox1 更換為 ComboBox1
ListBox2 更換為 ComboBox2

Dim xlSh As Worksheet
Private Sub UserForm_Initialize()
Set xlSh = Sheets("總表")
TextBox1_Change '
End Sub
Private Sub CommandButton1_Click()
Dim Rng As Range, xi As Integer
If ComboBox1.ListIndex > -1 And ComboBox2.ListIndex > -1 Then
With xlSh
xi = 2
Do While .Cells(xi, "C") <> ""
If .Cells(xi, "B").Text = ComboBox2 And .Cells(xi, "C") = ComboBox1 Then
If Rng Is Nothing Then
Set Rng = .Range(.Cells(xi, "d"), .Cells(xi, "i"))
Else
Set Rng = Union(Rng, .Range(.Cells(xi, "d"), .Cells(xi, "i")))
End If
End If
xi = xi + 1
Loop
Rng.Copy
Sheets("報價單列印").Cells(Rows.Count, "b").End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
End With
Else
MsgBox "客戶名稱或日期 ??? "
End If
End Sub
Private Sub CommandButton2_Click()
Unload UserForm1
End Sub
Private Sub TextBox1_Change()
Dim xi As Integer, xlString As String
With xlSh
xi = 2
Do While .Cells(xi, "C") <> ""
If .Cells(xi, "C") Like "*" & TextBox1 & "*" Then
If InStr(xlString, "," & .Cells(xi, "C") & ",") = 0 Then
xlString = xlString & "," & .Cells(xi, "C") & ","
End If
End If
xi = xi + 1
Loop
End With
If xlString = "" Then 'TextBox1的內容找不到
ComboBox1.Clear
ComboBox2.Clear
Exit Sub
End If
With ComboBox1
.List = Split(Mid(xlString, 2, Len(xlString) - 2), ",,")
.Value = .List(0)
End With
End Sub
Private Sub ComboBox1_Change()
Dim xi As Integer, xlString As String
If ComboBox1.ListIndex > -1 Then
'控制項.ListIndex = -1 控制項的值或選項不在List內
With xlSh
xi = 2
Do While .Cells(xi, "C") <> ""
If .Cells(xi, "C") = ComboBox1 Then
If InStr(xlString, "," & .Cells(xi, "B") & ",") = 0 Then
xlString = xlString & "," & .Cells(xi, "B") & ","
End If
End If
xi = xi + 1
Loop
End With
With ComboBox2
.List = Split(Mid(xlString, 2, Len(xlString) - 2), ",,")
.Value = .List(0)
End With
ElseIf ComboBox1.ListIndex = -1 Then
ComboBox2.Clear
End If
End Sub