ドロップダウンリスト（VBA研究会議事録_01）

みなさんこんにちはHiroCom777です。僕が所属しているノンプロ研では毎週土曜日にVBA研究会と称して、ExcelVBAの課題を出して回答を検討したりしています。

この取り組みなのですが、なかなか得られるものが多いのです。そこで、この連載ではVBA研究会で論じられた内容を議事録という形で残してみようと思います。

今回のお題

それでは今回のお題です

同じフォルダーにあるテキストファイル「Member.ini」を読み込んで
ドロップダウンリストに表示するコードを書いてください。
「Member.ini」にはメンバーの名前が書いてあります。

シート上に入力されるデータがくつかの候補に絞られる場合、候補をドロップダウンリスト形式で入力できるようにしておくと便利です。インターフェイスもスマートになります。

さらに候補が変更になった場合、その内容を簡単に変更できるようにすると運用が楽になります。早速取り組んでみましょう。

ドロップダウンリストの表示

Excelではデータの入力規制を使用することでシート上にドロップダウンリストを表示できます。設定は以下の様にしました。

この設定をマクロの自動記録で記録してソースリストを確認してみましょう。以下の結果が得られました。

    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="山田,鈴木,田中"
          以下省略
            ・
            ・
            ・
    End With

後半は詳細の設定なので省略します。「Formula1:=」に続くカンマ区切りの文字列を書き換えれば設定できそうです。ファイルから内容を読み込んでここを設定なおせば、実現できそうです。

ファイルから読み込む

ファイルを読み込むコードはコチラです。

  'ファイルを開いてを読み込み
  Dim fileNumber As Long
  fileNumber = FreeFile
  Open fileName For Input As fileNumber
  Do Until EOF(fileNumber)
    Dim readData As String
    Input #fileNumber, readData
    Dim dataList As String
    dataList = dataList & "," & readData
  Loop
  Close fileNumber

FreeFileで取得した番号でテキストファイルを開いて内容を読み込み、カンマ区切りの文字列に変換します。

読み込むテキストファイル「Member.ini」の内容は以下の様になっています。

山田
鈴木
田中

この読み込むテキストファイルですが、Windowsの標準アプリケーションであるメモ帳で作成する場合も多いと思います。この時注意しなければならないのは保存時の文字コードです。メモ帳のデフォルトでは「UTF-8」なのですが、Excelでテキストファイルを読み込む場合は「SHIFT-JIS（ANSI）」が基本となります。保存時には「名前を付けて保存」で文字コードを「ANSI」を指定してください。

文字設定のの制限

ドロップダウンリストに設定する文字数の制限は、255文字までとなっています。ところがこれをVBA経由で設定すると設定できてしまいます。そのまま動作するように見えるのですが、このExcelファイルを保存するとファイル破損の原因になるようです。255文字を超えて設定はできないように制限をかけておきます。

実際のコード

それでは実際のコードです。ドロップダウンリストを表示するシートモジュールに記述します。

Public Sub VBA_001()
  Call SetDropDownList(ThisWorkbook.Path & "\Member.ini", Me.Range("C2"))
End Sub

'ドロップダウンリストを設定
Private Sub SetDropDownList(fileName As String, rng As Range)
  'ファイルを開いてを読み込み
  Dim fileNumber As Long
  fileNumber = FreeFile
  Open fileName For Input As fileNumber
  Do Until EOF(fileNumber)
    Dim readData As String
    Input #fileNumber, readData
    Dim dataList As String
    dataList = dataList & "," & readData
  Loop
  Close fileNumber
  
  '255文字を越えたリスト設定は不可
  If 255 < Len(dataList) Then
    MsgBox "255文字を越えてリスト設定はできません", vbOKOnly + vbExclamation
    Exit Sub
  End If

  'ドロップダウンリストを設定
  With rng.Validation
    .Delete
    If dataList = "" Then Exit Sub
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:=dataList
  End With
End Sub

プロシージャ「SetDropDownList」は引数で指定されたファイルから内容を読み込んで、指定したセルにドロップダウンリストとして表示します。プロシージャ「VBA_001」から呼び出しています。セルには以下の様にドロップダウンリストが設定されたと思います。