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

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

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

¥»©«³Ì«á¥Ñ PKKO ©ó 2014-12-1 04:38 ½s¿è

³Â·Ð¦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
PKKO

TOP

¥»©«³Ì«á¥Ñ stillfish00 ©ó 2014-12-8 11:12 ½s¿è

¦^´_ 23# PKKO
·s¼Wª«¥óÃþ§O¼Ò²Õ clsComboBox
  1. Private WithEvents mComboBox As MSForms.ComboBox

  2. Public Sub InitialControl(ByRef oControl)
  3.   Set mComboBox = oControl
  4. End Sub

  5. Private Sub mComboBox_Change()
  6.   mComboBox.Clear
  7. End Sub

  8. Private Sub mComboBox_DropButtonClick()
  9.   Dim ar, i As Long, j As Long
  10.   ar = Application.Transpose(Range([A1], [A1].End(xlDown)))
  11.   For i = 1 To UBound(ar)
  12.     If ar(i) Like mComboBox.Text & "*" Then
  13.       j = j + 1
  14.       ar(j) = ar(i)
  15.     End If
  16.   Next
  17.   
  18.   If j > 0 Then
  19.     ReDim Preserve ar(1 To j)
  20.     mComboBox.List = ar
  21.   End If
  22. End Sub
½Æ»s¥N½X
UserForm:
  1. Private gcolComboBoxs As Collection

  2. Private Sub UserForm_Initialize()
  3.   Dim oComboBox As clsComboBox, x
  4.   
  5.   Set gcolComboBoxs = New Collection
  6.   
  7.   For Each x In Me.Controls
  8.     If StrComp(TypeName(x), "ComboBox", vbTextCompare) = 0 Then
  9.       Set oComboBox = New clsComboBox
  10.       oComboBox.InitialControl x
  11.       gcolComboBoxs.Add oComboBox
  12.     End If
  13.   Next
  14. End Sub
½Æ»s¥N½X
ªí¹F¤£²M¡BÃD·N¤£©ú½T¡B¨SªþÀɮ׮榡¡B¨S¦³°Q½×°ÝÃDªººA«×~~~~~~¥H¤W·R²ö¯à§U¡C

TOP

¦^´_ 22# GBKEE

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

TOP

¥»©«³Ì«á¥Ñ GBKEE ©ó 2014-12-6 13:53 ½s¿è

¦^´_ 21# PKKO

¥¼©R¦W.JPG
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
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

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

TOP

¦^´_ 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
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

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

TOP

¦^´_ 17# PKKO
¥Î·Æ¹«¿ï,¤£­n¥ÎÁä½L¿ï
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 16# GBKEE


   

¦Û°Ê±a¥X

sshot-1.png
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
PKKO

TOP

        ÀR«ä¦Û¦b : ­n¤ñ½Ö§ó¨ü½Ö¡D¤£­n¤ñ½Ö§ó©È½Ö¡C
ªð¦^¦Cªí ¤W¤@¥DÃD