Hsieh

¦^´_ 1# ayubbs

1. Private Sub Worksheet_Change(ByVal Target As Range)
   
2. Set d = CreateObject("Scripting.Dictionary")
   
3. If Target.Address = "$I$7" Then
   
4.     For Each a In Range([C3], [C3].End(xlDown))
   
5.       If a = Target Then d(a.Offset(, 1).Value) = ""
   
6.     Next
   
7.     With Target.Offset(1).Validation
   
8.     .Delete
   
9.     .Add xlValidateList, , , Join(d.keys, ",")
   
10.     End With
    
11. End If
    
12. End Sub
    
13. 
    
14. Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
15. Set d = CreateObject("Scripting.Dictionary")
    
16. If Target.Address = "$I$7" Then
    
17.     For Each a In Range([C3], [C3].End(xlDown))
    
18.     d(a.Value) = ""
    
19.     Next
    
20.     With Target.Validation
    
21.     .Delete
    
22.     .Add xlValidateList, , , Join(d.keys, ",")
    
23.     End With
    
24. End If
    
25. End Sub

½Æ»s¥N½X

ex.zip (12.76 KB)

¤U¸ü¦¸¼Æ: 13

2014-1-17 14:42

Hsieh

¦^´_ 3# ayubbs
­«ÂI¬O§Aªº²M³æ¬OÀHµÛ¨C­ÓÀɮפ£¦PÁÙ¬O²M³æ©ñ¦b¤@­ÓÀɮפº¡A¦Ó­n¨ä¥LÀɮקì¨ú³o¨Ç²M³æ§@¬°ÅçÃÒ¨Ó·½?