[µo°Ý] Ãö©óUserForm1¤¤ªºListBoxÀ³¥Î

av8d

1. Private Sub ListBox2_Click()
   
2.     For i = 0 To ListBox1.ListCount - 1
   
3.         If ListBox2.Selected(i) = True Then
   
4.             ActiveWorkbook.Sheets(14).Select
   
5.             Cells(1, 1).Select
   
6.         End If
   
7.     Next
   
8. End Sub

½Æ»s¥N½X

·í¿ï¾Ü²Ä¤@­Ó¿ï¶µ®É¡A§Ú­n¿ï¾Ü¦b¤u§@ªí14ªºA1 ¡A§YCells(1, 1)
·í¿ï¾Ü²Ä¤G­Ó¿ï¶µ®É¡A§Ú­n¿ï¾Ü¦b¤u§@ªí14ªºF1 ¡A§YCells(1, 6)
·í¿ï¾Ü²Ä¤T­Ó¿ï¶µ®É¡A§Ú­n¿ï¾Ü¦b¤u§@ªí14ªºK1 ¡A§YCells(1, 11)
·í¿ï¾Ü²Ä¥|­Ó¿ï¶µ®É¡A§Ú­n¿ï¾Ü¦b¤u§@ªí14ªºP1 ¡A§YCells(1, 16)
·í¿ï¾Ü²Ä¤­­Ó¿ï¶µ®É¡A§Ú­n¿ï¾Ü¦b¤u§@ªí14ªºA24 ¡A§YCells(24, 1)
·í¿ï¾Ü²Ä¤»­Ó¿ï¶µ®É¡A§Ú­n¿ï¾Ü¦b¤u§@ªí14ªºF24 ¡A§YCells(24, 6)
·í¿ï¾Ü²Ä¤C­Ó¿ï¶µ®É¡A§Ú­n¿ï¾Ü¦b¤u§@ªí14ªºK24 ¡A§YCells(24, 11)
·í¿ï¾Ü²Ä¤K­Ó¿ï¶µ®É¡A§Ú­n¿ï¾Ü¦b¤u§@ªí14ªºP24 ¡A§YCells(24, 16)
¥H¦¹Ãþ±À¡A¹w­p·|¦³50­Ó¿ï¶µ¡A¨C¥|­Ó¿ï¶µ·|´«¦C¡A½Ð°Ý¸Ó¦p¦ó¼¶¼g¡HÁÂÁÂ

¥Ø«e°ß¤@·Q¨ìªº¤èªk´N¬O¼g50¦¸

1.             If i = 0 Then Cells(1, 1).Select
   
2.             If i = 1 Then Cells(1, 6).Select
   
3.             If i = 2 Then Cells(1, 11).Select
   
4.             If i = 3 Then Cells(1, 16).Select

½Æ»s¥N½X

Andy2483

¦^´_ 1# av8d


    ÁÂÁ«e½úµoªí¦¹¥DÃD
«Ü¦³·N«äªº±¡¹Ò!
¤è«K´£¨Ñ½d¨ÒÀɶÜ?

singo1232001

¦^´_ 1# av8d

Andy2483

¦^´_ 3# singo1232001


   ÁÂÁ«e½ú
¼F®`¤F!
¤G¤¸¤@¦¸¤èµ{¦¡À³¥Î¤W¤F!
«á¾Ç¹ï¥N¼Æ¤]«Ü¦³¿³½ì,¦³¬ÝÀ´,ª¾¹D«ç»òÀ³¥Î¤F!
ÁÂÁ«e½ú

(¤£·Q¥Î¤èµ{¦¡¤]¥i¥H¥Îselect case)
Àµ½Ð«e½ú¼·ªÅ³]­pµ{¦¡½X«ü¾É«á¾Ç

singo1232001

¦^´_ 4# Andy2483


'    c = 5 * L1 - 4
¥u¦³³o¬q¥i§ï¦¨
Select Case L1
Case 1: c = 1
Case 2: c = 6
Case 3: c = 11
Case 4: c = 16
End Select

rªº³¡¤À¤]¯à§ï ¦ý½d³ò¤ñ¸û¤j ­n§ï«Ü¦h ©Ò¥H¤£¤j«Øij(1,24,37.......)¤j·§­n¦C13¶µ¥H¤W ¨Ã¥B­ìpo¥i¯àÁÙ­n§ó¦hªº¿ï¶µ ©Ò¥H«Øij¥u¥Î¤G¤¸¤@¦¸¤èµ{¦¡
cªº³¡¤À¦]¬°¥u¦³1,6,11,16¥|­Ó¿ï¶µ ©Ò¥H¥i¥H¥Îselect case³B²z

Andy2483

¦^´_ 5# singo1232001

ÁÂÁ«e½ú«ü¾É
«á¾ÇÂdzo¥DÃD·Q·dÀ´  Case«ç»ò¥Î
¥H¤U¤ß±oµù¸Ñ¦p¦³¿ù,½Ð«e½ú«ü¥¿»P«ü¾É
ÁÂÁ«e½ú

Private Sub ListBox1_Click()
LL = ListBox1.ListIndex + 1
If LL > 0 Then
    L0 = Int((LL - 1) / 4) + 1
    r = 23 * L0 - 22
    L1 = LL - ((L0 - 1) * 4)
    '¡ôCase­n¦³«e¦]! L1 ³o­Ó«e¦]¥i¯à¦³¦h­Ó
    Select Case L1
    '¡ô¦b Select Case »P End Select¤§¶¡¦C¥X«e¦]«áªGªººØÃþ
    Case 1: c = 1
    '¡ô·íL1¬O 1 ®É ¥H:²Å¸¹°Ï¹j!¶}©l³¯­zµ²ªG(¥Oc=1)
    Case 2: c = 6
    Case 3: c = 11
    Case 4: c = 16
    End Select
Unload Me
Cells(r, c).Select
End If
End Sub

lee88

¦^´_ 1# av8d
°Ñ¦Ò¬Ý¬Ý
UserForm ªºµ{¦¡½X

1. Dim Sh As Worksheet
   
2. Private Sub UserForm_Initialize()
   
3.     Set Sh = ActiveSheet
   
4.     ListBox1_¤º®e¨î©w
   
5. End Sub
   
6. Private Sub ListBox1_Click()
   
7.     With ListBox1
   
8.         If .ListIndex = -1 Then Exit Sub
   
9.        Sh.Range(.List(.ListIndex, 1)).Select
   
10.       Selection = .List(.ListIndex, 0) & "  " & .List(.ListIndex, 1)  'ÅçÃÒ ¶µ¥Ø»P¦ì¸m
    
11.     End With
    
12. End Sub
    
13. Private Sub ListBox1_¤º®e¨î©w()
    
14.     Dim Ar(), R As Integer, i As Integer, ii As Integer
    
15.     Ar = Array("A", "F", "K", "P")            '«ü©wÄd¦ì
    
16.     R = 0
    
17.     With ListBox1
    
18.         .ColumnCount = 2   ' Åã¥Ü2­ÓÄæ¦ì
    
19.         .Font.Size = 14
    
20.         For i = 0 To 49 Step 4
    
21.             R = IIf(i > 3, R + 1, 0)                 '¶¡¹j14¦Cªº­p¼Æ
    
22.             For ii = 0 To 3
    
23.              If i + ii + 1 > 50 Then Exit For
    
24.                 .AddItem
    
25.                 .List(i + ii, 0) = "¶µ" & i + ii + 1
    
26.                 .List(i + ii, 1) = Ar(ii) & IIf(R = 0, 1, R * 13 + R)   '¶¡¹j14¦Cªº¦ì¸m
    
27.             Next
    
28.     Next
    
29. End With
    
30. End Sub

½Æ»s¥N½X

av8d

¦^´_ 3# singo1232001


·PÁ«e½úªº¸Ñµª¡A«Ü¨ü¥Î¡A
¥Ø«e§Ú§ï¼g¤F¤@¤U¡A¦ý¬O¤S¥d¦í¤F¡A
¦pF1¦pªG¬OªÅ¡A§Ú³]©w¥[¤Jªº¶µ¥Ø·|²¤¹L¡A
¦ý«ü©w¦ì¸m´N·|¦ì²¾¡A¸Ó¦p¦ó§ï¼g¡AÁÂÁ¡C

1. Private Sub UserForm_Activate()
   
2.     Dim j As Integer
   
3.    
   
4.     For j = 1 To 300 Step 23
   
5.         If ActiveWorkbook.Sheets(1).Cells(j, 1).Value <> "" Then ListBox1.AddItem ActiveWorkbook.Sheets(1).Cells(j, 1).Value & "¡G" & ActiveWorkbook.Sheets(1).Cells(j + 20, 4).Value
   
6.         
   
7.         If ActiveWorkbook.Sheets(1).Cells(j, 6).Value <> "" Then ListBox1.AddItem ActiveWorkbook.Sheets(1).Cells(j, 6).Value & "¡G" & ActiveWorkbook.Sheets(1).Cells(j + 20, 9).Value
   
8.         
   
9.         If ActiveWorkbook.Sheets(1).Cells(j, 11).Value <> "" Then ListBox1.AddItem ActiveWorkbook.Sheets(1).Cells(j, 11).Value & "¡G" & ActiveWorkbook.Sheets(1).Cells(j + 20, 14).Value
   
10.                
    
11.         If ActiveWorkbook.Sheets(1).Cells(j, 16).Value <> "" Then ListBox1.AddItem ActiveWorkbook.Sheets(1).Cells(j, 16).Value & "¡G" & ActiveWorkbook.Sheets(1).Cells(j + 20, 19).Value
    
12.     Next
    
13. End Sub

½Æ»s¥N½X

singo1232001

¦^´_ 8# av8d


³o²o§è¨ìÅÞ¿è¨Æ«e³W¹º
§Úªº«Øij¬O listbox ¼W¥[¿ï¶µ®É
­Y¬°ªÅ  ¤@¼ËÅý¥¦Åã¥Ü¤å¦r ¦ý¤£­n¥Î²¤¹Lªº¤è¦¡³B²z  ¥ÎªÅ¥Õ©ÎªÌ"ªÅ"¦r¨ú¥N
¨Ã¥B ¤@¶}©l´Nª½±µÅý¥LµLªk¥¿±`°õ¦æ´N¦n

Private Sub ListBox1_Click()
LL = ListBox1.ListIndex + 1
if listbox1.list(1,0)="" then exit sub
...
...
...
...
¤]¥i¥H³Ì«á¦h¥[­Ó§PÂ_ Åý¥L©¿²¤
if r=1,c=6 then msgbox "¦¹¶µ¬°ªÅ"  : Exit sub
Unload Me
cells(r,c).select
End sub

­Y¥h°Ê¾ã­Ó¥D³W«h ¨º·|²£¥Í§ó½ÆÂøªº°ÝÃD ¦Ó¥B¨Æ«á·|«ÜÃø°£¿ù

av8d

¦^´_ 8# av8d
¦^´_ 9# singo1232001

·PÁ¨â¦ì«e½ú¡A¨ü¯q¨}¦h¡A¼gªk¤£¦P¡A¥Øªº¬Û¦P¡A¥Î³~¨Ï¥Î¼sªx¡C