VBAで入力規則を設定してみたい…
すぐ使える方法を教えてほしい！
入力規則のリスト項目をVBAで自動更新出来たらいいのにな
…そんなことできるの？
VBAで入力規則の設定に挑戦してみたけど
うまくできない…
こんにちは！VBAが好きになりすぎた結果、社内で効率化のためのVBA講座を開いていた8年目文系プログラマの佐藤です。
皆さんは入力規則を普段使っていますか？
入力規則とは、セルに入力できる内容をリストで指定出来たり、数字しかセルに入力できないようにしたり、文字数制限をかけたりできる非常に便利なエクセルの機能です。
入力する内容が人によってバラバラにならないため、いろんな人が編集する資料ではよく使われています。
しかしながら、大量のセルに手作業で設定するとなるとちょっと大変な作業になります。
これをVBAで自動化出来れば、更に効率化を進めることができますよね。ですが、エラーが起こってしまい思ったようにならなかったり、方法の説明が難しかったりして挫折してしまう方も少なくありません。
この記事ではそんなお悩みを解決するべく、VBAでの入力規則の設定方法について分かりやすく解説していきます。すぐ使える方法も紹介していきますので、一緒にVBAで入力規則を設定してみましょう。
最後には、上手くいかないときに見てほしい部分も解説していきます。上手くできないときにはそちらも参考にしてみてくださいね。
それではまず、VBAで入力規則を設定するときによく出てくる言葉から解説していきます。
さっそく一緒に学習を始めていきましょう！
入力規則を設定する時は「Validation」を使う！
VBAで入力規則を設定するときには「Validation」というものを使います。
これだけだと、何のことなのかさっぱりなので順を追って説明していきます。
皆さんはRangeオブジェクトをご存知でしょうか？セルに内容を入れたり、セルの色を変えたりするときに使っているものです。「Range(“A1”).Value = “こんにちは”」という風によく使っているかと思います。
実は、Rangeオブジェクトの中には、Value（値）の情報以外にもいろいろな情報が入っているんです。その中でも入力規則に関する情報が入っている部分の名前が「Validation」といいます。
例えば、A1に入力規則を追加したい時には「Range(“A1”).Validation.Add」と書き、どんな入力規則にするかの設定をそのあとに書いていきます。
ここで、実際の設定を見てみましょう。
1～10の範囲の数値しか入力できないようにする場合だとこのようになります。
Range("A1").Validation.Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="1", Formula2:="10"
Addと初めに書いてありますね。これで入力規則を追加する命令になります。そのあとスペースがあり、Type～と続いていますよね。
Typeにはどのような入力規則の種類にするかを指定します。画面で入力規則を設定する時のこの部分のことです。今回は「整数」という種類を指定するために「xlValidateWholeNumber」と書いています。
AlertStyleは入力規則に合わない内容を入力されたときにどうするか指定する部分です。
今回は停止にしています。画面だとこの部分の設定です。
最後のOperatorとFormula1、Formula2は画像の「データ」や「最大値」、「最小値」の部分のことです。今回は次の値の間、最小値は1で、最大値は10を設定しています。
このように、VBAで入力規則を設定する時も画面で設定する時と変わらない指定をすればいつも通り設定することができるんです。そう考えてみると、VBAで設定する時も、設定項目のイメージが湧きやすいですよね。
次の章で、さっそく具体的な方法を解説していきます。画面で入力規則を設定しているイメージで、コードも見ていきましょう。
VBAで入力規則を設定してみよう
この章では、VBAで入力規則を設定する方法の紹介をしていきます。
まずは、リストの入力規則を一緒に設定してみましょう。
ドロップダウンリストの入力規則を設定してみよう
リストの入力規則は、入力規則規則の中でもよく使われる種類です。
VBAで自動的に設定出来ればとても便利ですよね。
今回は、まず簡単に「はい」「いいえ」「どちらでもない」の3種類しか入力できないようにVBAで設定してみます。
サンプルコードはこちらです。
Sub validation_test()
'今設定されている入力規則を削除しておく
Range("A1").Validation.Delete
'入力規則を設定
Range("A1").Validation.Add Type:=xlValidateList, Formula1:="はい,いいえ,どちらでもない"
End Sub
実行すると、A1に入力規則が設定されました。
コードの中身を詳しくみていきましょう。
初めからとても大切なポイントです。
実は、すでに入力規則が設定されている部分にVBAで入力規則を再設定しようとするとこんなエラーになってしまうんです。
これを回避するためには一度そのセルの入力規則を消しておく必要があります。
何もない状態で削除してもエラーにはなりませんので、設定する前には必ず入力規則を削除するようにしましょう。
削除が済んだら入力規則を設定していきます。
今回はリストの入力規則を設定したいので、Typeには「xlValidateList」を指定しましょう。
リストの中身は「Formula1」に設定していきます。”（ダブルクォーテーション）で囲って、その中をカンマで区切って入力しましょう。
「”はい”,”いいえ”,”どちらでもない”」と入力するとエラーになってしまうので、注意して入力します。
もし、広い範囲に設定したければ「Range(“A1:A10”).Validation.Add」書くだけで広い範囲に設定することが出来ます。
決めた言葉だけ入力できるようリストにする方法はこれで完成です。
ですが、やっぱりシートに入力されている内容を使ってリストを管理したい時もあります。
その時の方法も合わせて解説していきます。
シートの内容でリストの入力規則を作る方法
画面で設定する時は、このように元の値のところに範囲を指定してシートに入力されている内容でリストをつくりますよね。
これをVBAで設定する方法も、先ほど紹介したものとそんなに変わりません。
さっそく見てみましょう。
今回は「担当者」というシートに書かれている名前をリストにして、Sheet1のA1からA10に規則を設定してみます。担当者シートはこんな感じです。
サンプルコードはこちら。
Sub validation_test()
'今設定されている入力規則を削除しておく
Sheets("Sheet1").Range("A1:A10").Validation.Delete
'入力規則を設定
Sheets("Sheet1").Range("A1:A10").Validation.Add Type:=xlValidateList, Formula1:="=担当者!$A$1:$A$5"
End Sub
実行すると、Sheet1のA1からA10まで入力規則が設定されました。
内容はほぼ同じでしたね。違う部分はFormula1の設定部分です。
ここには、画面で設定する時と同じように範囲を書くことが出来ます。今回はシートが別だったため「=担当者!」と書いてからセルを指定しています。もし、同じシートにリストにしたいものがあれば「=$A$1:$A$5」とシートの名前を指定しないで書いても問題ありません。
これでリストの入力規則を設定する基本は完璧です。
一瞬で完了することが出来るのでとても時短になりますよね。内容も画面で設定する時と同じように書くので、イメージしやすいかと思います。この方法を使ってリストの入力規則を設定してみましょう！
さて、次の項目ではリストの内容が自動で更新されるような方法を紹介していきます。
これもとても簡単に出来るので、リストの内容が増えたり減ったりしそうなときは次の項目で紹介する方法を使ってみましょう。
ドロップダウンリストの入力規則に項目を自動追加してみよう
シートの内容を使ったリストで、内容が追加されるたびに範囲を書き直して実行するのは大変ですよね。それを解消する方法について解説していきます。 実は範囲の指定の部分にOFFSETという関数と、COUNTAという関数を組みわせて使うだけなんです。 サンプルコードはこちらになります。
Sub validation_test()
'今設定されている入力規則を削除しておく
Sheets("Sheet1").Range("A1:A10").Validation.Delete
'入力規則を設定
Sheets("Sheet1").Range("A1:A10").Validation.Add Type:=xlValidateList, Formula1:="=OFFSET(担当者!$A$1,0,0,COUNTA(担当者!$A:$A),1)"
End Sub
OFFSETは起点から指定された数字分のセルを指定してくれる関数、COUNTAは空白ではないセルの数を教えてくれる関数です。
この組み合わせはリストの入力規則を作る時によく使われるものですが、もちろんVBAでもそのまま設定することで同じように使えます。
このテクニックについて詳しく学びたい方は、次のサイトがとても参考になりますのでご確認ください。関数の説明等も詳しく載っています。
この入力規則を設定することで、自動でリストの内容が更新されるようになります。
先ほどの担当者シートに1名追加して、リストを見てみると…
リストの範囲を変えていないのに自動で1名追加されました！
これならメンテナンスも楽になりますね。リストの内容が変わりそうな場合はOFFSETとCOUNTAの組み合わせで範囲を指定してみましょう。
このように、OFFSETとCOUNTAの組み合わせは非常に便利ですが、実は空白が途中にあるとリストに空白も含まれてしまう点に注意が必要です。
リストの内容に空白も含まれそう…という場合には、VBAを駆使して次の項目で紹介する方法を使ってみましょう。
リストに空白を設定したくないときは
もしリストの範囲に空白セルが含まれていたらリストにも次の画像のように謎の空白が出てきてしまいます。
なんだか気持ち悪いですよね、出来れば消したいところです。
これは範囲の指定での工夫だけでは解決できないので、VBAを使って対処していきます。
サンプルコードはこちらです。
Sub validation_test()
Dim name As String
Dim i As Long
'今設定されている入力規則を削除しておく
Sheets("Sheet1").Range("A1:A10").Validation.Delete
'セルの中が空白かどうか確かめ、変数に格納する
For i = 1 To 10
If Sheets("担当者").Cells(i, 1).Value <> "" Then
name = name + "," + Sheets("担当者").Cells(i, 1).Value
End If
Next
'入力規則を設定
Sheets("Sheet1").Range("A1:A10").Validation.Add Type:=xlValidateList, Formula1:=Mid(name, 2)
End Sub
まずは、忘れず入力規則を削除するところから始めます。
次に、あらかじめ多めに見積もっておいた範囲をループさせ、セルが空白かどうか確かめていきます。今回は10行目まで見ることにしました。
'セルの中が空白かどうか確かめ、変数に格納する
For i = 1 To 10
If Sheets("担当者").Cells(i, 1).Value <> "" Then
name = name + "," + Sheets("担当者").Cells(i, 1).Value
End If
Next
セルが空白じゃなかった時に、変数nameの中にカンマ区切りでセルの内容を入れていきます。最終的な変数nameの中は「,佐藤,斉藤,山田,田中,渡辺,高橋」になっています。
ここまで来れば、あとは一番初めに紹介した方法「はい」「いいえ」「どちらでもない」を設定した時と同じ仕組みです。
Formula1に変数nameを指定します、この時Midを使っているのは最初にもカンマが含まれてしまっているためです。
これを実行すると、リストはこんな感じに変わります。
田中さんと渡辺さんの間に合った空白が消えていますね、これでリストの空白を削除することができました。
このようにVBAと組み合わせることで、入力規則の設定も幅広く行うことができます。いろいろ組み合わせて、独自のマクロを作ってみてくださいね。
ここまで学習して
VBAでリストの内容を取得して何かにつかってみたい、リストの内容を取得できるのかな？
と思う方もいらっしゃるかもしれません。
もちろんリストの内容も、どんな入力規則のタイプなのかも簡単に取得することができます。次の項目で解説していくので、さっそく見ていきましょう。
入力規則の内容を取得してみる
入力規則の内容をVBAで取得して、どんな入力規則が設定されているのかを見ることが出来ます。方法はとても簡単です。
例えば、入力規則の種類を取得したい時なら
Sub validation_test()
Dim validation_type As String
'今設定されている入力規則を取得
validation_type = Sheets("Sheet1").Range("A1:A10").Validation.Type
MsgBox validation_type
End Sub
と書くことでTypeが取得できます。ただしこの時に返ってくる値は数値の方なので気を付けましょう。
リストの内容を取得したいなら
Sub validation_test()
Dim validation_type As String
'今設定されている入力規則を取得
validation_type = Sheets("Sheet1").Range("A1:A10").Validation.Formula1
MsgBox validation_type
End Sub
これで取得することが出来ます。
このようにValidationの後に、取得したい設定項目の名前を書くだけで取ることが出来るというわけです。設定する時と同じ言葉が出てくるので、なんとなくわかりやすいですよね。
リストの入力規則に関しての解説はこれで終了です。
基本のやり方をマスターすれば、工夫次第でいろいろなリストを設定できます。どんどん実践して慣れていきましょう。
次は数値に関する入力規則について、簡単に紹介していきます。
数値に関する入力規則を設定してみよう
数値に関する入力規則は、例えば10桁以上は入力できないようにするとか、1から10の間の数値しか入力できないようにするとかそういうものになります。
設定の方法はリストの時と全く変わりません。ちょっとだけ設定項目が増えるのでそこだけ確かめてみましょう。
まずは整数の入力規則の場合です。
5以下の数字しか入力できないという規則を設定してみます。
Sub validation_test()
'今設定されている入力規則を削除しておく
Sheets("Sheet1").Range("A1:A10").Validation.Delete
'入力規則を設定
Sheets("Sheet1").Range("A1:A10").Validation.Add Type:=xlValidateWholeNumber, Operator:=xlLessEqual, Formula1:="5"
End Sub
実行して、入力規則が設定されたセルに「6」を入力してみるとしっかり入力規則に合っていないというメッセージが出てきました。
Operatorの部分にどのような基準で数値を比べたいのかを設定します。今回は5「以下」にしたいので、「xlLessEqual」と書きます。
同じように、文字列の長さで規則を作ることが出来ます。
5文字以上入力しなければならない規則を作ってみましょう。
Sub validation_test()
'今設定されている入力規則を削除しておく
Sheets("Sheet1").Range("A1:A10").Validation.Delete
'入力規則を設定
Sheets("Sheet1").Range("A1:A10").Validation.Add Type:=xlValidateTextLength, Operator:=xlGreaterEqual, Formula1:="5"
End Sub
こちらも、入力規則が設定されたセルに4文字入力してみると入力規則規則に合っていないというメッセージが出ました。
このように、設定値を組み合わせることでいろいろな入力規則を設定することが出来ます。その時の状況にあったものを選んで設定していきましょう。
VBAで入力規則を設定する方法は何となく分かったけど、いろいろな設定値の言葉が分からない…
そう思ったあなたも安心してください。次の項目で、設定値の言葉を紹介していきますので、こちらも合わせて確認していきましょう。
Validationで入力規則を設定するときの設定値を紹介
前の項目で、入力規則を設定した時にいろいろな設定値の言葉が出てきました。例えば、整数の入力規則にするために「xlValidateWholeNumber」という言葉を使いましたよね。
自分で入力規則を設定する時、なんて書いたらいいか分からない…そうならないためにも、その設定値を表にまとめて紹介していきます。
これを見て、どの入力規則を設定したいか確認してから実践してみましょう。
数値で指定しても、値で指定してもどちらも問題ありませんのでお好みで設定してみてくださいね。
まずはTypeに設定できる値です。
Typeはどんな入力規則にするか決めるところでした。画面での設定だと次の部分です。
VBAで設定する時は、次の表から選んで入力規則を決定しましょう。
Typeに設定できる値（必ず指定する／数値で指定してもOK）
| 値 | 数値 | 内容 |
|---|---|---|
| xlInputOnly | 0 | すべての値 |
| xlValidateWholeNumber | 1 | 整数 |
| xlValidateDecimal | 2 | 小数点 |
| xlValidateList | 3 | リスト指定 |
| xlValidateDate | 4 | 日付指定 |
| xlValidateTime | 5 | 日時指定 |
| xlValidateTextLength | 6 | 文字列（長さ指定） |
| xlValidateCustom | 7 | ユーザ設定 |
さて次はAlertStyleに設定できる値です。
これは、入力規則に合わない内容が入力された時の動きについて決めるところでした。画面で設定する時の次の部分です。
VBAで設定する時は、下の表から選んで書きましょう。
AlertStyleに設定できる値（省略してもOK／数値で指定してもOK）
| 値 | 数値 | 内容 |
|---|---|---|
| xlValidAlertStop | 1 | 停止 |
| xlValidAlertWarning | 2 | 注意 |
| xlValidAlertInformation | 3 | 情報 |
最後にOperatorに設定できる値です。
こちらは、値をどのように比べるか決めるところでした。画面で設定する時の「データ」の部分です。
VBAの時は、次の表から選んで設定しましょう。
Operatorに設定できる値（省略してもOK／数値で指定してもOK）
| 値 | 数値 | 内容 |
|---|---|---|
| xlBetween | 1 | Formula1とFormula2の間 |
| xlNotBetween | 2 | Formula1とFormula2の間以外 |
| xlEqual | 3 | Formula1と等しい |
| xlNotEqual | 4 | Formula1と等しくない |
| xlGreater | 5 | Formula1より大きい |
| xlLess | 6 | Formula1より小さい |
| xlGreaterEqual | 7 | Formula1以上 |
| xlLessEqual | 8 | Formula1以下 |
以上の表から選んで「1～10の範囲の整数しか入力できない」ように設定を書いてみると次のような感じになります。
Range("A1").Validation.Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="1", Formula2:="10"
このように組み合わせることでいろいろな設定することができます。
設定できる値はたくさんありますが、この表をよく確認して希望に合う設定を書いてみてくださいね。
次に規則に合っていないときに表示されるメッセージの編集方法について説明していきます。
このままだとなんだか不親切でわかりにくいメッセージですよね。
入力規則の設定にちょい足しするだけで出来るので、こちらも設定してみましょう。
規則に合っていない時のメッセージを変えてみよう
規則に合っていないときに出てくるメッセージは、設定しない状態だと何をどう直したら規則に合うのか教えてくれません。
規則を設定した時に、わかりやすいようメッセージも変更しておきましょう。なんとタイトルも変更することが出来ますよ。
手順はとても簡単で、入力規則を設定したあとに、少し設定項目が増えるだけです。
サンプルコードを見てみましょう。
Sub validation_test()
'今設定されている入力規則を削除しておく
Sheets("Sheet1").Range("A1:A10").Validation.Delete
'入力規則を設定
Sheets("Sheet1").Range("A1:A10").Validation.Add Type:=xlValidateTextLength, Operator:=xlGreaterEqual, Formula1:="5"
Sheets("Sheet1").Range("A1:A10").Validation.ErrorTitle = "エラー"
Sheets("Sheet1").Range("A1:A10").Validation.ErrorMessage = "5文字以上入力してください。"
End Sub
これを実行して、1文字だけ入力してみたところタイトルや内容が違うメッセージが表示されました。
このように、エラーメッセージやタイトルを設定して入力者にわかりやすい資料にすることができます。ぜひこちらも入力規則を設定する時には合わせて設定してみてください。
これで入力規則の設定方法についての解説は終了です。
ここまで上手く設定できましたか？もし上手くできないとなったら次の項目を確認してみましょう。
設定したけど上手くいかない…そんな時の確認事項
入力規則をVBAで設定する際に引っ掛かりやすいポイントを2つ紹介していきます。
もし上手くできないときにはこちらも確認してみてください。
すでに設定されている入力規則を削除しているか確認しよう
サンプルコードでは必ず最初に入力規則の削除を入れていました。
すでに入力規則が設定されているところには再設定できません。
実行したらエラーが出てしまう…そんな時には入力規則の削除が最初に行われているか確認してみましょう。
どこに設定されているか分からない時には次の方法を使って一括削除しましょう。
Sub validation_test() '入力規則を全削除 Cells.Validation.Delete End Sub
これでシートに設定されている入力規則をすべて削除できます。
必ず削除してから設定されるよう、順番には気を付けて作成しましょう。
入力規則を設定したいシートやセルを選択してからやってみよう
実は何かの拍子に設定したいシートやセルがアクティブじゃなくなってしまった時、エラーが発生してしまうんです。
それを回避するために、一度設定の前にSelectをつかって対象のセルやシートを選択状態にしておくという回避方法があります。
もし、削除もしているけど上手くいかない…というときにはSelectを使う方法も試してみましょう。
対象のセルやシートを選択状態にする方法を詳しく知りたい！という方は、次の記事をお勧めします。
Selectでセルを指定する方法の項目で詳しく解説しています。
こちらの記事ではシートを選択する方法を詳しく解説しています。
シートが保護されていないか確認しよう
せっかく設定した入力規則が変更されないように…などの理由でシートに保護がかかっていた場合、入力規則を設定することが出来ません。
上手くいかない場合は、一度シートの保護がされていないかも確認してみましょう。
保護は校閲の保護の項目から解除できますよ。
まとめ
これですべての解説が終了しました。
VBAと組み合わせることで、さまざまな入力規則の設定ができそうですよね。
その時の状況にあった入力規則の設定がサクサク出来るよう、この記事を参考に実践して貰えると筆者も嬉しいです。上手くいかないときには、紹介した項目をチェックしてみてくださいね。
それでは、また次の解説で！
