31本目：入力規則

Excel&VBA解説サイト「エクセルの神髄」様出題の問題集、
「VBA100本ノック」に対する私の回答と解説のページです。

100本ノックの出題リストはこちらから
excel-ubara.com

出題：入力規則

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

◇ 出題ページはこちら

ソースコード

定義モジュール

' 100本ノック031：入力規則
Sub シートの選択リストを設置する()
    
    Dim ws対象シート As Worksheet: Set ws対象シート = ActiveSheet
    Dim wb対象ブック As Workbook: Set wb対象ブック = ActiveWorkbook
    
    ' シート名リストをカンマ区切りで取得
    Dim シート名リスト As String
    Dim シート As Worksheet
    For Each シート In wb対象ブック.Worksheets
        シート名リスト = シート名リスト & "," & シート.Name
    Next
    
    ' 入力規則をセット
    ws対象シート.Range("A1").NumberFormatLocal = "@"
    With ws対象シート.Range("A1").Validation
        .Delete
        .Add Type:=xlValidateList, _
             AlertStyle:=xlValidAlertStop, _
             Formula1:=Mid(シート名リスト, 2)
        .ErrorTitle = "入力値エラー"
        .ErrorMessage = "入力したシートは存在しません。"
        .ShowError = True
    End With
    
End Sub

解説

入力規則を設定する問題でした。

入力規則はRangeオブジェクトのValidationプロパティを操作することで設定できます。

特に特殊な仕様はありませんので、
マクロ記録やネット検索、生成AIを使って対象プロパティを調べればOKです。

一応補足になりますが、本マクロはシート名にカンマが入っていると動きません。

しかも、もしそうだったとするとその対応は結構大変で、
シート名をセルに書き出してそのセルを入力規則とする必要があります。

もし本当にその必要があるなら致し方ないのでそう組みますが、
そうでもない場合は「シート名にカンマは禁止」で運用した方がいいと思います。

マクロですべて解決しようとすると突然コード量が爆増することも多いですので、
運用でカバーできるところは運用でカバーしていきましょう。