こんにちは！Lenocoです。本日も見てくださりありがとうございます。
今回は実案件で遭遇した、プルダウンをマクロで作った際の落とし穴についてお話したいと思います！
プルダウンをマクロで作った際の落とし穴
Excelのプルダウン使ったことありますか？
みなさん、Excelのプルダウンを作ったことはありますか？
「データ」タブの「データの入力規則」の「設定」タブで、
「リスト」を選択し、「元の値」にカンマ区切りの文字列やセル範囲を入れることでプルダウンを作成することができます。
数式を入力することもでき、データ量に合わせてセル範囲を可変に選択することもできます。
また、大項目・中項目・小項目とあったときに、選択された大項目に対応した中項目のリスト、その中項目に対応した小項目のリストを
プルダウンに表示するという上級者テクニックもあります！
かなり便利で、マクロで自動的にプルダウンを作成することもあります。
なぜかファイルに問題があり、修復された
先日マクロの作成依頼があり対応していました。
その中で、ある表の横軸の項目をすべてプルダウンに表示するという動作をいれていたのですが、
プルダウンを作成するところまではうまくいくのですが、そのファイルを保存して閉じてまた開くと、
以下のエラーメッセージが表示されてしまいました。
「はい」を押すと以下のダイアログボックスが表示されます。
最初は何が原因かわからなかったのですが、どうやらプルダウンの作成方法に原因があったようです。
コードは以下のようになっていました。
コード
Sub CreateList1()
Dim i As Long
Dim categoryForList As String
'①左から1セルずつ見ていき、カンマ区切りで文字列を作成
For i = 5 To Range("E4").End(xlToRight).Column
If categoryForList = "" Then
categoryForList = Cells(4, i)
Else
categoryForList = categoryForList & "," & Cells(4, i)
End If
Next i
'②リストに設定
Range("B10").Validation.Delete
Range("B10").Validation.Add Type:=xlValidateList, Formula1:=categoryForList
End Sub
ファイル破損の原因
横軸の項目を全てカンマ区切りで文字列を作成し、それをプルダウンに設定していたのですが、
カンマを含め255文字を超える場合このようなことが起こるようです。
実際、255以内に文字数を収めた場合は問題なくファイルを開くことができました。
そしてExcelのバージョンによっては、ファイルが破損したことで図形や罫線がなくなってしまったり、ということも起きてしまいました。
図形をマクロの開始ボタンとしていた場合、マクロが実行できなくなるという致命的な事態になります！
対応策
カンマ区切りでは文字数の上限を超えてしまうので、セル範囲指定をすることにしました。
やり方はとても簡単で、プルダウンの項目にしたいセル範囲のアドレスを、
「元の値」に入力するだけです。
コードは以下のようになります。
コード
Sub CreateList2()
'セル範囲をリストに設定
Range("B10").Validation.Delete
Range("B10").Validation.Add Type:=xlValidateList, Formula1:="=" & Range("E4").Address & ":" & Range("E4").End(xlToRight).Address
End Sub
カンマ区切りのコードよりシンプルになりました！
項目名がそのままリストにするのであればこれで大丈夫ですが、
項目名＋何かを表示したい場合は、リスト用のシートを作ってあげると良いです。
わたしの場合は、列番号を表示したかったので、シートを作成しました。
リスト用のシートに、「●列目：項目名」の形で文字列を作成していき、
セル範囲を設定してあげます。
さいごに
最初は原因が分からなくてもんもんとしていたのですが、
原因が分かったときはとってもすっきりしました！
何も問題がないと思っている部分でも、時には疑ってみることも大事ですね！
Lenoco
