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

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

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

ZA0004001.png
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. Private Sub UserForm_Initialize()
  14.     Set xSh = Sheets("²M³æ")
  15.     '«Å§i xSH = Sheets("²M³æ")

  16.     MultiPage1.Value = 0
  17.     Show_List
  18.     Dim i As Single, Rng As Range
  19.     Set d = CreateObject("scripting.dictionary")
  20.     '«Å§i d = «Ø¥ß¨Ã¶Ç¦^ ActiveX ª«¥ó «Ø¥ß¤@­Ó Dictionaryª«¥ó
  21.     Set Rng = xSh.Cells(5, "D")
  22.     'xSH = Sheets("°ò¥»¸ê®Æ") Rng =Àx¦s®æ Cells(²Ä5Äæ,C¦C)
  23.     Do While Rng <> ""
  24.     'Rng = Àx¦s®æ Cells(²Ä5Äæ,C¦C) ¤£µ¥©ó  ªÅ¥Õ
  25.         If d.exists(Rng.Value) = False Then
  26.             '
  27.             Set d(Rng.Value) = Rng
  28.         Else
  29.             Set d(Rng.Value) = Union(Rng, d(Rng.Value))
  30.         End If
  31.         Set Rng = Rng.Offset(1)
  32.     Loop
  33.     ComboBox1.List = d.KEYS
  34.     'ComboBox1 µ¥©ó d KEYS
  35. End Sub

  36. Private Sub SpinButton1_Change()
  37.     ³æ¸¹.Caption = SpinButton1
  38.     Msg³æ¸¹ = True
  39.     Show_List
  40.     Msg³æ¸¹ = False
  41. End Sub

  42. Private Sub Show_List()
  43.     Dim i As Integer, E As Variant
  44.     Set xRng(1) = xSh.Range("c4").CurrentRegion
  45.     With xSh
  46.         i = .Columns.Count
  47.         Set xRng(1) = .Range("c4").CurrentRegion
  48.         Set xRng(2) = .Cells(1, i).CurrentRegion
  49.         xRng(2).Clear
  50.         If Msg³æ¸¹ Then
  51.             .Cells(1, i) = "³æ¸¹"
  52.             .Cells(2, i) = SpinButton1
  53.         Else
  54.         End If
  55.         Set xRng(2) = .Cells(1, i).CurrentRegion
  56.     End With
  57.     Set xRng(3) = xRng(2).Cells(1).Offset(, -20).Resize(, xRng(1).Columns.Count)
  58.     xRng(3).CurrentRegion.Clear
  59.     xRng(1).AdvancedFilter xlFilterCopy, xRng(2), xRng(3)
  60.     Set xRng(3) = xRng(3).CurrentRegion
  61.     If xRng(3).Rows.Count > 2 Then
  62.         Set xRng(3) = xRng(3).Rows("2:" & xRng(3).Rows.Count)
  63.     Else
  64.         Set xRng(3) = xRng(3).Rows(2)
  65.     End If
  66.     With ListBox1
  67.         .ColumnHeads = True
  68.         .ColumnCount = -1
  69.         .RowSource = xRng(3).Address(, , , 1, 1)
  70.     End With
  71.     xRng(2).Clear
  72. End Sub
½Æ»s¥N½X
ZA0004001.rar (33.14 KB)
excel¾Ç²ß¤¤!¿v¥X¹Ú·Q
https://www.youtube.com/channel/UCPAiRNsHX3k_omp0YDgfDiA

¥»©«³Ì«á¥Ñ kkooo15 ©ó 2019-7-29 23:25 ½s¿è

¦^´_ 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)
excel¾Ç²ß¤¤!¿v¥X¹Ú·Q
https://www.youtube.com/channel/UCPAiRNsHX3k_omp0YDgfDiA

TOP

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

TOP

¦^´_ 3# ciboybj

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

ZA0004001-2.rar (34.69 KB)

excel¾Ç²ß¤¤!¿v¥X¹Ú·Q
https://www.youtube.com/channel/UCPAiRNsHX3k_omp0YDgfDiA

TOP

        ÀR«ä¦Û¦b : ¤f»¡¦n¸Ü¡B¤ß·Q¦n·N¡B¨­¦æ¦n¨Æ¡C
ªð¦^¦Cªí ¤W¤@¥DÃD