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

[µo°Ý] ListBox»P±Æ§Ç¥[Á`°ÝÃD

¦^´_ 40# starbox520
TR±Æ¾÷&²£¥X¼Ò²Õªºµ{¦¡½X



   
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 41# GBKEE


    ¥i¥H¤F!!!!G¤j¯u¬O¾Ç®üµL±V....³o¨ÇªF¦è§Ú§ä¤F¦n´X­Ó§«ô³£µL±q¤U¤â....
    ³o­Ó¤èªk§Ú§¹¥þ¨S·Q¨ì¹L...
    ¥u¬OListBox¸Ìªº¸ê®Æ«e­±¿z¿ï§¹«á ­n«ç»ò§â³Ñ¤Uªº¸ê®Æ(¤u§@ªí2)©ñ¦b«á­±©O(¤Ï¦Êªº³¡¤À¥i¥H¥u¯à¤Ï¥Õ¤@µ§ ¡A¤£­n³o»ò¦hµ§¶Ü)
    ¦]¬°¤U­±¨º­ÓListBox2§Ú­n°µ¥¼¥[Á`«eªº¸ê®Æ(­n±qWIP¥h§ä)¥i¯à·|¥Î¨ì¤W­±ListBox¤Ï¥Õ«á´N¥i¥Hª½±µÅã¥Ü¦b¤U­±ListBox
    ¤u§@ªí2ªºªF¦è¬O§Ú±qWIP¨ºÃä¼g¹L¨Óªº
    ListBox2ªº¸ê®Æ­n¹ïÀ³ListBox1¤Ï¥Õ«áCustomer  .  Package  .BodySize .  LC  (±qWIP¥h§ä³o4­Ó¬Û¦Pªº)
    µM«á§e²{"Customer"  ."Location"   ."Device Type"   ."Package".   "BodySize".   "LC".   "QTY"  ."T/Y"  ."Schedule"  .  "Oven OutTime"
    ³oºâ¬O³Ì²×¥Øªº¤F

TOP

¦^´_ 42# starbox520
§Aªº»¡©ú¨S¦³«ÜÁA¸Ñ.
frmSelector¼Ò²Õ,­×§ï¤@¤U¸Õ¸Õ¬Ý
  1. Option Explicit
  2. Private Sub UserForm_Initialize()
  3.     StartupPosition = 0
  4.     Top = 0
  5.     Left = Windows(1).Width - Width
  6.     lstSelector_³]©w
  7. End Sub
  8. Private Sub lstSelector_³]©w()
  9.     With lstSelector
  10.         '**¤Ï¦Êªº³¡¤À¥i¥H¥u¯à¤Ï¥Õ¤@µ§ ¡A¤£­n³o»ò¦hµ§¶Ü
  11.       ' **¨ú®ø     .MultiSelect = 1            *** MultiSelect ÄÝ©Ê  «ü©wª«¥ó¬O§_±µ¨ü¦h­«¿ï¨ú¡C
  12.        If Not IsEmpty(Sheets("TR±Æ¾÷&²£¥X").Sh_Ar) Then .List = Sheets("TR±Æ¾÷&²£¥X").Sh_Ar
  13.     End With
  14.     With ListBox1  '**frmSelector¤¤ªº²Ä¤G­ÓListBox ±±¨î¶µ
  15.         .ColumnCount = 9
  16.         .ColumnWidths = "60,35,75,40,30,60,30,70,30"
  17.     End With
  18. End Sub
  19. Private Sub lstSelector_Change()
  20.     If lstSelector.ListIndex > -1 Then Ex_WIP
  21. End Sub
  22. Private Sub Ex_WIP()
  23.     Dim i As Integer, Ar, A(1 To 4), Ab(), ii As Integer
  24.        With Me.lstSelector
  25.             For i = 0 To 3
  26.                 A(i + 1) = .List(.ListIndex, i)
  27.             Next
  28.        End With
  29.        i = 2
  30.     With Sheets("WIP")
  31.         Do While .Cells(i, 1) <> ""
  32.             If .Cells(i, "A") = A(1) And .Cells(i, "E") = A(2) And .Cells(i, "G") = A(3) And .Cells(i, "F") = A(4) Then
  33.                 If IsEmpty(Ar) Then ReDim Ar(1 To 1) Else ReDim Preserve Ar(1 To UBound(Ar) + 1)
  34.                 ReDim Ab(1 To 1, 1 To 9)
  35.                 For ii = 1 To 8
  36.                    Ab(1, ii) = .Cells(i, ii + 1) ' 8Äæ¸ê®Æ: BÄæ- IÄæ
  37.                Next
  38.                Ab(1, 9) = .Cells(i, "K") 'KÄæ
  39.                Ar(UBound(Ar)) = Ab
  40.                  '** Listbox ³Ì¦hÅã¥Ü 9¦C
  41.             End If
  42.             i = i + 1
  43.         Loop
  44.     End With
  45.     '***********************************
  46.     '**frmSelector¤¤ªº²Ä¤G­ÓListBox ±±¨î¶µ
  47.     With ListBox1
  48.         .Clear
  49.         If UBound(Ar) > 1 Then
  50.              .List = Application.Transpose(Application.Transpose(Ar))
  51.         ElseIf UBound(Ar) = 1 Then
  52.             .List = Ar(1)
  53.         End If
  54.     End With
  55.     '***********************************
  56. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 43# GBKEE


¦^ª©¤j
¥X²{°»¿ù¿ù»~...


1.  ²Ä¤@­ÓListBoxÀ³¸Ó¬O¦C¥X¬Ûªñªº«á¡A³Ñ¾lªº¸ê®Æ¦A¥H¤u§@ªí2"¼Æ¶q"¤j¤pÅã¥Ü


2.²Ä¤G­ÓListBox§e²{¤èªk¬O¡AÂI¨ä¤¤¤@¶µ¡A·|¥X²{±q"WIP"¸ê®Æ¸Ìªº§ì¥X¬õµ§°é¿ï¹ïÀ³¬Û¦Pªº¸ê®Æ
ListBox2  ·|¹³¥H¤U¹Ï¥Ü(·|§ì¨úWIP¸ê®Æ   10­ÓÄæ¦ìªº¸ê®Æ)

TOP

¦^´_ 44# starbox520
2003ª© ¨S¦³¿ù»~

·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¥»©«³Ì«á¥Ñ starbox520 ©ó 2016-10-26 20:51 ½s¿è

¦^´_ 45# GBKEE
  1. Option Explicit
  2. Private Sub UserForm_Initialize()
  3.     StartupPosition = 0
  4.     Top = 0
  5.     Left = Windows(1).Width - Width
  6.     lstSelector_³]©w
  7. End Sub
  8. Private Sub lstSelector_³]©w()
  9. With lstSelector
  10.         '**¤Ï¦Êªº³¡¤À¥i¥H¥u¯à¤Ï¥Õ¤@µ§ ¡A¤£­n³o»ò¦hµ§¶Ü
  11.       ' **¨ú®ø     .MultiSelect = 1            *** MultiSelect ÄÝ©Ê  «ü©wª«¥ó¬O§_±µ¨ü¦h­«¿ï¨ú¡C
  12.        If Not IsEmpty(Sheets("TR±Æ¾÷&²£¥X").Sh_Ar) Then .List = Sheets("TR±Æ¾÷&²£¥X").Sh_Ar
  13.     End With
  14.     With ListBox1  '**frmSelector¤¤ªº²Ä¤G­ÓListBox ±±¨î¶µ
  15.         .ColumnCount = 9
  16.         .ColumnWidths = "60,35,75,40,30,60,30,70,30"
  17.     End With
  18. End Sub
  19. Private Sub lstSelector_Change()
  20.     If lstSelector.ListIndex > -1 Then Ex_WIP
  21. End Sub
  22. Private Sub Ex_WIP()
  23.     Dim i As Integer, Ar, A(1 To 4), Ab(), ii As Integer
  24.        With Me.lstSelector
  25.             For i = 0 To 3
  26.                 A(i + 1) = .List(.ListIndex, i)
  27.             Next
  28.        End With
  29.        i = 2
  30.     With Sheets("WIP")
  31.         Do While .Cells(i, 1) <> ""
  32.             If .Cells(i, "A") = A(1) And .Cells(i, "E") = A(2) And .Cells(i, "G") = A(3) And .Cells(i, "F") = A(4) Then
  33.                 If IsEmpty(Ar) Then ReDim Ar(1 To 1) Else ReDim Preserve Ar(1 To UBound(Ar) + 1)
  34.                 ReDim Ab(1 To 1, 1 To 9)
  35.                 For ii = 1 To 8
  36.                    Ab(1, ii) = .Cells(i, ii + 1) ' 8Äæ¸ê®Æ: BÄæ- IÄæ
  37.                Next
  38.                Ab(1, 9) = .Cells(i, "K") 'KÄæ
  39.                Ar(UBound(Ar)) = Ab
  40.                  '** Listbox ³Ì¦hÅã¥Ü 9¦C
  41.             End If
  42.             i = i + 1
  43.         Loop
  44.     End With
  45. With ListBox1
  46.         .Clear
  47.         If UBound(Ar) > 1 Then
  48.              .List = Application.Transpose(Application.Transpose(Ar))
  49.         ElseIf UBound(Ar) = 1 Then
  50.             .List = Ar(1)
  51.         End If
  52.     End With
  53.     '***********************************
  54. End Sub
  55. Private Sub CommandButton1_Click()
  56.     Dim AA, i As Integer, ii As Integer
  57.     With lstSelector
  58.         For i = 0 To .ListCount - 1
  59.             If .Selected(i) Then
  60.                 If IsEmpty(AA) Then ReDim AA(1 To 4, 1 To 1) Else ReDim Preserve AA(1 To 4, 1 To UBound(AA, 2) + 1)
  61.                 For ii = 1 To 4
  62.                     AA(ii, UBound(AA, 2)) = .List(i, ii - 1)
  63.                 Next
  64.             End If
  65.         Next
  66.     End With
  67.     If IsEmpty(AA) Then
  68.         MsgBox "§A¨S¦³¿ï¨ú¸ê®Æ"
  69.     ElseIf UBound(AA, 2) > 4 Then
  70.         MsgBox "§A¿ï¨ú ¶W¹L 4 µ§ ¸ê®Æ"
  71.     Else
  72.         If MsgBox("¦@ ¿ï¨ú " & UBound(AA, 2) & " µ§¸ê®Æ" & vbLf & "½T©w¿é¤J", vbYesNo) = vbYes Then
  73.             With Sheets("TR±Æ¾÷&²£¥X").Sh_Rng.Offset(1)
  74.                 .Resize(4, 4) = ""
  75.                 .Resize(UBound(AA, 2), UBound(AA)) = Application.Transpose(AA)
  76.             End With
  77.         End If
  78.     End If
  79. End Sub
½Æ»s¥N½X
­þ¸Ì¿ù¤F©O= =¥L¤@ª½»¡«¬ºA¤£²Å... §Ú¬O2010ªºÀ³¸Ó¸ò2003¬Û³q§r....
¤@ª½Åã¥ÜArªº°Ñ¼Æ«¬ºA¦³°ÝÃD

²Ä¤@­ÓListBoxÀ³¸Ó¬O¦C¥X¬Ûªñªº«á¡A³Ñ¾lªº¸ê®Æ¦A¥H¤u§@ªí2"¼Æ¶q"¤j¤pÅã¥Ü
³o­Ó¥i¥H¦C¤J¶Ü

TOP

¥»©«³Ì«á¥Ñ GBKEE ©ó 2016-10-27 05:17 ½s¿è

¦^´_ 46# starbox520
  1.   c_c_lai ´£¨Ñ °ª©ó2003ª©
  2.             '  Sheets("WIP").Cells(i, "F").Value : 950 : Variant/Double : A(4) : "950" : Variant/String
  3.             ' If .Cells(i, "A") = A(1) And .Cells(i, "E") = A(2) And .Cells(i, "G") = A(3) And .Cells(i, "F") = A(4) Then
  4.             If .Cells(i, "A") = A(1) And .Cells(i, "E") = A(2) And .Cells(i, "G") = A(3) And CStr(.Cells(i, "F")) = A(4) Then
½Æ»s¥N½X
¦p¦A¤£¦æ½ÐªþÀÉ

²Ä¤@­ÓListBoxÀ³¸Ó¬O¦C¥X¬Ûªñªº«á¡A³Ñ¾lªº¸ê®Æ¦A¥H¤u§@ªí2"¼Æ¶q"¤j¤pÅã¥Ü
Åã¥Ü¦b­þ¸Ì!

¤£¤F¸Ñªº¬O ªþ¹Ïªºªí¤@©Îªí¤G ÅÞ¿è¦p¦ó©wªº

·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¥»©«³Ì«á¥Ñ c_c_lai ©ó 2016-10-27 09:56 ½s¿è

¦^´_ 46# starbox520
¤@¯ë¦b¨Ï¥Î Sheets("WIP").Cells(i, "F")  ®É¡A§Ú­Ì§¡½T«H
Sheets("WIP").Cells(i, "F") ·|¶Ç¦^­È 950 (Á|¨Ò°²³])¡A¦P®É
A(4) ­È¥ç¬O  950 (Á|¨Ò°²³])¡C
¸gÆ[¹î Sheets("WIP") ªº F Ä椺¦s©ñ­È§¡¬° ¼Æ­È«¬ºA (Double)¡A
·í§A±N¨âªÌ°µ¹ïµ¥ªº¤ñ¹ï®É«K·|²£¥Í¤£µ¥­È¡A«Kµø¬° "¨âªÌ" ¶¡±ø¥ó¤£²Å¦X (¤£¦¨¥ß)
(¤@­Ó¬°¦r¦ê«¬ºA¡B¥t¤@­Ó¬°Âùºë·Ç [¤ñ¹ï®É¥¦¤£·|¦Û°ÊÂà´«¬°¦r¦ê«¬ºA]) ¦Ó¸õ¹L¤£³B²z¡C
µ²ªG¡A±µ¤U¨Ó·í§A±ý±N«e­±¸g "¤ñ¹ï¹B§@" ²£¥Íªº Ar ÅܼƮ³¨Ó¹B¥Î®É¡A
¦]¤§«e©Ò¦³¤ñ¹ï§¡µL¤@±ø¥ó¦¨¥ß¡AAr ¤º®e­È·íµM¬°ªÅ­È (Empty)¡A
¬O¬G·í®³¥¦­n Assign ­Èµ¹ .List ®É¡A«K²£¥Í¤F§A¬Ý¨ìªºµe­±µ²§½¤F¡C
  1. If .Cells(i, "A") = A(1) And .Cells(i, "E") = A(2) And .Cells(i, "G") = A(3) And .Cells(i, "F") = A(4) Then
½Æ»s¥N½X
­×§ï¦¨
  1. If .Cells(i, "A") = A(1) And .Cells(i, "E") = A(2) And .Cells(i, "G") = A(3) And CStr(.Cells(i, "F")) = A(4) Then
½Æ»s¥N½X
«K OK ¤F¡C

TOP

¥»©«³Ì«á¥Ñ GBKEE ©ó 2016-10-27 13:53 ½s¿è

¦^´_ 48# c_c_lai
2003ª©¨S³o°ÝÃD



   


PS:TR±Æ¾÷&²£¥X¼Ò²Õ
­×¥¿
  1. Private Sub Ex_Customer_Package()
  2.     Dim i As Integer, ii As Integer, Ar
  3.     Sh_Ar = Ar:    i = 2
  4.     With Sheets("¤u§@ªí2")
  5.         Do While .Cells(i, 1) <> ""
  6.             If .Cells(i, 1) = Sh_Rng And .Cells(i, 2) = Sh_Rng(1, 2) Then
  7.             If IsEmpty(Ar) Then ReDim Ar(1 To 1) Else ReDim Preserve Ar(1 To UBound(Ar) + 1)
  8.             Ar(UBound(Ar)) = .Cells(i, 1).Resize(, 4)
  9.             End If
  10.             i = i + 1
  11.         Loop
  12.     End With
  13.     If IsEmpty(Ar) Then Exit Sub
  14.     If UBound(Ar) = 1 Then
  15.         Sh_Ar = Ar(1)
  16.         Else
  17.     Sh_Ar = Application.Transpose(Application.Transpose(Ar))
  18.     End If
  19. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 48# c_c_lai


    ¦^CC¤j
    ­×§ï«á°ÝÃDÁÙ¬O¦s¦b...
    TTR001.rar (566.99 KB)

TOP

        ÀR«ä¦Û¦b : ¥ÌÄ@°µ¡BÅw³ß¨ü¡C
ªð¦^¦Cªí ¤W¤@¥DÃD