[µo°Ý] ¤ÀÃþ°ÝÃD

ayubbs

ex1.rar (5.92 KB)

°ÝÃD
¤U¸ü¦¸¼Æ: 12

2014-1-17 13:09

·Q½Ð±Ð¦U¦ì¦Ñ®v
¤p©f¤@­ÓÃö©ó¤ÀÃþªº°ÝÃD½Ð±Ð¡A¤º®e¦pªþ¥ó¡A¦]¤£¤Ó·|´y­z¡A·Q½Ð±Ð¦U¦ì¥ý¶i¡A¦¹
¤u§@©ú²Ó¥i§_¨Ï¥ÎVBA¤è¦¡§¹¦¨¤U©Ô¦¡¿ï³æ¡AÁÂÁÂ

GBKEE

¦^´_ 3# ayubbs
   

³q±`¬O¨ú¥Î¦@¥Îªº¸ê®Æ¤£¤è«K±N¥¨¶°©ñ¸m©ó¤º

¦@¥Îªº¸ê®ÆÀɪº®æ¦¡¦p¤@­P,¥i±N¥¨¶°³]©ó¥\¯àªí¤W

Hsieh

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

ayubbs

¦^´_ 2# Hsieh


ÁÂÁ¦Ѯv«ü¾É¡A«D±`¦n¥Î¡A¦ý¥t¥i½Ð±Ð¦Ñ®v¡A¦]§Ú¸ê®Æ¾ã²z®É¡A³q±`¬O¨ú¥Î¦@¥Îªº¸ê®Æ¤£¤è«K±N¥¨¶°©ñ¸m©ó¤º¡A
¥t¥i¦³¨ä¥¦¤è¦¡¥i¤£¥Îworksheet change ¤è¦¡¥i¹F¦¨§@·~¶Ü?
¤ñ¦p¨Ï¥ÎcommendButton¤è¦¡¡A±Nvba©ñ¸m©ó¥t¤@¤u§@·~­±¡A»Ý­n¨Ï¥Î®É°õ¦æ§Y¥i¡A¦¹ºØ¤è¦¡¥¼ª¾¬O§_¥i¹F¦¨¡A
¦Ñ®vªº¤è¦¡«D±`¯«©_¡A¦p¤è«K¥i§_¬°µ{¦¡½X¡Aµy¥[µù¸ÑÅý¤p©f¥i¥H¾Ç²ß¨ä¤¤ªºÀ³¥Î¤è¦¡¡AÁÂÁÂ

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