[µo°Ý] 20­Ó¬Û¦P¥\¯àComboBox¦p¦ó¥i¥H¬Ù¤u¼¶¼g?

PKKO

³Â·Ð¦U¦ì¤j¤j¸ÑÃD¤F!

¥H¤Uµ{¦¡½X¬°©T©wªº,¥Øªº¦b©ó"©m"¿é¤J«á¥i¦Û°Ê±a¥X¦W¤l
°ÝÃD¤@ : ­Y¦³20­Ó¬Û¦P¥\¯à¤§µ{¦¡½X,¸Ó¦p¦ó¼¶¼g?¥i½Ð¤j¤j­Ì´£¨Ñ±zªº¼¶¼g¤è¦¡¶Ü?

°ÝÃD¤G : ¦¹µ{¦¡½X¦bComboBox¿é¤J§¹"§Ú"¤§«á,³£·|¦Û°Ê±a¥X«á­±¦WºÙ,¨Ò¦p"§Ú·R§A"
¦ý¬O¦]¬°±a¥X"§Ú·R§A",¦]¦¹¤U©Ô¦¡¿ï³æÅܦ¨¨S±o¿ï,¥²¶·­n¦b¿é¤J§¹"§Ú"¤§«á,±N«á­±¹w³]±a¥Xªº¦WºÙ§R°£
¯d¤U"§Ú"¤@­Ó¦r,¤U©Ô¦¡¿ï³æ¤~¯à¥X²{¸ê®Æ®w¶}ÀY¬°"§Ú"ªº©Ò¦³¦WºÙ,¨Ò¦p¥X²{"§Ú·R§A","§Ú«ë§A","§Ú¦n°ª"¤§Ãþªº
­n¦p¦óÁקK³oºØµ~¹Ò,¿é¤J§¹¥ô¦ó¦r¤§«á,³£¤£·|¦Û°Ê±a¥X,¦Ó¥i¥H¥Ñ¤U©Ô¦¡¿ï³æ¿ï¨ú©O¡H

1. Private Sub ComboBox1_Change()
   
2.     Dim Arr
   
3.     '§ä¥X¬Û¦P©m¦Wªº©µÄò
   
4.     For i = 1 To Range("A65536").End(xlUp).Row
   
5.         V = UserForm1.ComboBox1
   
6.         L = Len(UserForm1.ComboBox1)
   
7.         If Left(Range("A" & i), L) = Left(UserForm1.ComboBox1, L) Then
   
8.             Name1 = Name1 & "," & Range("A" & i)
   
9.         End If
   
10.     Next
    
11.     Name1 = Mid(Name1, 2, 10000) '¥h°£","¸¹
    
12.     Arr = Split(Name1, ",")
    
13.     UserForm1.ComboBox1.List = Arr
    
14. End Sub

½Æ»s¥N½X

PKKO

¦^´_ 22# GBKEE

·PÁ¶Wª©¤j¤jªº¥Î¤ß
¤p§Ì¤]·Q¨ì¤F¤@­Ó¤èªk¥i¥H¸Ñ¨M³o­Ó°ÝÃD
¨Ï¥ÎDropButtonClick´N¤£·|¦³³o­Ó°ÝÃD¤F
¤w¥i¦¨¥\¸Ñ¨M,«D±`·PÁ¶Wª©¤j¤j

stillfish00

¦^´_ 23# PKKO
·s¼Wª«¥óÃþ§O¼Ò²Õ clsComboBox

1. Private WithEvents mComboBox As MSForms.ComboBox
   
2. 
   
3. Public Sub InitialControl(ByRef oControl)
   
4.   Set mComboBox = oControl
   
5. End Sub
   
6. 
   
7. Private Sub mComboBox_Change()
   
8.   mComboBox.Clear
   
9. End Sub
   
10. 
    
11. Private Sub mComboBox_DropButtonClick()
    
12.   Dim ar, i As Long, j As Long
    
13.   ar = Application.Transpose(Range([A1], [A1].End(xlDown)))
    
14.   For i = 1 To UBound(ar)
    
15.     If ar(i) Like mComboBox.Text & "*" Then
    
16.       j = j + 1
    
17.       ar(j) = ar(i)
    
18.     End If
    
19.   Next
    
20.   
    
21.   If j > 0 Then
    
22.     ReDim Preserve ar(1 To j)
    
23.     mComboBox.List = ar
    
24.   End If
    
25. End Sub

½Æ»s¥N½X

UserForm:

1. Private gcolComboBoxs As Collection
   
2. 
   
3. Private Sub UserForm_Initialize()
   
4.   Dim oComboBox As clsComboBox, x
   
5.   
   
6.   Set gcolComboBoxs = New Collection
   
7.   
   
8.   For Each x In Me.Controls
   
9.     If StrComp(TypeName(x), "ComboBox", vbTextCompare) = 0 Then
   
10.       Set oComboBox = New clsComboBox
    
11.       oComboBox.InitialControl x
    
12.       gcolComboBoxs.Add oComboBox
    
13.     End If
    
14.   Next
    
15. End Sub

½Æ»s¥N½X

PKKO

¦^´_ 22# GBKEE

«z,¤p§Ì¨S¬Ý¹L³o­ÓªF¦è,¬ã¨s¤@¤U¥ý,·PÁ¤j¤j!

GBKEE

¦^´_ 21# PKKO

¤U¸ü (97.23 KB)

2014-12-6 13:49

1. Option Explicit
   
2. Dim UserComboBox() As New Class1
   
3. Private Sub UserForm_Initialize() 'ªí³æªì©l¤Æªº¨Æ¥ó
   
4.     Dim e As Control, i As Integer
   
5.     For Each e In Me.Controls
   
6.         If InStr(e.Name, "ComboBox") Then
   
7.             ReDim Preserve UserComboBox(0 To i)
   
8.             Set UserComboBox(i).xlComBox = e
   
9.             i = i + 1
   
10.         End If
    
11.     Next
    
12. End Sub
    
13. Private Sub CommandButton1_Click()
    
14.     Unload Me   'Ãö³¬ªí³æ
    
15. End Sub

½Æ»s¥N½X

[ Class1 ] (ª«¥óÃþ§O¼Ò²Õ)ªºµ{¦¡½X

1. Option Explicit
   
2. Public WithEvents xlComBox As MSForms.ComboBox
   
3. Private Sub xlComBox_DropButtonClick()  '«ö¤UComboBox¤U©Ô½bÀY«ö¶s
   
4.     Dim i As Long, S As String, Name1 As String, Arr As Variant
   
5.     If xlComBox.ListIndex > -1 Then Exit Sub
   
6.     'ComboBoxªº­È,¤£¦bComboBox.Listªº²M³æ¤¤¿ï¨úªº: ComBox.ListIndex=-1
   
7.     S = UCase(Trim(UserForm1.TextBox1))
   
8.     For i = 1 To Range("A65536").End(xlUp).Row
   
9.         If UCase(Range("A" & i)) Like S & "*" And S <> "" Then
   
10.             Name1 = Name1 & "," & Range("A" & i)
    
11.         End If
    
12.     Next
    
13.     Arr = Split(Mid(Name1, 2), ",")
    
14.     With xlComBox
    
15.         If UBound(Arr) > -1 Then
    
16.             .List = Arr
    
17.             .Value = Arr(0)
    
18.         Else
    
19.             .Clear
    
20.         End If
    
21.         .DropDown
    
22.     End With
    
23. End Sub

½Æ»s¥N½X

PKKO

¦^´_ 20# GBKEE


    ·PÁ¶Wª©¤j¤j,¦ý¦p¦¹ªº¸Ü,·|¤Ó½ÆÂø,¦]¬°§Ú¦³«D±`¦h­Ó³£»Ý­n¤U©Ô¦¡¿ï³æ

¨C¤@­Ó¤U©Ô¦¡¿ï³æ³£­n·f°t¤@­Ótextbox ¥i¯à¼È¤£¦Ò¼{

ÁÙ¬O¥ý±Ë¥h³v¦r¿z¿ïªº¥\¯à,¦Ó¿ï¾Ü¥u¯à¥Î²Ä¤@­Ó¦r¿z¿ï¦n¤F

GBKEE

¦^´_ 19# PKKO
ªí³æ¥[¤@TextBox±±¨î¶µ ¸Õ¸Õ¬Ý

1. Option Explicit
   
2. Private Sub TextBox1_Change()
   
3.     Dim i As Long, Name1, Arr
   
4.     For i = 1 To Range("A65536").End(xlUp).Row
   
5.         If Range("A" & i) Like Trim(TextBox1) & "*" And Trim(TextBox1) <> "" Then
   
6.             Name1 = Name1 & "," & Range("A" & i)
   
7.         End If
   
8.     Next
   
9.     Arr = Split(Mid(Name1, 2), ",")
   
10.     With ComboBox17
    
11.         If UBound(Arr) <> -1 Then
    
12.             .List = Arr
    
13.             .Value = Arr(0)
    
14.         Else
    
15.             .Clear
    
16.         End If
    
17.     End With
    
18. End Sub
    
19. Private Sub ComboBox17_Change()
    
20.     ComboBox17.DropDown
    
21. End Sub

½Æ»s¥N½X

PKKO

¦^´_ 18# GBKEE


    §Ú³£¬O¥Î·Æ¹«ÂIªº,¤@¼Ë·|¦Û°Ê±a¥X,¦pªG¯uªº¨S¿ìªkªº¸Ü,§Ú´N³W©w¥u·|¿z¿ï²Ä¤@­Ó¦r¥À,´N¤£¯à¿z¿ï¤@­Ó¦r¥H¤W,

¦ý¦Û°Ê±a¥Xªº°ÝÃD´N·|³Q¸Ñ¨M,¥Ø«e¥u¦³·Q¨ì³o­Ó¤è¦¡,·PÁ±z!!

GBKEE

¦^´_ 17# PKKO
¥Î·Æ¹«¿ï,¤£­n¥ÎÁä½L¿ï

PKKO

¦^´_ 16# GBKEE


   

¤U¸ü (2.13 KB)
¦Û°Ê±a¥X

2014-12-6 06:30

¨Ï¥Î¤j¤j±zªºµ{¦¡¤§«á,½T¹ê¿é¤J"§Ú",¥u·|¥X²{"§Ú"

¦ý¬O±q²Ä¤G¦¸¶}©l,¥u­n¿é¤J"§Ú",³£·|¦Û°Ê±a¥X"¦n°ª"(¦p¹Ï¤ù©Ò¥Ü)

­Y¬OÃö³¬USERFORM¦A¥´¶},²Ä¤@¦¸³£·|¥¿±`,¦ý²Ä¤G¦¸¶}©l¤§«á¨C¤@¦¸³£·|¦Û°Ê±a¥X"¦n°ª"

¤£ª¾¬°¦ó..

¦Ó­Y¬O§â¦Û°Ê±a¥Xªº"¦n°ª"¥Î¤â°Ê§R°£,½T¹ê¤U©Ô¿ï³æ¬O·|¥X²{"§Ú","§Ú¦n°ª","§Ú¦n¸G"

¦ý¨Ï¥ÎªÌ¨C¦¸³£­n§â¦Û°Ê±a¥Xªº¤å¦r§R°£,¹ê¦b»á¬°¤£«K...³Â·Ð¤j¤j¤F,©êºp