{"id":12174,"date":"2019-09-04T08:00:46","date_gmt":"2019-09-03T23:00:46","guid":{"rendered":"https:\/\/www.ka-net.org\/blog\/?p=12174"},"modified":"2019-09-04T11:23:37","modified_gmt":"2019-09-04T02:23:37","slug":"vba%e3%83%a6%e3%83%bc%e3%82%b6%e3%83%bc%e3%83%95%e3%82%a9%e3%83%bc%e3%83%a0%e4%b8%8a%e3%81%ae%e3%82%b3%e3%83%b3%e3%83%9c%e3%83%9c%e3%83%83%e3%82%af%e3%82%b9%e3%81%a7%e3%82%aa%e3%83%bc%e3%83%88","status":"publish","type":"post","link":"https:\/\/www.ka-net.org\/blog\/?p=12174","title":{"rendered":"[VBA]\u30e6\u30fc\u30b6\u30fc\u30d5\u30a9\u30fc\u30e0\u4e0a\u306e\u30b3\u30f3\u30dc\u30dc\u30c3\u30af\u30b9\u3067\u30aa\u30fc\u30c8\u30b3\u30f3\u30d7\u30ea\u30fc\u30c8\u6a5f\u80fd\u3092\u5b9f\u88c5\u3059\u308b\u65b9\u6cd5"},"content":{"rendered":"

MSDN\u30d5\u30a9\u30fc\u30e9\u30e0\u306b\u300c\u30e6\u30fc\u30b6\u30fc\u30d5\u30a9\u30fc\u30e0\u4e0a\u306e\u30b3\u30f3\u30dc\u30dc\u30c3\u30af\u30b9\u3067\u3001\u4efb\u610f\u306e\u6587\u5b57\u5217\u3067\u30a2\u30a4\u30c6\u30e0\u3092\u30d5\u30a3\u30eb\u30bf\u30ea\u30f3\u30b0\u3057\u305f\u3044\u300d<\/i>\u3068\u3044\u3063\u305f\u8cea\u554f\u304c\u3042\u308a\u307e\u3057\u305f\u3002<\/p>\n

\"\"<\/div>
social.msdn.microsoft.com<\/div><\/div>
\"\"<\/figure>
Autocomplete \/ Filter ComboBox in Userform<\/div>
https:\/\/social.msdn.microsoft.com\/Forums\/en-us\/isvvba\/thread\/52a0fed9-8d34-48f2-b107-80be0e054d51<\/div><\/div>
<\/div><\/div><\/a><\/div><\/div>\n

\u65e9\u3044\u8a71\u304c\u30aa\u30fc\u30c8\u30b3\u30f3\u30d7\u30ea\u30fc\u30c8(\u30b5\u30b8\u30a7\u30b9\u30c8)\u6a5f\u80fd\u3067\u3059\u306d\uff01
\n\u30b3\u30f3\u30dc\u30dc\u30c3\u30af\u30b9\u306b\u306fMatchEntry<\/a>\u30d7\u30ed\u30d1\u30c6\u30a3\u3082\u7528\u610f\u3055\u308c\u3066\u3044\u307e\u3059\u304c\u3001\u4eca\u56de\u306e\u7528\u9014\u3067\u306f\u7269\u8db3\u308a\u307e\u305b\u3093\u3002<\/p>\n

\u305d\u3053\u3067\u3001KeyUp\u30a4\u30d9\u30f3\u30c8\u3092\u5229\u7528\u3057\u305f\u4ee3\u66ff\u624b\u6bb5\u3092\u8003\u3048\u3066\u307f\u308b\u3053\u3068\u306b\u3057\u307e\u3057\u305f\u3002<\/p>\n

KeyUp\u30a4\u30d9\u30f3\u30c8\u306b\u3088\u308b\u30aa\u30fc\u30c8\u30b3\u30f3\u30d7\u30ea\u30fc\u30c8\u6a5f\u80fd\u306e\u5b9f\u88c5<\/h4>\n

\u3055\u3063\u305d\u304f\u30b3\u30fc\u30c9\u3067\u3059\u3002
\n\u3061\u306a\u307f\u306b\u3001\u4e0b\u8a18\u30b3\u30fc\u30c9\u306fExcel\u306b\u9650\u3089\u305aWord\u3084PowerPoint\u3067\u3082\u305d\u306e\u307e\u307e\u4f7f\u7528\u3059\u308b\u3053\u3068\u304c\u3067\u304d\u307e\u3059\u3002<\/p>\n

'\u203bUserForm\u306b\u8a18\u8ff0\r\nOption Explicit\r\n\r\nPrivate WithEvents cboAutoComplete As MSForms.ComboBox\r\nPrivate cboStored As Object\r\n\r\nPrivate Sub UserForm_Initialize()\r\n  Dim v As Variant\r\n  Dim i As Long\r\n  \r\n  '\u30b3\u30f3\u30dc\u30dc\u30c3\u30af\u30b9\u306e\u30ea\u30b9\u30c8\u8a2d\u5b9a\r\n  '\u4e0b\u8a18\u6c0f\u540d\u306f http:\/\/kazina.com\/dummy\/ \u3067\u4f5c\u6210\u3057\u305f\u30c0\u30df\u30fc\u30c7\u30fc\u30bf\r\n  v = Array("\u4e2d\u539f \u8349\u592a", "\u67f3 \u9686", "\u76f8\u7530 \u529f\u88dc", "\u6b66\u7530 \u6df3", "\u95a2\u6839 \u5b88", "\u677e\u4e95 \u7f8e\u4f50", "\u5965 \u535a\u660e", _\r\n            "\u4e95\u4e0a \u4fca\u4e8c", "\u5c71\u4e2d \u306a\u3064\u307f", "\u6751\u702c \u7531\u6a39", "\u3068\u3088\u305f \u4eac\u5b50", "\u5bcc\u5ca1 \u7531\u5b87", "\u6cc9 \u307e\u307f", _\r\n            "\u798f\u5ca1 \u61b2\u4e00", "\u5800 \u30b5\u30c0\u30f2", "\u5c0f\u67f3 \u62d3\u90ce", "\u6885\u6ca2 \u5b63\u8863", "\u89d2 \u9244\u6d0b", "\u53e4\u8c37 \u8a69\u7e54", _\r\n            "\u77f3\u7530 \u96c5\u4e4b", "\u6d77\u8001\u539f \u7f8e\u54b2", "\u9060\u5c71 \u90c1\u6075", "\u897f\u6d66 \u5149", "\u5927\u6cbc \u3057\u307c\u308a", "\u5948\u826f \u4f73\u4e43", _\r\n            "\u6709\u6751 \u7f8e\u5e78", "\u5ca9\u9593 \u5e83\u53f8", "\u897f \u660e\u65e5", "\u53e4\u6ca2 \u65e9\u7e54", "\u67f4\u7530 \u308a\u3048", "\u897f\u6751 \u7f8e\u5e78", _\r\n            "\u4e09\u8239 \u9ebb\u7dd2", "\u5927\u718a \u306a\u3064\u307f", "\u5927\u6cc9 \u82bd\u4ee5", "\u576a\u4e95 \u6709\u6d77", "\u4eca \u73b2\u90a3", "\u6ca2\u4e95 \u5948\u6708", _\r\n            "\u4f0a\u85e4 \u8389\u7dd2", "\u5c71\u4e2d \u6625\u6a39", "\u5bae\u8feb \u88d5\u53f8", "\u6709\u7530 \u30d2\u30ab\u30eb", "\u6709\u7530 \u3042\u3055\u307f", "\u7530\u8fba \u7802\u7fbd", _\r\n            "\u65e5\u4e0b \u7f8e\u4f50\u5b50", "\u5916\u5c71 \u967d\u5b50", "\u6c60\u7530 \u3061\u3048\u307f", "\u677e\u5d0e \u77e5\u53f2", "\u5409\u6ca2 \u6d69\u592a\u90ce", "\u7530\u539f \u60c7", _\r\n            "\u91d1\u5150 \u8def\u5b50")\r\n  With ComboBox1\r\n    .MatchEntry = fmMatchEntryNone\r\n    For i = LBound(v) To UBound(v)\r\n      .AddItem v(i)\r\n    Next\r\n  End With\r\n  \r\n  '\u4fdd\u5b58\u7528\u306eComboBox\u306b\u30ea\u30b9\u30c8\u3092\u30b3\u30d4\u30fc\r\n  Set cboAutoComplete = ComboBox1\r\n  Set cboStored = CreateObject("Forms.ComboBox.1")\r\n  cboStored.List = cboAutoComplete.List\r\nEnd Sub\r\n\r\nPrivate Sub cboAutoComplete_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)\r\n  Dim accCbo As Office.IAccessible\r\n  Dim accLst As Office.IAccessible\r\n  Dim i As Long\r\n  \r\n  Set accCbo = cboAutoComplete\r\n  Select Case KeyCode\r\n    '\u52d5\u4f5c\u3059\u308b\u30ad\u30fc\u6307\u5b9a \u203b\u5fc5\u8981\u306b\u5fdc\u3058\u3066\u5909\u66f4\r\n    '\u5909\u63db(28),\u7121\u5909\u63db(29)\r\n    Case 28, 29, vbKeyBack, vbKeySpace, vbKeyDelete, _\r\n         vbKeyA To vbKeyZ, vbKey0 To vbKey9, vbKeyNumpad0 To vbKeyNumpad9\r\n\r\n      '\u30d5\u30a3\u30eb\u30bf\u30ea\u30f3\u30b0\u3057\u3066\u30a2\u30a4\u30c6\u30e0\u8ffd\u52a0\r\n      cboAutoComplete.Clear\r\n      For i = 0 To cboStored.ListCount - 1\r\n        If cboStored.List(i) Like "*" & cboAutoComplete.Text & "*" Then\r\n          cboAutoComplete.AddItem cboStored.List(i)\r\n        End If\r\n      Next\r\n\r\n      '\u958b\u3044\u3066\u3044\u308b\u30c9\u30ed\u30c3\u30d7\u30c0\u30a6\u30f3\u3092\u9589\u3058\u308b\r\n      If accCbo.accName(&H2&) = "\u9589\u3058\u308b" Then\r\n        Set accLst = accCbo.accChild(&H3&)\r\n        accLst.accDoDefaultAction &H0&\r\n        'DoEvents\r\n      End If\r\n\r\n      cboAutoComplete.DropDown\r\n  End Select\r\nEnd Sub<\/pre>\n
\"\"<\/a><\/p>\n
\u4ed5\u7d44\u307f\u306f\u4e0b\u8a18\u306e\u3088\u3046\u306b\u975e\u5e38\u306b\u5358\u7d14\u3067\u3059\u3002<\/p>\n
    \n
  1. \u30e6\u30fc\u30b6\u30fc\u30d5\u30a9\u30fc\u30e0\u304c\u958b\u3044\u305f\u3068\u304d(UserForm_Initialize)\u306b\u3001\u5bfe\u8c61\u3068\u306a\u308b\u30b3\u30f3\u30dc\u30dc\u30c3\u30af\u30b9\u306e\u30ea\u30b9\u30c8\u3092\u4fdd\u5b58\u7528\u306e\u30b3\u30f3\u30dc\u30dc\u30c3\u30af\u30b9\u306b\u30b3\u30d4\u30fc\u3059\u308b\u3002<\/li>\n
  2. \u30b3\u30f3\u30dc\u30dc\u30c3\u30af\u30b9\u3067\u30ad\u30fc\u304c\u62bc\u3055\u308c\u305f\u3068\u304d(KeyUp\u30a4\u30d9\u30f3\u30c8)\u306b\u3001\u5165\u529b\u3055\u308c\u305f\u6587\u5b57\u5217\u3092\u5143\u306b\u30d5\u30a3\u30eb\u30bf\u30ea\u30f3\u30b0\u3057\u3066\u3001\u4fdd\u5b58\u7528\u306e\u30b3\u30f3\u30dc\u30dc\u30c3\u30af\u30b9\u304b\u3089\u30ea\u30b9\u30c8\u3092\u4f5c\u6210\u3059\u308b\u3002<\/li>\n<\/ol>\n
    \u552f\u4e00\u5de5\u592b\u3057\u305f\u70b9\u306f\u3001DropDown\u30e1\u30bd\u30c3\u30c9\u3067\u958b\u3044\u305f\u30ea\u30b9\u30c8\u3092\u9589\u3058\u308b\u51e6\u7406\u3067\u3057\u3087\u3046\u304b\u3002
    \n\u4e0b\u8a18\u30b3\u30fc\u30c9\u306e\u90e8\u5206\u3067\u5b9f\u88c5\u3057\u3066\u3044\u307e\u3059\u304c\u3001\u3053\u308c\u304c\u306a\u304b\u306a\u304b\u4e0a\u624b\u304f\u3044\u304d\u307e\u305b\u3093\u3067\u3057\u305f\u3002<\/p>\n
    If accCbo.accName(&H2&) = "\u9589\u3058\u308b" Then\r\n  Set accLst = accCbo.accChild(&H3&)\r\n  accLst.accDoDefaultAction &H0&\r\n  'DoEvents\r\nEnd If<\/pre>\n
    \u6700\u521d\u306f\u30e6\u30fc\u30b6\u30fc\u30d5\u30a9\u30fc\u30e0\u306b\u30ad\u30fc\u30b3\u30fc\u30c9\u3092\u9001\u4fe1\u3057\u3066\u7121\u7406\u3084\u308a\u9589\u3058\u305f\u308a\u3057\u3066\u3044\u307e\u3057\u305f\u304c\u3001\u6700\u7d42\u7684\u306b\u306f\u4e0a\u8a18\u306e\u5f62\u306b\u843d\u3061\u7740\u304d\u307e\u3057\u305f\u3002
    \n(\u3082\u3057\u304b\u3057\u305f\u3089\u3001OS\u3084Office\u306e\u30d0\u30fc\u30b8\u30e7\u30f3\u306b\u3088\u3063\u3066\u306f\u4e0a\u624b\u304f\u52d5\u4f5c\u3057\u306a\u3044\u304b\u3082\u3057\u308c\u307e\u305b\u3093\u2026\u3002)<\/p>\n
    \n
    \n