[µo°Ý] ListboxÄæ¦ìÅã¥Üªº°ÝÃD

lifedidi

½Ð°Ý¤j¤j ¤p§Ì¦bsheet5¦³30´X­ÓÄæ¦ì±qDÄæ¦ì~ANÄæ¦ì ­nÅã¥Ü¦blistbox¤W

¦ý¬O¥X²{¤F°ÝÃD¡G

¤U¸ü (24.66 KB)

2013-12-12 08:25

²Ä¤QÄæ¦ì¥H«á´N¤Þ­zµL®Ä¡A

½s½X¦p¤U¡AÁÂÁ¡C

1. Dim i As Integer
   
2. 
   
3. With Listbox1
   
4.     .ColumnCount = 37
   
5.     .ColumnWidths = "20 pt;20 pt;20 pt;20 pt;20 pt;20 pt;20 pt;20 pt;20 pt;20 pt;20 pt;20 pt;20 pt;20 pt;20 pt;20 pt;20 pt;20 pt;20 pt;20 pt;20 pt;20 pt;20 pt;20 pt;20 pt;20 pt;20 pt;20 pt;20 pt;20 pt;20 pt;20 pt;20 pt;20 pt;20 pt;20 pt;20 pt"
   
6. End With
   
7. 
   
8. With Sheet5
   
9. i = 1
   
10. Do While .Cells(i, "A") <> ""
    
11.     With Listbox1
    
12.     .AddItem
    
13.     r = .ListCount
    
14.     .List(r - 1, 0) = Sheet5.Cells(i, "D")
    
15.     .List(r - 1, 1) = Sheet5.Cells(i, "E")
    
16.     .List(r - 1, 2) = Sheet5.Cells(i, "F")
    
17.     .List(r - 1, 3) = Sheet5.Cells(i, "G")
    
18.     .List(r - 1, 4) = Sheet5.Cells(i, "H")
    
19.     .List(r - 1, 5) = Sheet5.Cells(i, "I")
    
20.     .List(r - 1, 6) = Sheet5.Cells(i, "J")
    
21.     .List(r - 1, 7) = Sheet5.Cells(i, "K")
    
22.     .List(r - 1, 8) = Sheet5.Cells(i, "L")
    
23.     .List(r - 1, 9) = Sheet5.Cells(i, "M")
    
24.     .List(r - 1, 10) = Sheet5.Cells(i, "N")
    
25.     .List(r - 1, 11) = Sheet5.Cells(i, "O")
    
26.     .List(r - 1, 12) = Sheet5.Cells(i, "P")
    
27.     .List(r - 1, 13) = Sheet5.Cells(i, "Q")
    
28.     .List(r - 1, 14) = Sheet5.Cells(i, "R")
    
29.     .List(r - 1, 15) = Sheet5.Cells(i, "S")
    
30.     .List(r - 1, 16) = Sheet5.Cells(i, "T")
    
31.     .List(r - 1, 17) = Sheet5.Cells(i, "U")
    
32.     .List(r - 1, 18) = Sheet5.Cells(i, "V")
    
33.     .List(r - 1, 19) = Sheet5.Cells(i, "W")
    
34.     .List(r - 1, 20) = Sheet5.Cells(i, "X")
    
35.     .List(r - 1, 21) = Sheet5.Cells(i, "Y")
    
36.     .List(r - 1, 22) = Sheet5.Cells(i, "Z")
    
37.     .List(r - 1, 23) = Sheet5.Cells(i, "AA")
    
38.     .List(r - 1, 24) = Sheet5.Cells(i, "AB")
    
39.     .List(r - 1, 25) = Sheet5.Cells(i, "AC")
    
40.     .List(r - 1, 26) = Sheet5.Cells(i, "AD")
    
41.     .List(r - 1, 27) = Sheet5.Cells(i, "AE")
    
42.     .List(r - 1, 28) = Sheet5.Cells(i, "AF")
    
43.     .List(r - 1, 29) = Sheet5.Cells(i, "AG")
    
44.     .List(r - 1, 30) = Sheet5.Cells(i, "AH")
    
45.     .List(r - 1, 31) = Sheet5.Cells(i, "AI")
    
46.     .List(r - 1, 32) = Sheet5.Cells(i, "AJ")
    
47.     .List(r - 1, 33) = Sheet5.Cells(i, "AK")
    
48.     .List(r - 1, 34) = Sheet5.Cells(i, "AL")
    
49.     .List(r - 1, 35) = Sheet5.Cells(i, "AM")
    
50.     .List(r - 1, 36) = Sheet5.Cells(i, "AN")
    
51. 
    
52.     End With
    
53.     i = i + 1
    
54. Loop
    
55. 
    
56. End With

½Æ»s¥N½X

lifedidi

¦^´_ 11# stillfish00


    Dear ¤j¤j

¦W¤l·|¦³­«½Æªº®É­Ô (¦ý¬O³¡ªù +¦W¤l ´N¤£·|­«½Æ) §Ú¥[¤F¤@¬q

If Sheet1.Cells(i, 1) = ListBox1.List(ListBox1.ListIndex, 0) And Sheet1.Cells(i, 2) = ListBox1.List(ListBox1.ListIndex, 1) Then

´N¥i¥H¤F(À³¸Ó...)XD

ÁÂÁ¤j¤jªºÀ°¦£¡I

stillfish00

¦^´_ 10# lifedidi
¦W¦r·|­«½Æ¶Ü? ¤£·|´N§ï¥Î¦W¦r§PÂ_
If Sheet1.Cells(i, 2) = ListBox1.List(ListBox1.ListIndex, 1) Then

lifedidi

¦^´_ 9# stillfish00

    CASE.rar (19.83 KB)

¤U¸ü¦¸¼Æ: 6

2013-12-17 11:36

Àɮפ¤§Ú­n¬d¸ß¨C­Ó¤Hªº¸ê®Æ¡A

¦ý¬O§Ú³o¼Ëªº¤èªk¥u­n¹J¨ì¦P³¡ªù´N·|¥d¦b²Ä¤@¦ì¡A½Ð¤j¤j«ü±Ð¡AÁÂÁ¡C

stillfish00

¦^´_ 8# lifedidi

§Ú­n§ì¨ú"³¯¾å©ú" «h ÂI²Ä¤@¦æ Listbox1.value = ¸ê®Æ®wªº­È §Y¥i§ì¨ì¶}ÀY

¬Ý¤£À´¡Aªþ¤WÀÉ®×µ{¦¡¬Ý¬Ý

lifedidi

¦^´_ 6# stillfish00

¤j¤j¡A½Ð±Ð±z¡G

¤p§Ì²{¦b¥ÎListbox1ªº¸ê®ÆÂI¿ï«á ¥i¥H±N¸ê®Æ®wªº¸ê®Æ¥N¨ìTextbox¤W

§ÚªºListbox1²M³æªº²M³æ°²¦p¦³¨âµ§¸ê®Æ

»s³y³¡¡@³¯¾å©ú¡@¤uµ{®v¡@¨k¡@¢µ¦~
¥Í²£³¡¡@²ø©y¼z¡@±M­û¡@¡@¤k¡@¢¶¦~

ÂI²Ä¤@¦æ Listbox1.value ªº­È¬° »s³y³¡
ÂI²Ä¤G¦æ Listbox1.value ªº­È¬° ¥Í²£³¡

§Ú­n§ì¨ú"³¯¾å©ú" «h ÂI²Ä¤@¦æ Listbox1.value = ¸ê®Æ®wªº­È §Y¥i§ì¨ì¶}ÀY

¦ý¬O¤U­±ªº±¡§Î¡A«h­n§ì²Ä¤GÄæ¦ìªº¸ê®Æ¤~¯à¥¿½T§ì¨ì¡A½Ð°Ý¸Ó«ç»ò§ì©O¡H

»s³y³¡¡@¤ý¤Ñ¤~¡@¤uµ{®v¡@¨k¡@¢²¦~¡@
»s³y³¡¡@³¯¾å©ú¡@¤uµ{®v¡@¨k¡@¢µ¦~
¥Í²£³¡¡@²ø©y¼z¡@±M­û¡@¡@¤k¡@¢¶¦~

ÁÂÁ¡I

lifedidi

¦^´_ 6# stillfish00

¤£¦n·N«ä §Ú­ì©lªºDATA¬OCOPY¦P¤@­ÓÄæ¸ê®Æ ¾É­P°õ¦æ«áµ²ªG³£ªø¤@¼Ë....

¤j¤j¡AÁÂÁ§A~¤w¸g¸Ñ¨M¤F¡I¡I

stillfish00

ª½±µ¥Î¤£¦æ¶Ü?

1. Private Sub UserForm_Initialize()
   
2.   Dim ar
   
3. 
   
4.   With Sheet5
   
5.     ar = .Range(.[a2], .[a2].End(xlDown)).Resize(, 37).Value
   
6.   End With
   
7.   With ListBox1
   
8.     .ColumnCount = 37
   
9.     .ColumnWidths = "20 pt;20 pt;20 pt;20 pt;20 pt;20 pt;20 pt;20 pt;20 pt;20 pt;20 pt;20 pt;20 pt;20 pt;20 pt;20 pt;20 pt;20 pt;20 pt;20 pt;20 pt;20 pt;20 pt;20 pt;20 pt;20 pt;20 pt;20 pt;20 pt;20 pt;20 pt;20 pt;20 pt;20 pt;20 pt;20 pt;20 pt"
   
10.     .List = ar
    
11.   End With  
    
12. End Sub

½Æ»s¥N½X

¦^´_ 5# lifedidi

lifedidi

¦^´_ 4# stillfish00


ÁÂÁ¤j¤jªº¤èªk¡A

¤p§Ì¥[¤F¤@¨ÇÅܼÆ(¦]¬°Äæ¦ì¼Æ·|¤@ª½¼W¥[ ±q²Ä¤GÄæ¶}©l§ì¨ì³Ì«á¤@Äæ)¡A¦ý¬O«ç»ò§ï³£§ì¤£¨ì­È¡A

½Ð±Ð¤j¤j¡AÁÂÁ¡C

1. With Sheet5
   
2. 
   
3. i = 2
   
4. 
   
5. Do While .Cells(i, "A") <> ""
   
6.    
   
7. j = ".[A" & i & "]"
   
8.    
   
9. With Sheet5
   
10. 
    
11. ar = .Range(j, j.End(xlDown)).Resize(, 37).Value
    
12. 
    
13. End With
    
14. 
    
15. Listbox1.List = ar
    
16.    
    
17. i = i + 1
    
18.    
    
19. Loop
    
20. 
    
21. End With

½Æ»s¥N½X

stillfish00

¦^´_ 3# lifedidi
­n¯}10Äæ­­¨î¡A1¬O±q .RowSource ¤Þ¥Î¤u§@ªí½d³ò
2¬O¥Î¤Gºû°}¦C¤@¦¸µ¹¨ì .List ¡A¦p¤U

1. Private Sub UserForm_Initialize()
   
2.   Dim ar
   
3. 
   
4.   With ListBox1
   
5.       .ColumnCount = 37
   
6.       .ColumnWidths = "20 pt;20 pt;20 pt;20 pt;20 pt;20 pt;20 pt;20 pt;20 pt;20 pt;20 pt;20 pt;20 pt;20 pt;20 pt;20 pt;20 pt;20 pt;20 pt;20 pt;20 pt;20 pt;20 pt;20 pt;20 pt;20 pt;20 pt;20 pt;20 pt;20 pt;20 pt;20 pt;20 pt;20 pt;20 pt;20 pt;20 pt"
   
7.   End With
   
8.   
   
9.   With Sheet5
   
10.     ar = .Range(.[a1], .[a1].End(xlDown)).Resize(, 37).Value
    
11.   End With
    
12.   ListBox1.List = ar
    
13. 
    
14. End Sub

½Æ»s¥N½X