- 帖子
- 1517
- 主題
- 40
- 精華
- 0
- 積分
- 1541
- 點名
- 0
- 作業系統
- Windows 7
- 軟體版本
- Excel 2010 & 2016
- 閱讀權限
- 100
- 性別
- 男
- 來自
- 台灣
- 註冊時間
- 2020-7-15
- 最後登錄
- 2025-10-31
|
5#
發表於 2024-1-17 15:48
| 只看該作者
本帖最後由 Andy2483 於 2024-1-18 09:17 編輯
謝謝論壇,謝謝各位前輩
後學藉此帖練習在儲存格自動產生需求清單與次清單,學習方案如下,請各位前輩指教
選取[K2]儲存格產生清單:
[K2]選取清單後在[M2]產生次清單:
Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Brr, Z, i&
With Target
If .Address(0, 0) = "K2" Then
Brr = Range([C2], [C65536].End(3))
Set Z = CreateObject("Scripting.Dictionary")
For i = 1 To UBound(Brr)
If Trim(Brr(i, 1)) <> "" Then Z(Trim(Brr(i, 1))) = ""
Next
With .Validation
.Delete
If Z.Count > 0 Then .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=Join(Z.KEYS(), ",")
Set Z = Nothing: Brr = Empty
End With
End If
End With
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Brr, Z, i&
With Target
If .Address(0, 0) = "K2" Then
Brr = Range([D2], [C65536].End(3))
Set Z = CreateObject("Scripting.Dictionary")
For i = 1 To UBound(Brr)
If Brr(i, 1) = .Value And Trim(Brr(i, 2)) <> "" Then Z(Trim(Brr(i, 2))) = ""
Next
With [M2].Validation
.Delete
If Z.Count > 0 Then .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=Join(Z.KEYS(), ",")
Set Z = Nothing: Brr = Empty
End With
End If
If .Address(0, 0) = "M2" Then Call 列出明細
End With
End Sub |
|
