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

[µo°Ý] ½Ð°Ý¡G¦h­Ó¤U©Ô¦¡¿ï³æ³]­p

[µo°Ý] ½Ð°Ý¡G¦h­Ó¤U©Ô¦¡¿ï³æ³]­p

¦U¦ì¤j¤j±z¦n¡A
·Q½Ð±Ð¥H¤Uªº°ÝÃD¡G


¹Ï¤ù»P§Æ±æ§e²{»¡©ú¡G
(1) ÄæA~ÄæD¡G¬O¸ê®Æ®wªº·§©À¡C¥Ø«e¥u¥ý¦C¥X12µ§¸ê®Æ¡C
(2) ÄæI~ÄæL¡G¬O¤U©Ô¦¡¿ï³æªº³]­p¡C
(2-1) ÄæI(¥D­n¤ÀÃþ)¡G§Æ±æ§e²{¥X¤£­«½Æªº²M³æ¡G¤å¨ã¡B¥Í¬¡¡B¹q¾¹
(2-2) ÄæJ(¶µ¥Ø)¡G§Æ±æ§e²{¥X¤£­«½Æªº²M³æ
¨Ò2-2-1¡G ­YÄæI¿ï¤å¨ã®É¡AÄæJ§e²{¥X¤£­«½Æªº²M³æ¡GA4¯È(ÁöµM¸ê®Æ®w¦³5­Ó¡A¦ý¥u»ÝÅã¥Ü1­Ó)¡Bª½¤Ø
¨Ò2-2-2¡G ­YÄæI¿ï¹q¾¹®É¡AÄæJ§e²{¥X¤£­«½Æªº²M³æ¡G¿Oªw(ÁöµM¸ê®Æ®w¦³2­Ó¡A¦ý¥u»ÝÅã¥Ü1­Ó)
(2-3) ÄæK(³W®æ)¡G§Æ±æ§e²{¥X¤£­«½Æªº²M³æ
¨Ò2-3-1¡G©Ó¨Ò2-2-1¡AÄæJ¿ï¤FA4¯È¡AÄæK§e²{¥X¤£­«½Æªº²M³æ¡G¥Õ¦â(ÁöµM¸ê®Æ®w¦³2­Ó¡A¦ý¥u»ÝÅã¥Ü1­Ó)
(2-4) ÄæL(³æ¦ì)¡G¨ÌÄæK¿ï¾Üªº¦C¥X¹ïÀ³ªº²M³æ
¨Ò¡G©Ó¨Ò2-3-1¡AÄæK¿ï¤F¥Õ¦â¡AÄæL§e²{¥X²M³æ¡G1½c¡B1¥]

ªþ¤WÀɮסG ·s¼W Microsoft Excel ¤u§@ªí (2).zip (6.81 KB)

¦A³Â·Ð¦U¦ì¤j¤j¡A©âªÅ¨ó§U´£¨Ñ·Qªk¡AÁÂÁ±z¡C

  1. Dim d As Object
½Æ»s¥N½X
  1. Private Sub Worksheet_Change(ByVal Target As Range)
  2. Dim rng As Range, s$, k, t, xd As Object, i&, j&
  3. If Target.Cells.Count > 1 Then Exit Sub
  4. Set rng = [i2:l2]
  5. If Application.Intersect(rng, Target) Is Nothing Then Exit Sub
  6. If Target = rng(rng.Cells.Count) Then Exit Sub
  7. If d Is Nothing Then Call zz: Exit Sub
  8. Set xd = CreateObject("scripting.dictionary")
  9. For j = 1 To rng.Cells.Count
  10.      s = s & rng(j).Value & "|"
  11.     If Target = rng(j) Then Exit For
  12. Next
  13. 1000
  14. k = Filter(d.keys, s)
  15. For Each t In k
  16.     xd(Split(t, "|")(j)) = ""
  17. Next
  18. t = Join(xd.keys, ",")
  19. If t = "" Then s = s & "|": j = j + 1: rng(j).Validation.Delete: GoTo 1000
  20. With rng(j + 1).Validation
  21.     .Delete
  22.     .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
  23.     xlBetween, Formula1:=t
  24. End With
  25. Application.EnableEvents = 0
  26. i = InStr(t, ",")
  27. Select Case i
  28.     Case 0: rng(j + 1) = t
  29.     Case 1: rng(j + 1) = Mid(t, 2)
  30.     Case Else: rng(j + 1) = ""
  31. End Select
  32. rng(j + 1).Select
  33. Application.EnableEvents = 1
  34. End Sub
½Æ»s¥N½X
  1. Sub zz()
  2. Set d = CreateObject("scripting.dictionary")
  3. Dim a, b(), s$, k
  4. a = [a1].CurrentRegion.Value
  5. ReDim b(UBound(a, 2) - 1)
  6. For i = 2 To UBound(a)
  7.     d(a(i, 1)) = ""
  8.     For j = 1 To UBound(a, 2)
  9.         b(j - 1) = a(i, j)
  10.     Next
  11.     d(Join(b, "|")) = ""
  12. Next
  13. s = Join(Filter(d.keys, "|", False), ",")
  14. With [i2].Validation
  15.     .Delete
  16.     .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
  17.     xlBetween, Formula1:=s
  18. End With
  19. Application.ScreenUpdating = 0
  20. Application.EnableEvents = 0
  21. [i2:l2] = ""
  22. Application.ScreenUpdating = 1
  23. Application.EnableEvents = 1
  24. MsgBox "Please select item form " & [i1].Value & " first"
  25. [i2].Select
  26. End Sub
½Æ»s¥N½X

zz.zip (18.89 KB)

TOP

¦^´_ 2# ikboy


ÁÂÁ ¡Aikboyªº¨ó§U¡A§Ú¦A¸Õ¬Ý¬Ý

TOP

¦^´_ 1# gaishutsusuru


·Q½Ð°Ý¦³¥i¥H¤£»Ý­n¥¨¶°ªº¼gªk¶Ü¡HÁÂÁÂ

TOP

¥»©«³Ì«á¥Ñ jeffrey628litw ©ó 2020-11-22 17:49 ½s¿è

¦^´_ 4# gaishutsusuru


        ¤j¤j±z¦n¡G±z¦pªG¬O­n°µ4¼h¤U©Ô¿ï³æ¡A±z­n¥Î±zªº¤èªk¡A§Ú©Òª¾¹Dªº¬O   ³Ì³Â·Ðªº»s§@¤è¦¡ (PS¡G³oºØ§@·~³t«×¸ê®Æ¶q¤@Åܤj·|¹q¸£³t«×ÅܫܺC¡A
                             ¤ñ VBA ªº ¤U©Ô¿ï³æ Listbox ºC«D±`¦h)¡G
                            ªþ¤W±Ð¾ÇÀɵ¹§A°Ñ¦Ò¡A±z¥i¥H¦Û¦æ¬ã¨s¬Ý¬Ý¡C

ÀɮפU¸ü¡G   2­Ó 4¼h¤U©Ô¿ï³æ (¦³¤@­Ó±Ð¾ÇÀÉ).rar (70.92 KB)

TOP

¦^´_ 4# gaishutsusuru

          «Øij¨Ï¥Î VBA Listbox ¤U©Ô¿ï³æ (³t«×§Ö¡A¦Ó¥B«Ø¥ß¸ê®Æ®w¤ñ¸û®e©ö¤@ÂI)¡G




   ÀɮפU¸ü¡G 4¼hListbox ¤U©Ô¿ï³æ v.01.rar (25.88 KB)

TOP

        ÀR«ä¦Û¦b : ªY½à§O¤H´N¬O²øÄY¦Û¤v¡C
ªð¦^¦Cªí ¤W¤@¥DÃD