ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

[µo°Ý] ¿é¤J¸ê®Æ«á«K¦Û°Ê¦a¦¨¬°

[µo°Ý] ¿é¤J¸ê®Æ«á«K¦Û°Ê¦a¦¨¬°

¦U¦ì°ª¤â¤j¤j­Ì

·Q³]­p¤@ÅçÃÒ¸ê®Æªí¡A¦b sheet 1 ªº B Äæ¿é¤J¸ê®Æ«á«K¦Û°Ê¦a¦¨¬°ÅçÃÒªº¤U©Ô²M³æ¡A¥i¬O­Y¦bªþ¥ó B2 Àx¦s®æ¿é¤J test ¥i¥H¦b B3 ¥X²{¡A¦ý¦b B3 ¦A,¿é¤J test again ¦b B4 ªº¤U©Ô²M³æ´N¤£¯à¥X²{¤F¡H¥i¦³¬Æ»ò¤èªk¸Ñ¨M¡H­Y¤£¥Î¥¨¶°¤S¦p¦ó¡H

ªþ¥ó
http://www.sendspace.com/file/dl52wq

ÁÂÁ¡I
¤Ñ¥Í§Ú§÷¥²¦³¥Î¡A¤dª÷´²ºÉÁÙ´_¨Ó

¥»©«³Ì«á¥Ñ Hsieh ©ó 2010-5-11 20:51 ½s¿è

©w¸q¦WºÙlist
=OFFSET(Sheet1!$B$2,,,IF(COUNTA(Sheet1!$B:$B)=0,1,COUNTA(Sheet1!$B:$B)),)
ÅçÃÒ²M³æ
¤½¦¡=list
Dropdown list.rar (10.53 KB)
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

©w¸q¦WºÙlist
=OFFSET(Sheet1!$B$2,,,IF(COUNTA(Sheet1!$BB)=0,1,COUNTA(Sheet1!$BB)),)
ÅçÃÒ²M³æ
¤½ ...
Hsieh µoªí©ó 2010-5-11 17:12


====================================================================================
¯u¤ß·PÁ  Hsieh ¤j¤j¡A¬°§Ú¸Ñ¨M¦h§xÂZ¦~ªº°ÝÃD¡AÁÂÁ¡I
¦ý­Y¥[¤W±ø¥ó¬O²¾°£­«½Æ©M¥uÅã¥Ü³Ì±`¥X²{ªº 20µ§¤S¥i¤£¥i¥H°µ¨ì¡H

¤S­n³Â·Ð  Hsieh ¤j¤j¡A·P®¦¡IÁÂÁ¡I
¤Ñ¥Í§Ú§÷¥²¦³¥Î¡A¤dª÷´²ºÉÁÙ´_¨Ó

TOP

LETUGO¥S
¤£¦n·N«ä.§Ú·QDOWNLOAD±zªº
ªþ¥ó
http://www.sendspace.com/file/dl52wq
¨Ó¬Ý...(¦ý¬OHSª© ...
PD961A µoªí©ó 2010-5-11 20:20



===================================================================
ªþ¥ó

Drop down.zip (27.26 KB)

Original & Modified files

¤Ñ¥Í§Ú§÷¥²¦³¥Î¡A¤dª÷´²ºÉÁÙ´_¨Ó

TOP

¥»©«³Ì«á¥Ñ Hsieh ©ó 2010-5-11 23:03 ½s¿è

¦^´_ 4# letugo


    ³o¼Ë¥i¯à­n¥Î¨ìVBA»²§U¤~¦æ Dropdown list.rar (14.06 KB)
  1. Private Sub Worksheet_Change(ByVal Target As Range)
  2. Dim Ar()
  3. Set d = CreateObject("Scripting.Dictionary")
  4. If Target.Column = 3 Then '«ü©wÄæ¦ì
  5.    With Target.EntireColumn.Validation
  6.    .Delete
  7.    For Each a In Range(Cells(2, Target.Column), Target)
  8.      d(a.Value) = d(a.Value) + 1
  9.    Next
  10.    Do Until d.Count = 0 Or i = 20
  11.        k = Application.Large(d.items, 1)
  12.        k = Application.Match(k, d.items, 0)
  13.        ans = Application.Index(d.keys, k)
  14.        If mystr = "" Then
  15.        mystr = ans
  16.        Else
  17.        mystr = mystr & "," & ans
  18.        End If
  19.        d.Remove ans: i = i + 1
  20.    Loop
  21.    If Not IsError(mystr) And mystr <> "" Then .Add xlValidateList, Formula1:=mystr: .ShowError = False
  22.    End With
  23. End If
  24. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_  letugo


    ³o¼Ë¥i¯à­n¥Î¨ìVBA»²§U¤~¦æ
Hsieh µoªí©ó 2010-5-11 22:53

==========================================================
­º¥ý¦A¦¸¦hÁ Hsieh ¤j¤j½ç±Ð¡A¯u¤ß·PÁ¡I
Thanks again !
¤Ñ¥Í§Ú§÷¥²¦³¥Î¡A¤dª÷´²ºÉÁÙ´_¨Ó

TOP

¹ï¤£°_ Hsieh ¤j¤j
¹j¤F³o»ò¤[ÁÙ¦Aµo°Ý¡A´N¬O C Ä檺¤U©Ô²M³æ«ç¼Ë°µ¥X¨Ó¡H¬O¥Î¸ê®ÆÅçÃҶܡH
¥t¥~­Y·Q¦b E Äæ¥[¤J¥t¤@²Õ²¾°£­«½Æ©M¥uÅã¥Ü³Ì±`¥X²{ªº 30µ§ªº¤U©Ô²M³æ
¨º»ò VBA ¤S§ï¦¨«ç¼Ë©O¡H
³Â·Ð Hsieh ¤j¤j½ç±Ð¡AÁÂÁ¡I
¤Ñ¥Í§Ú§÷¥²¦³¥Î¡A¤dª÷´²ºÉÁÙ´_¨Ó

TOP

¦^´_ 9# letugo
Ãø¹D§A³£¨S¥h¤F¸Ñµ{¦¡½X¶Ü?
If Target.Column = 3 Or Target.Column = 5 Then '«ü©wÄæ¦ì

Do Until d.Count = 0 Or i = 30 'i¬°«ü©w±Æ¦W¶q

¥u¬O§â¤£­«½Æ¦W³æ­pºâ¥X²{¦¸¼Æ«á,¤@¦¸¼Æ¦h¹è¥[¤JÅçÃÒ²M³æ
´N³o»ò³æ¯ÂªºÅÞ¿è
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_  letugo
Ãø¹D§A³£¨S¥h¤F¸Ñµ{¦¡½X¶Ü?
If Target.Column = 3 Or Target.Column = 5 Then '«ü©wÄæ¦ì
...
Hsieh µoªí©ó 2010-8-17 00:21


===================================================================
  ½Ð®¤¤p§Ì·M¬N¡A¸g Hsieh ¥S«üÂI¥Ù¶ë¶w¶}
  ÁÂÁ¡I
¤Ñ¥Í§Ú§÷¥²¦³¥Î¡A¤dª÷´²ºÉÁÙ´_¨Ó

TOP

        ÀR«ä¦Û¦b : ­n¤ñ½Ö§ó¨ü½Ö¡D¤£­n¤ñ½Ö§ó©È½Ö¡C
ªð¦^¦Cªí ¤W¤@¥DÃD