麻辣家族討論版版 - Powered by Discuz! Board

Dim Sh As Worksheet, d As Object, d2 As Object, d3 As Object
公式如下
Private Sub CommandButton1_Click()
If TextBox1.Text <> "" Then
'將 TextBox1 資料放入 TBox1
UserForm2.TBox1.Text = TextBox1.Text
'將 TextBox1 資料放入 TBox1
Unload UserForm1
'關閉表單
Else
End If
End Sub
Private Sub UserForm_Initialize()
Dim i As Single, Rng As Range
Set Sh = Sheets("合約資訊")
'宣告 SH = Sheets("合約資訊")
Set d = CreateObject("scripting.dictionary")
'宣告 d = 建立並傳回 ActiveX 物件建立一個 Dictionary物件
Set Rng = Sh.Cells(5, "E")
'SH = Sheets("客戶資料") Rng =儲存格 Cells(第5欄,C列)
Do While Rng <> ""
'Rng = 儲存格 Cells(第5欄,C列) 不等於空白
If d.exists(Rng.Value) = False Then
'
Set d(Rng.Value) = Rng
Else
Set d(Rng.Value) = Union(Rng, d(Rng.Value))
End If
Set Rng = Rng.Offset(1)
Loop
ComboBox1.List = d.KEYS
'ComboBox1 等於 d KEYS
TextBox1 = ""
'TextBox1資料清除
End Sub
Private Sub ComboBox1_Change()
Dim R As Range
TextBox1 = ""
Set d2 = CreateObject("scripting.dictionary")
'd2 = 建立並傳回 ActiveX 物件建立一個 Dictionary物件
For Each R In d(ComboBox1.Value).Offset(, -1).Cells
'ComboBox1的資料找到儲存格後值 -1列
If d2.exists(R.Value) = False Then
'如果 d2 的值是真就
Set d2(R.Value) = R
'Dictionary物件內的
Else '否就
Set d2(R.Value) = Union(R, d2(R.Value))
'd2內的值等於 Union內的值
End If
Next
ComboBox2.Clear
'ComboBox2資料清除
ComboBox2.List = d2.KEYS
'ComboBox2 等於 d2 KEYS
End Sub
Private Sub ComboBox2_Change()
TextBox1 = ""
If ComboBox2.ListIndex > -1 Then TextBox1.Text = Sh.Cells(d2(ComboBox2.Value).Row, "F")
'F欄
'ListIndex = -1 值不在List中
End Sub

複製代碼