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

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

¦^´_ 22# GBKEE

«z,¤p§Ì¨S¬Ý¹L³o­ÓªF¦è,¬ã¨s¤@¤U¥ý,·PÁ¤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

·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