【ExcelVBA】小技２　セルを可変するドロップダウンリストにする（おまけ〜ボタンにマクロを登録する方法）

f:id:hamochikuwa440:20210902105629p:plain

さて、Excelのドロップダウンリストといえば、「入力規制リスト」です。リボンから選択し、リストデータ一覧をカンマで区切りで直接入力したり、セルの範囲で設定したりして使うものです。
不特定多数の人に使ってもらう目的でフォーマットファイルを準備する場合、想定外のデータ入力を防ぎたい時などによく使われているのを見かけます。
今回は、VBAでこの機能を操作し、「ユーザーフォーム」や「ActiveX」のコンボボックスのようにセルを使おう！という事です。

「わざわざ入力規制を使う理由がわからない」と思われる方が多いかもしませんが、「ユーザーフォーム」や「ActiveX」って、実はOSによって使えないことがあるんです。

以前、Excel for MacでVBAを使った派遣管理システムを作った時、この壁にぶち当たりました。。。始めは別の手が思いつかず、ワークシートに検索結果を列へ表示させ、小技１のダブルクリックでリストからデータを選択できるようにしましたが、リスト数が可変するものは、検索結果リスト以降の行に、何も表示させられなくなるというブサイクなものになりました。
それではいかんと思い、フォームコントロールのコンボボックスも試みましたが、連続するセルでないとリストデータとして使えないため、表示用にリストを新たに作らねばならないなど想像以上に使い勝手が悪い。
こうなったら、Macでユーザーフォームは作れないけど、Windowsで作ったものを使うことはなぜかできてしまうので試してみました。結果、フォームが表示されているとワークシート操作はできないし、フォームのフォントは崩れるしで別の対応の必要性が出ます。しょうがないから、Macの動きの想定を盛り込み、Windowsでコードを修正、Macで確認と、、、そんなことを数回繰り返すと、ファイルがクラッシュし、結局、どちらのOSからも開くことができず、元に戻せない状態になるなど、、、、運用するには不安定で納得いくものがなかなかできませんでした。
なんとか別の解決策を考えている時、「入力規制のリストでコンボボックスを表現できないか？」と思いつき、試しに使ってみたら、意外にコンボボックスとして使えたんです！しかも入力規制リストを通常に使う時のデータ制限（255文字？でしたっけ？）を考えなくても動いたんです！

そこでMacでコンボボックスが使えずに機能導入を諦めている方にも知ってほしい（笑）ということで紹介です。

まず、入力規制のリストを設定する基本のVBAコードは

With ActiveSheet.Range("B4").Validation
    .Delete 
    .Add Type:=xlValidateList, Formula1:="A,B,C"
End With

となります。翻訳のように解説しますと

表示しているシートのB4セルに入力規制をEnd Withまでで設定します。
設定をクリアします。
リストの値のみ許可とし、値は "A,B,C" とします。
設定はここまでです。

このコードはExcelのリボンの入力規制からリストを選択して、A,B,Cと直接入力するのと一緒です。セルの範囲を指定するときは"A,B,C"のところを"=$A$5:$C$5"のように変更します。イコールを忘れがちなので注意です。

しか〜し！！これではただの入力規制の設定です。今回はこれじゃない（笑）

このリストの内容を可変できるようにしたいわけなので、"A,B,C"のところが可変できるようにすればいいわけです。
どうするのかといいますと、

Formula1:=変数（もしくは配列）

と変数を使うだけでいいんです！
変数１つではデータが１つだけしか入らないのでは？と考えてしまいがちですが、その考え方をかえて、変数の中でカンマ区切りのデータを文字列で作って１つの文字列にしてしまうんです。ループの中で使うカウントアップ（インクリメント）の要領です。変数＝変数 & "," & 新データみたいな感じでループを使って作成するのです。

配列が得意な方は、配列にデータを入れてJoin（配列,",")としてカンマ区切りでデータをつなげれば一発でできますね。

それでは実際に変数を使った方法と配列を使った方法でサンプルを作ってみたいと思います。

今回は説明用なのでこのまま実用で使うと不備だらけになると思います。そこは検索方法や入力データをガチガチにガードするなど工夫してください。そういった工夫については、別の「Excelで書類管理」の記事の中で書こうかなと思います。

それではまず、Excel上の作成イメージは下のようにします。

f:id:hamochikuwa440:20210812081409j:plain

作成するファイルの形は、「personal」シートに検索表示するものがあり、「staff_data」シートにデータベースがあるものにします。データベースの形は上の写真のように、

A列	B列	C列	d列
id	名前	カナ	住所

にします。カナはコードの中で強制的に半角比較にするので、データの状態は全角でも半角でもどちらでも可能とします。

検索表示する流れは、「セルB2」にカナの頭文字（全角、半角どちらでも可) を入力してもらい、コントロールで配置したボタンを押すと検索し、条件のあうものだけ「id＋名前」のリストを作り、「セルB4:C4」に結果が出るようにします。今回は、入力された検索文字が何文字だろうが強制的に1文字にするようにしておきます。（ここの制御を変えると続きの文字列検索なども可能）

それでは変数に入れて表示する方法

Sub name_search()
Dim p_sheet As Worksheet, d_sheet As Worksheet
    
    Set p_sheet = ThisWorkbook.Worksheets("personal")
    Set d_sheet = ThisWorkbook.Worksheets("staff_data")

Dim r As Integer, x As String, result As String

x = StrConv(Left(p_sheet.Range("B2"), 1), vbNarrow)

r = 2
result = ""
Do While d_sheet.Cells(r, 1) <> ""
    If StrConv(d_sheet.Cells(r, 3), vbNarrow) Like x & "*" Then
        If result = "" Then
            result = d_sheet.Cells(r, 1) & " " & d_sheet.Cells(r, 2)
        Else
            result = result & "," & d_sheet.Cells(r, 1) & " " & d_sheet.Cells(r, 2)
        End If
    End If
　　r = r + 1
Loop

If result <> "" Then
     With p_sheet.Range("B4").Validation
          .Delete 
          .Add Type:=xlValidateList, Formula1:=result
     End With
Else 
    MsgBox "該当者ありません", vbOKOnly + vbInformation
End If

End Sub

つぎに配列に入れて表示する方法
データを一時的に格納する方法が違うだけで、前の変数に入れる方法と全く同じ動きをします。

Sub name_search()
Dim p_sheet As Worksheet, d_sheet As Worksheet
    
    Set p_sheet = ThisWorkbook.Worksheets("personal")
    Set d_sheet = ThisWorkbook.Worksheets("staff_data")

Dim r As Integer, x As string, result() As String, n as Long

x = StrConv(Left(p_sheet.Range("B2"), 1), vbNarrow)

r = 2 
n = 0
Do While d_sheet.Cells(r, 1) <> ""
    If StrConv(d_sheet.Cells(r, 3), vbNarrow) Like x & "*" Then
        Redim Preserve result(n)
        result(n) = d_sheet.Cells(r, 1) & " " & d_sheet.Cells(r, 2)
        n = n + 1
    End If
　　r = r + 1
Loop

If n>0 Then
     With p_sheet.Range("B4").Validation
          .Delete 
          .Add Type:=xlValidateList, Formula1:=Join(result, ",")
     End With
Else 
    MsgBox "該当者ありません", vbOKOnly + vbInformation
End If

End Sub

解説です。長くなるので変数に入れて使うものだけまとめます。

f:id:hamochikuwa440:20210812214655p:plain

そして、上のコードをボタンに設定して実行すると

f:id:hamochikuwa440:20210812203354p:plain

こんな感じになります。

カナの文字を入力して、検索ボタンを押すたびにリストが変化するコンボボックスが完成です！！！

・・・と、浮かれすぎに注意です。まだ運用する前に少し問題があります。
実は、この方法、リストのデータ数が既定値を超えている場合、（超えなければ問題ないかと）使っているときは問題ないのですが、入力規制リストの設定を残したまま保存し、再度ファイルを開くと、、、、Windowsはギリギリファイルの修復で復活できますが、Macはかなりの高確率でファイル自体がクラッシュします！！まあ、、、制限超えてますから当たり前ですよね。

なので、コンボボックスとして使ったあと、表示用シートから移動する時やファイルを保存する前に、必ず入力規制の設定をDeleteして回避しましょう。それぞれWorksheetとWorkbookのイベントがありますので、シートがアクティブでなくなった時（Deactivate）かworkbookを保存する前（BeforeSave）のところにVBAで書いておくといいですよ。

「該当のワークシート」.「該当のセル」.Validation.Delete

と記入しておけば大丈夫です。これは最初の説明にもある、設定のクリアのところですね。

例えば今回のコードで、保存される前に消すならこんな感じ

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

    ThisWorkbook.Worksheets("personal").Range("B4").Validation.Delete

End Sub

これでプルダウンリストは完成ですね！
このあと、リストから選択したあとにデータを抽出して反映させれば使えます。そちらは書類整理の記事の方で詳しく書こうと思いますが、今回の例のようにid+データの場合、私がよく使うのは、 split(分割したい文字列,区切りになるキーの文字列) を使って文字列を分割し、idを抽出して検索に使う方法ですね。
初めてシステム作ったとき、idを設定せずに通常Excel関数（matchとか）のように行番号を使っていたこともありますが、これですとデータを関連づけることができず、使うデータごとに検索しないといけなくなり、コードも無駄に増えていきます。なので、データベースの運用と同じようにid管理にしたほうが、データ項目やテーブルを増やして関連付けたいときなど対応しやすいです。従業員管理ならすでに使っている社員番号とか使うと便利じゃないですかね。

また、ボタンを押してリストを変更するではなく、文字を入力したら変更したいという人も多いでしょう。（むしろそちらのほうが多いか^^;) それは別の記事で書きます。今回は入力規制が利用できるという話でした。

おまけ

フォームコントロールのボタンにマクロを設定する方法

f:id:hamochikuwa440:20210812205454p:plain