[µo°Ý] ¿ï¾ÜListBox¤º¸ê®Æ¦p¦ó©ñ¨ì¨ä¥LUserFormªºTextBox

kkooo15

¤U¸ü (67.49 KB)

2019-7-27 12:21

°ÝÃD¤@
½Ð°Ý­n¦p¦ó¥i¥HÂI¿ïListBoxªº¨ä¤¤ªº¸ê®Æ«á
«ö½T©w¥i¥H±NÂI¿ïªº"½s¸¹"©ñ¨ìUserForm2ªºTextBox1

°ÝÃD¤G
¦pªG§Ú¦³¤G­ÓUserForm
§Ú¿ïUserForm2¨Ï¥Î¬d¸ß®É¡A"½s¸¹"´N·|¦s¦bUserForm2ªºTextBox1
§Ú¿ïUserForm3¨Ï¥Î¬d¸ß®É¡A"½s¸¹"´N·|¦s¦bUserForm3ªºTextBox1

1. Option Explicit
   
2. Dim xSh As Worksheet, xRng(1 To 3) As Range, Msg³æ¸¹ As Boolean, d As Object, d2 As Object, d3 As Object
   
3. Private Sub CommandButton6_Click()
   
4.     If TextBox1.Text <> "" Then
   
5.     '±N UserForm2¬d¥X¨Óªº½s¸¹ ¸ê®Æ©ñ¤J UserForm1ªºTextBox1
   
6.     UserForm1.TextBox1.Text = UserForm2.TextBox1.Text
   
7.     '±N TextBox1 ¸ê®Æ©ñ¤J TBox1
   
8.     Unload UserForm2
   
9.     'Ãö³¬ªí³æ
   
10.     Else
    
11.     End If
    
12. End Sub
    
13. 
    
14. Private Sub UserForm_Initialize()
    
15.     Set xSh = Sheets("²M³æ")
    
16.     '«Å§i xSH = Sheets("²M³æ")
    
17. 
    
18.     MultiPage1.Value = 0
    
19.     Show_List
    
20.     Dim i As Single, Rng As Range
    
21.     Set d = CreateObject("scripting.dictionary")
    
22.     '«Å§i d = «Ø¥ß¨Ã¶Ç¦^ ActiveX ª«¥ó «Ø¥ß¤@­Ó Dictionaryª«¥ó
    
23.     Set Rng = xSh.Cells(5, "D")
    
24.     'xSH = Sheets("°ò¥»¸ê®Æ") Rng =Àx¦s®æ Cells(²Ä5Äæ,C¦C)
    
25.     Do While Rng <> ""
    
26.     'Rng = Àx¦s®æ Cells(²Ä5Äæ,C¦C) ¤£µ¥©ó  ªÅ¥Õ
    
27.         If d.exists(Rng.Value) = False Then
    
28.             '
    
29.             Set d(Rng.Value) = Rng
    
30.         Else
    
31.             Set d(Rng.Value) = Union(Rng, d(Rng.Value))
    
32.         End If
    
33.         Set Rng = Rng.Offset(1)
    
34.     Loop
    
35.     ComboBox1.List = d.KEYS
    
36.     'ComboBox1 µ¥©ó d KEYS
    
37. End Sub
    
38. 
    
39. Private Sub SpinButton1_Change()
    
40.     ³æ¸¹.Caption = SpinButton1
    
41.     Msg³æ¸¹ = True
    
42.     Show_List
    
43.     Msg³æ¸¹ = False
    
44. End Sub
    
45. 
    
46. Private Sub Show_List()
    
47.     Dim i As Integer, E As Variant
    
48.     Set xRng(1) = xSh.Range("c4").CurrentRegion
    
49.     With xSh
    
50.         i = .Columns.Count
    
51.         Set xRng(1) = .Range("c4").CurrentRegion
    
52.         Set xRng(2) = .Cells(1, i).CurrentRegion
    
53.         xRng(2).Clear
    
54.         If Msg³æ¸¹ Then
    
55.             .Cells(1, i) = "³æ¸¹"
    
56.             .Cells(2, i) = SpinButton1
    
57.         Else
    
58.         End If
    
59.         Set xRng(2) = .Cells(1, i).CurrentRegion
    
60.     End With
    
61.     Set xRng(3) = xRng(2).Cells(1).Offset(, -20).Resize(, xRng(1).Columns.Count)
    
62.     xRng(3).CurrentRegion.Clear
    
63.     xRng(1).AdvancedFilter xlFilterCopy, xRng(2), xRng(3)
    
64.     Set xRng(3) = xRng(3).CurrentRegion
    
65.     If xRng(3).Rows.Count > 2 Then
    
66.         Set xRng(3) = xRng(3).Rows("2:" & xRng(3).Rows.Count)
    
67.     Else
    
68.         Set xRng(3) = xRng(3).Rows(2)
    
69.     End If
    
70.     With ListBox1
    
71.         .ColumnHeads = True
    
72.         .ColumnCount = -1
    
73.         .RowSource = xRng(3).Address(, , , 1, 1)
    
74.     End With
    
75.     xRng(2).Clear
    
76. End Sub

½Æ»s¥N½X

ZA0004001.rar (33.14 KB)

¤U¸ü¦¸¼Æ: 3

2019-7-27 12:23

kkooo15

¦^´_ 3# ciboybj

¤Ó·PÁ¤F
§Ú¤S¥i¥H¦b©¹«e¤@¨B¤F

ciboybj

¦^´_ 1# kkooo15
§Ú¦³¤@­ÓÃþ¦üªºµ{¦¡¤ù¬q
¨Ï¥ÎªÌÂùÀ»ListBox1¤¤ªº¸ê®Æ«á (·íµM§A¤]¥i¥H¿ï¾Ü¥Î³æÀ»)
¥Îfor°j°é¬d¥X­þ¤@µ§¸ê®Æ³QÂIÀ» µM«áListBox1.List(i) À³¸Ó´N¥i¥H®³¨ì¸ê®Æ

1. Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
   
2.     For i = 0 To ListBox1.ListCount - 1
   
3.         If ListBox1.Selected(i) = True Then
   
4.             TextBox1.Text = ListBox1.List(i)
   
5.             ListBox1.Visible = False
   
6.         End If
   
7.     Next i
   
8. End Sub

½Æ»s¥N½X

kkooo15

¦^´_ 1# kkooo15

°ÝÃD¤G
¦pªG§Ú¦³¤G­ÓUserForm
§Ú¿ïUserForm2¨Ï¥Î¬d¸ß®É¡A"½s¸¹"´N·|¦s¦bUserForm2ªºTextBox1
§Ú¿ïUserForm3¨Ï¥Î¬d¸ß®É¡A"½s¸¹"´N·|¦s¦bUserForm3ªºTextBox1

§Ú·Q¨ì¿ìªk¸Ñ¨M¤F¡C

´N¬O¦b¶}ªí³æªº®É­Ô§â¸ê®Æ²MªÅ¡C

1. Sub show1()
   
2.     UserForm1.show
   
3. End Sub
   
4. Sub show2()
   
5.     '¶}±Òªí³æ®É²MªÅTEXTBOX1¤ºªº¸ê®Æ
   
6.     UserForm2.TextBox1.Text = ""
   
7.     UserForm2.show
   
8. End Sub
   
9. Sub show3()
   
10.     '¶}±Òªí³æ®É²MªÅTEXTBOX1¤ºªº¸ê®Æ
    
11.     UserForm3.TextBox1.Text = ""
    
12.     UserForm3.show
    
13. End Sub

½Æ»s¥N½X

µM«á±o¨ì¸ê®Æ«á«ö½T©w®É
UserForm2¸òUserForm3³£©ñ¸ê®Æ

1. Private Sub CommandButton6_Click()
   
2.     If TextBox1.Text <> "" Then
   
3.     '±N UserForm2¬d¥X¨Óªº½s¸¹ ¸ê®Æ©ñ¤J UserForm1ªºTextBox1
   
4.     UserForm2.TextBox1.Text = UserForm1.TextBox1.Text
   
5.     UserForm3.TextBox1.Text = UserForm1.TextBox1.Text
   
6.     '±N TextBox1 ¸ê®Æ©ñ¤J TBox1
   
7.     Unload Me
   
8.     'Ãö³¬ªí³æ
   
9.     Else
   
10.     End If
    
11. End Sub

½Æ»s¥N½X

²{¦b®t..§Ú­n«ç»ò¶i¥hÂIListBox¤º¸ê®Æ«á¡A·|©ñ¨ì¤W­ÓªºTextBox

ZA0004001-1.rar (33.63 KB)

¤U¸ü¦¸¼Æ: 7

2019-7-29 23:25