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

[µo°Ý] ·Q½Ð°Ý¤U©Ô¦¡¿ï³æ°µ·s¼W§R°£­×§ï

[µo°Ý] ·Q½Ð°Ý¤U©Ô¦¡¿ï³æ°µ·s¼W§R°£­×§ï

¥»©«³Ì«á¥Ñ bridetobe ©ó 2014-7-2 15:12 ½s¿è

§Ú§Q¥Î¤u§@ªí2ªºAÄæ¨ÓÅçÃÒ¸ê®Æ§ì¨ì¤u§@ªíªºBÄæ¨Ó°µ¤U©Ô¦¡¿ï³æ
§Ú©Ô¤U©Ô¦¡¿ï³æ«ö·s¼W ¥i¥H¿é¤J·s¼Wªº¸ê®Æ
¤u§@ªí2ªºAÄæ³Ì¤U­±¤]¦³¥X²{·s¼Wªº¸ê®Æ
¦ý¬O«oÅã¥Ü 1004¥Ø«eµ{¦¡½X¥L»¡À³¥Îµ{¦¡©Îª«¥ó©w¸q¿ù»~
µM«á¤U©Ô¦¡¿ï³æ¤S¤£¨£¤F ½Ð°Ý«ç»ò¸Ñ¨M
¥Ø«eµ{¦¡½X

Private Sub Worksheet_Change(ByVal Target As Range) '
If ActiveCell = "·s¼W" Then
   newitem = InputBox("½Ð¿é¤J·s«~¦W:")
   ActiveCell = newitem
   Sheets("²M³æ").Range("A1").End(xlDown) = newitem
   Sheets("²M³æ").Range("A1").End(xlDown).Offset(rowOffset:=1, columnOffset:=0) = "·s¼W"
   Call y

End If
   
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
End Sub

Public Sub y()
    nowselect = ActiveCell.Address

   lastrow = Sheets("²M³æ").Range("A1").End(xlDown).Row
       Columns("B:B").Select
       With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=²M³æ!$A$1:$A$" & lastrow
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .IMEMode = xlIMEModeNoControl
        .ShowInput = True
        .ShowError = True
    End With
    Range(nowselect).Select
End Sub


½Ð¤j®a±Ï±Ï§Ú³o­Ó¥i¼¦ªº¹ê²ß¤k¤j¥Í:'(
¥»¨­¤£¤Ó·|¥´µ{¦¡¤F ¤£ª¾¦ó¸Ñ
¦ÑÁ󵹤F§@·~§R°£©M­×§ï ¦ý¬O¸£¤¤¯uªº¨S·§©À
¥[¤W·s¼Wªº¦a¤è¤£ª¾¹D­þ¸Ì¥X¿ù
²Ä¤@¦¸¨Óµo°Ý ¦pªG¦³­þ¸Ì¨Sª`·Nªº ¯uªº«Ü©êºp
ÁÂÁ¤j®a><

¦^´_ 1# bridetobe
¦bÅçÃÒ©Ò¦bªº¤u§@ªí¼Ò²Õ
  1. Private Sub Worksheet_Change(ByVal Target As Range)
  2. Dim Rng As Range, newitem$, newlist$
  3. If Target.Address Like "$B$*" Then '§ïÅܪºÀx¦s®æ¬°BÄæ
  4.     If Target(1) = "·s¼W" Then '­Y¿ï¾Ü·s¼W
  5.     Application.EnableEvents = False 'Ãö³¬Ä²µo¨Æ¥óµ{§Ç
  6.     newitem = InputBox("½Ð¿é¤J·s¼W¶µ¥Ø") '·s¼W¶µ¥Ø
  7.        With ¤u§@ªí2
  8.        .[A1].End(xlDown).Offset(1) = newitem '²M³æ©³³¡·s¼W¶µ¥Ø
  9.        newlist = "=" & .Range(.[A1], .[A1].End(xlDown)).Address(, , , 1) '·s²M³æ¦ì§}
  10.        End With
  11.      Set Rng = Target.SpecialCells(xlCellTypeSameValidation) '¬Û¦PÅçÃÒÀx¦s®æ
  12.      With Rng.Validation
  13.      .Delete '§R°£ÅçÃÒ
  14.      .Add xlValidateList, , , newlist '¥[¤JÅçÃÒ
  15.      End With
  16.       Target = newitem 'Àx¦s®æ§ï¬°·s­È
  17.     Application.EnableEvents = True '¶}±ÒIJµo¨Æ¥óµ{§Ç
  18.      End If
  19. End If
  20. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 2# Hsieh


¨º½Ð°Ý¤@¤U ¦pªG§Ú¬O·Q¦b¤U©Ô¦¡¿ï³æ¸Ì­±¿ï¾Ü§R°£
µM«á¸õ¥X¿é¤J¤è¶ôÅý¨Ï¥ÎªÌ¿é¤J¦b²M³æ¤u§@ªíAÄæ·Q­n§R°£ªº¸ê®Æ
µM«á¦b¥h·j´M¦³¨S¦³¦bAÄæ¸Ì¥X²{ ¦pªG¦³´N±NAÄ椺ªº¸ê®Æ§R°£
¨S¦³ªº¸Ü´N¸õ¥X©|¥¼¦³¦¹°Ó«~
³o­Óªº¸Ü­n«ç»ò¥´·|¤ñ¸û¦n

TOP

¦^´_ 3# bridetobe

¸Õ¸Õ¬Ý¬O§_²Å¦X?
  1. Private Sub Worksheet_Change(ByVal Target As Range)
  2. Dim A As Range
  3. If Target.Column <> 2 Then Exit Sub
  4. With ¤u§@ªí2
  5.   ThisWorkbook.Names.Add "²M³æ", "=OFFSET(" & .Name & "!$A$1,,,COUNTA(" & .Name & "!$A:$A))" '«Ø¥ß°ÊºA½d³ò¦WºÙ°µ¬°²M³æ
  6.   Set A = .Cells(.Rows.Count, 1).End(xlUp).Offset(1) '²M³æ¤U¤@®æ
  7. End With
  8. With Target
  9. Select Case .Value
  10. Case "·s¼W"
  11.    newitem = InputBox("¿é¤J·s¼W¶µ¥Ø")
  12.    If IsError(Application.Match(newitem, [²M³æ], 0)) Then A.Value = newitem Else MsgBox newitem & "¤w¦b²M³æ¤º": Exit Sub
  13.    With .EntireColumn.Validation
  14.    .Delete
  15.    .Add xlValidateList, , Formula1:="=" & [²M³æ].Address(, , , 1)
  16.    End With
  17.    Target = newitem
  18. Case "§R°£"
  19.    delitem = InputBox("¿é¤J§R°£¶µ¥Ø")
  20.    Set A = [²M³æ].Find(delitem, lookat:=xlWhole)
  21.    If A Is Nothing Then
  22.       MsgBox delitem & "¥¼¦b²M³æ¤º"
  23.       Else
  24.       A.Delete xlShiftUp
  25.    With .EntireColumn.Validation
  26.    .Delete
  27.    .Add xlValidateList, , Formula1:="=" & [²M³æ].Address(, , , 1)
  28.    End With
  29.    End If
  30. End Select
  31. End With
  32. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 4# Hsieh


¥i¥H!!!¤Ó·PÁ¤F!!!!!!ÁÂÁÂÁÂÁÂ!!!


¥i¥H¦A½Ð°Ý¤@­Ó¦a¤è¶Ü
¦pªG·Q­n·s¼W«eªº²M³æ©³³¡¬O³o¼Ë
«e.JPG
·s¼W§¹123«áªº²M³æ©³³¡¬O³o¼Ë
«á.JPG

´N¬O·s¼W §R°£ ­×§ï³£·|¦U©¹¤U¤@®æ
§Ú­ì¥»ªºµ{¦¡½X¬O³o¼Ë¥´
  1.    Sheets("²M³æ").Range("A1").End(xlDown).Offset(rowOffset:=1, columnOffset:=0) = "·s¼W"
  2.       Sheets("²M³æ").Range("A1").End(xlDown).Offset(rowOffset:=1, columnOffset:=0) = "§R°£"
  3.          Sheets("²M³æ").Range("A1").End(xlDown).Offset(rowOffset:=1, columnOffset:=0) = "­×§ï"
½Æ»s¥N½X
¥i¬O¥X¨Ó¦n¹³¤£¤Ó¹ï ­n«ç»ò§ï·|¤ñ¸û¦n

¤£¦n·N«ä§Ú°ÝÃD¦n¦h><

TOP

¥»©«³Ì«á¥Ñ Hsieh ©ó 2014-7-7 12:46 ½s¿è

¦^´_ 5# bridetobe
  1. Private Sub Worksheet_Change(ByVal Target As Range)
  2. Dim A As Range
  3. If Target.Column <> 2 Then Exit Sub
  4. With ¤u§@ªí2
  5. ThisWorkbook.Names.Add "²M³æ", "=OFFSET(" & .Name & "!$A$1,,,COUNTA(" & .Name & "!$A:$A),)"
  6. With Range("B:B").Validation
  7.    .Delete
  8.    .Add xlValidateList, , , "=²M³æ"
  9. End With
  10. Select Case Target.Value
  11. Case "·s¼W"
  12. newitem = InputBox("¿é¤J·s¼W¶µ¥Ø")
  13. If Application.CountIf([²M³æ], newitem) = 0 Then
  14.    Set A = .Columns("A:A").Find("·s¼W", lookat:=xlWhole)
  15.    A.Insert xlShiftDown
  16.    A.Offset(-1) = newitem
  17.    Target = newitem
  18. Else
  19.    MsgBox "¶µ¥Ø¤w¦s¦b²M³æ¤º"
  20. End If
  21. Case "§R°£"
  22. delitem = InputBox("¿é¤J§R°£¶µ¥Ø")
  23. Set A = .Columns("A:A").Find(delitem, lookat:=xlWhole)
  24. If A Is Nothing Then
  25.    MsgBox delitem & "¤£¦s¦b²M³æ¤º"
  26.    Else
  27.    A.Delete xlShiftUp
  28. End If
  29. Case "­×§ï"
  30. chitem = InputBox("¿é¤J­×§ï¶µ¥Ø")
  31. Set A = .Columns("A:A").Find(chitem, lookat:=xlWhole)
  32. If A Is Nothing Then
  33.    MsgBox chitem & "¤£¦s¦b²M³æ¤º"
  34.    Else
  35.    A.Value = InputBox("¿é¤J§ó¥¿¶µ¥Ø", , chitem)
  36.    Target = A
  37. End If
  38. End Select
  39. End With
  40. With Range("B:B").Validation
  41.    .Modify xlValidateList, , , "=²M³æ"
  42. End With
  43. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 6# Hsieh


¤Ó·PÁ¤F!!!­ì¨Ó¬O³o¼Ë><
ÁÂÁÂÁÂÁÂ!!!

TOP

        ÀR«ä¦Û¦b : ¡i¬O§_µo´§¤F¨}¯à¡H¡j¤H¶¡¹Ø©R¦]¬°µu¼È¡A¤~§óÅã±o¬Ã¶Q¡CÃø±o¨Ó¤@½ë¤H¶¡¡AÀ³°Ý¬O§_¬°¤H¶¡µo´§¤F¦Û¤vªº¨}¯à¡A¦Ó¤£­n¤@¨ý¨Dªø¹Ø¡C
ªð¦^¦Cªí ¤W¤@¥DÃD