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

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

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

¥»©«³Ì«á¥Ñ av8d ©ó 2022-10-17 13:07 ½s¿è
  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

¦^´_ 1# av8d


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

TOP

¥»©«³Ì«á¥Ñ singo1232001 ©ó 2022-10-17 17:01 ½s¿è

¦^´_ 1# av8d

listbox¿ï¶µv2.zip (23.7 KB)

TOP

¦^´_ 3# singo1232001


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

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

TOP

¦^´_ 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

TOP

¦^´_ 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

TOP

¦^´_ 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

TOP

¦^´_ 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

TOP

¥»©«³Ì«á¥Ñ singo1232001 ©ó 2022-10-20 13:01 ½s¿è

¦^´_ 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¨Æ«á·|«ÜÃø°£¿ù

listbox¿ï¶µv3.zip (24.74 KB)

TOP

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

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

TOP

        ÀR«ä¦Û¦b : ¤Hªº²´·úªø¦b«e­±¡A¥u¬Ý¨ì§O¤Hªº¯ÊÂI¡Aµ·²@¬Ý¤£¨ì¦Û¤vªº¯ÊÂI¡C
ªð¦^¦Cªí ¤W¤@¥DÃD