VBA100本ノック 31本目：入力規則

公開日：2020-11-23　最終更新日：2021-01-13

VBA100本ノック 31本目：入力規則

セルに入力規則を設定する問題です。
ブックのシート一覧のリストにします。

ツイッター連動企画です。
ツイートでの見やすさを考慮して、ブック・シート指定等を適宜省略しています。

VBAテスト用のサンプルデータはご自身でご用意ください。

出題

#VBA100本ノック 31本目
アクティブシートのA1セルに、入力規則の「リスト」を設定します。
「リスト」の「元の値」には、ブックの全シートのシート名を設定してください。
A1セルに間違った値を入力した場合は分かり易い警告文がでるようにしてください。
※再実行を考慮
※ブックの指定は任意

VBA作成タイム

この下に頂いた回答へのリンクと解説を掲載しています。
途中まででも良いので、できるだけ自分でVBAを書いてみましょう。

他の人の回答および解説を見て、書いたVBAを見直してみましょう。

頂いた回答

出題ツイートの引用ツイートでの回答

解説

入力規則はRangeのValidationオブジェクトです。
設定する場合はDeleteしてから新しい規則を設定します。
リストはAddメソッドのFormula1にカンマ(,)区切りの文字列で設定します。
数値のシート名012等で頭の0が消えないように表示形式を文字列に変更しています。


  
    
      Sub VBA100_31_01()

　　Dim ws As Worksheet

　　Set ws = ActiveSheet

　　Dim wb As Workbook

　　Set wb = ws.Parent

　　

　　Dim ary() As String

　　ReDim ary(1 To wb.Sheets.Count)

　　

　　Dim i As Long

　　For i = 1 To wb.Sheets.Count

　　　　ary(i) = wb.Sheets(i).Name

　　Next

　　

　　ws.Range("A1").NumberFormat = "@"

　　With ws.Range("A1").Validation

　　　　.Delete

　　　　.Add Type:=xlValidateList, _

　　　　　　 AlertStyle:=xlValidAlertStop, _

　　　　　　 Formula1:=Join(ary, ",")

　　　　.ErrorTitle = "入力値不正"

　　　　.ErrorMessage = "ドロップダウンリストから選択してください。"

　　　　.ShowError = True

　　End With

End Sub

リストはカンマ区切りなので、シート名にカンマが含まれていると分割されてしまいます。
リストを文字列で設定する場合はいくつかの制限があります。
制限を気にせずにリスト設定するには、セル範囲をリストに設定するようにします。
このVBAは記事補足に掲載しました。

補足

リストを直接文字列指定した場合の制限

入力規則のリスト文字列はカンマ(,)区切りなので、シート名にカンマが含まれていると分断してしまいます。
その他、リストに文字列で設定する場合は、いろいろと制限があります。

先頭シートが=で始まる場合
https://twitter.com/audiobookrunner/status/1330825517176524800

リスト文字列が255文字を超える場合
https://twitter.com/ExcelQBag/status/1330850173476454401

このような場合は、上記VBAでは正しく設定できません。
これらに対応するには、セル範囲をリストに設定するようにします。
ただし、さすがに無いとは思いますがリストの要素数も32,767が上限となります。

リストに直接文字列を指定するのは、数も少なく、変更も少ない場合に限定したほうが良いと思います。
実務としては以下のように、シートを利用するようにした方が無難かつ柔軟に対応できると思います。

セル範囲を指定して対応


  
    
      Sub VBA100_31_02()

　　Dim wb As Workbook, ws As Worksheet

　　Set ws = ActiveSheet

　　Set wb = ws.Parent

　　

　　Dim ws設定 As Worksheet

　　Set ws設定 = getSheet(wb, "設定")

　　

　　Dim i As Long, cnt As Long

　　ws設定.Columns(1).Clear

　　ws設定.Columns(1).NumberFormat = "@"

　　cnt = 0

　　For i = 1 To wb.Sheets.Count

　　　　If wb.Worksheets(i).Visible <> xlSheetVeryHidden Then

　　　　　　cnt = cnt + 1

　　　　　　ws設定.Cells(cnt, 1) = wb.Sheets(i).Name

　　　　End If

　　Next

　　

　　ws.Range("A1").NumberFormat = "@"

　　With ws.Range("A1").Validation

　　　　.Delete

　　　　.Add Type:=xlValidateList, _

　　　　　　 AlertStyle:=xlValidAlertStop, _

　　　　　　 Formula1:="=" & ws設定.Range("A1").Resize(cnt).Address(external:=True)

　　　　.ErrorTitle = "入力値不正"

　　　　.ErrorMessage = "ドロップダウンリストから選択してください。"

　　　　.ShowError = True

　　End With

End Sub


Function getSheet(ByVal wb As Workbook, ByVal aName As String) As Worksheet

　　On Error Resume Next

　　Set getSheet = wb.Worksheets(aName)

　　If Err Then

　　　　Set getSheet = wb.Worksheets.Add

　　　　getSheet.Visible = xlSheetVeryHidden

　　　　getSheet.Name = aName

　　End If

　　On Error GoTo 0

End Function

セルにシート一覧を出力して、そのセル範囲を指定すれば上記の問題点は解消されます。

「設定」シートをxlSheetVeryHiddenで作成して、A列にシート一覧を出力しています。
実務的には、「設定」シートはあらかじめ用意しておき、ユーザーに操作させたくない各種設定値を入れておくことに使ったりします。
xlSheetVeryHiddenはユーザーに触れさせない為のものなので、上記VBAでは一覧から除外しています。

リストの区切り文字のカンマ(,)は何処で設定されているのか

この区切り文字が使われているようです。
これを別の文字、例えば/に変更すると入力規則も/区切りになります。
ただし、これは他でも使われているので変更しない方が良いです。

サイト内関連ページ

第126回．入力規則（Validation）

・Validationオブジェクト・入力規則（Validation）の使用例・入力規則を設定しても無効データが入力されてしまう場合への対処

第112回．動的配列（ReDim）

・ReDimステートメント・要素数の変更について・配列について

第113回．配列に関連する関数

・LBound関数とUBound 関数・Array関数・IsArray 関数・Join関数・Filter関数・Eraseステートメント

同じテーマ「VBA100本ノック」の記事

28本目：シートをブックに分割

・出題・頂いた回答・解説・補足・サイト内関連ページ

29本目：画像の挿入

・出題・頂いた回答・解説・補足・サイト内関連ページ

30本目：名札作成（段組み）

・出題・頂いた回答・解説・補足・サイト内関連ページ

31本目：入力規則
32本目：Excel終了とテキストファイル出力

・出題・頂いた回答・解説・補足・サイト内関連ページ

33本目：マクロ記録の改修

・出題・頂いた回答・解説・補足・サイト内関連ページ

34本目：配列の左右回転

・出題・頂いた回答・解説・補足・サイト内関連ページ

35本目：条件付き書式

・出題・頂いた回答・解説・補足・サイト内関連ページ

36本目：列の並べ替え

・出題・頂いた回答・解説・補足・サイト内関連ページ

37本目：グラフの色設定

・出題・頂いた回答・解説・補足・サイト内関連ページ

38本目：1シートを複数シートに振り分け

・出題・頂いた回答・解説・補足・サイト内関連ページ

アクセスランキング・・・ランキング一覧を見る

1.最終行の取得（End,Rows.Count）｜VBA入門
2.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
3.RangeとCellsの使い方｜VBA入門
4.ひらがな⇔カタカナの変換｜エクセル基本操作
5.繰り返し処理（For Next)｜VBA入門
6.変数宣言のDimとデータ型｜VBA入門
7.ブックを閉じる・保存（Close,Save,SaveAs）｜VBA入門
8.並べ替え（Sort）｜VBA入門
9.セルのクリア（Clear,ClearContents）｜VBA入門
10.Findメソッド（Find,FindNext,FindPrevious）｜VBA入門

ホーム
マクロVBA入門編
VBA100本ノック
31本目：入力規則

このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。

記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。

VBA練習問題
VBA100本ノック 31本目：入力規則