Excel VBAを利用してプルダウンリストの選択肢からの入力制限を実装する方法の手順を先に要約すると「候補を別sheetのrangeまたはtableで管理し、対象Range.ValidationへxlValidateList、xlValidAlertStop、ShowError=Trueを設定します。既存validationを削除する前にworkbook backupと現在のFormula1を記録します。」となります。対象環境はmacro-enabled workbookのnamed rangeをlist sourceにし、直接貼り付け等の限界も説明する構成です。 確認ポイント：Validationは将来の入力を制限しますが、設定前から存在する候補外値を自動修正しません。
入力規則を変更する範囲と候補表を固定する
入力規則のリストをVBAで検査する前に、対象Range、Validation.Type、Formula1、エラーメッセージ設定を記録します。Formula1がセル範囲、名前定義、直接入力のどれかを判定し、許可値を重複なしで展開します。既存セルの不正値は別一覧へ出し、検査だけの実行でセル内容や規則を書き換えません。
- workbookを別名copyしmacro署名と保護状態を確認する
- 対象sheet/rangeと既存Validation.Type/Formula1を記録する
- 候補に空白、重複、comma、255文字制約がないか確認する
- pasteやmacro書込がvalidationを迂回する条件をtestする
既存Validationを退避してから適用する
Validation.Deleteは既存ruleを失う変更です。workbookを閉じた状態のbackupまたはversion historyを取り、対象rangeだけへ適用します。event macroを併用する場合はEnableEventsをfinally相当で必ず元へ戻し、error時にExcel全体のeventが無効のままにならないようにします。不具合時はbackup workbookまたは保存したruleから復元します。
入力制限を設定・検証・復元する順序
Range.Validationの現行設定を読む
Sub InspectValidation()
With ThisWorkbook.Worksheets("Input").Range("A2:A100").Validation
Debug.Print .Type, .Formula1, .AlertStyle, .ShowError
End With
End Sub削除前の読み取りです。validationなしではerrorになるためhandlerで区別します。
AllowedValuesの参照範囲を確認する
Sub ApplyListValidation()
Dim target As Range
Set target = ThisWorkbook.Worksheets("Input").Range("A2:A100")
With target.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=AllowedValues"
.IgnoreBlank = True
.InCellDropdown = True
.ShowError = True
.ErrorTitle = "入力値を確認してください"
.ErrorMessage = "一覧から選択してください。"
End With
End SubAllowedValuesはworkbook内の確認済みnamed rangeです。
xlValidateListを対象範囲へ適用する
Sub VerifyValidation()
With ThisWorkbook.Worksheets("Input").Range("A2").Validation
Debug.Assert .Type = xlValidateList
Debug.Assert .AlertStyle = xlValidAlertStop
Debug.Print .Formula1, .ShowError
End With
End Sub設定できたことをpropertyで確認します。
既存値のうち候補外を列挙する
Sub FindInvalidCells()
Dim allowed As Range, c As Range
Set allowed = ThisWorkbook.Names("AllowedValues").RefersToRange
For Each c In ThisWorkbook.Worksheets("Input").Range("A2:A100")
If Len(c.Value2) > 0 And WorksheetFunction.CountIf(allowed, c.Value2) = 0 Then Debug.Print c.Address(False, False), c.Value2
Next c
End Sub値を削除せずcandidate一覧だけを出します。
保存したFormula1から元設定を再作成する
' 作業前に保存したFormula1、AlertStyle、IgnoreBlank、ShowErrorを使いValidationを再作成する
' workbook backupから対象sheetを戻す方法も事前にtestするValidation.Deleteはundo stackに頼らずbackupとsnapshotから復元します。
空候補・重複候補・既存違反を別扱いにする
Data Validationはuser input補助で、copy/paste、external update、VBA代入などで期待外値が入る場合があります。comma区切りliteralはlocaleや長さ制限に弱いためrange referenceを使います。xlValidAlertStopでもShowError=Falseならblockingされません。blank許可、case、前後空白をbusiness ruleとして決めます。
入力規則と条件付き書式を混同しない
- 候補をcomma文字列へ直書きする
- 既存validationを記録せずDeleteする
- ShowErrorを設定しない
- pasteでの迂回を見ない
- sheet名やrangeをActiveSheet依存にする
workbook copyで復元可能性を確認する
候補内、候補外、blank、前後空白、paste、複数cell、protected sheetをtest copyで確認します。Validation property、既存dataの無効件数、保存・再open後のruleを確認し、元workbook backupから復元できることもtestします。
プルダウン入力制限の合格基準
workbook-level named range AllowedValuesをThisWorkbook.Names(…).RefersToRangeで解決し、Input sheetのValidationへ設定します。無効値探索も同じRange objectを使い、ActiveSheetのRange解決へ依存しません。
候補名を検証するVBAコード
本文中の完成codeが主手順です。ここでは同じcodeを重複掲載せず、次の三状態と境界値を使って結果を判定します。期待する通常状態は「AllowedValuesが一意に解決され、Validation Type/Formula1/ShowErrorが期待値になる」です。
設定済み・候補なし・実行errorを分類する
- 期待どおり：AllowedValuesが一意に解決され、Validation Type/Formula1/ShowErrorが期待値になる
- 0件・非適用：blank許可時の空cellはinvalid件数へ含めず、候補0件は設定errorとして分ける
- 実行error：name不存在、別workbook参照、protected sheet、255文字等の制約はVBA errorとして停止する
ValidationはpasteやVBA代入を完全には防がないため、既存dataのCountIf検査を別に行います。.Delete前にFormula1等を保存し、workbook backupを用意します。sheet-level nameを使う場合もscopeを明示します。
空白・100%件外・名前切れを試験する
Sub VerifyAllowedName()
Dim allowed As Range
Set allowed = ThisWorkbook.Names("AllowedValues").RefersToRange
Debug.Print allowed.Parent.Name, allowed.Address, WorksheetFunction.CountA(allowed)
Debug.Assert WorksheetFunction.CountA(allowed) > 0
End Subvalid/invalid、blank、case差、候補0件、name不存在、paste、protected sheetをcopyでtestします。name scope/address、Validation property、invalid cell件数、復元後propertyを記録します。
Excel VBAを利用してプルダウンリストの選択肢からの入力制限を実装する方法の証跡には、実行対象と取得時刻に加え、通常・0件・errorのどれへ分類したかを残します。通常系は「AllowedValuesが一意に解決され、Validation Type/Formula1/ShowErrorが期待値になる」、停止系は「name不存在、別workbook参照、protected sheet、255文字等の制約はVBA errorとして停止する」を判断文としてそのまま作業票へ写し、担当者ごとの言い換えで意味が変わらないようにします。
入力規則の点検を自動化する場合は、対象workbookを閉じた時点のcopyから開き、各runでworkbook scopeとworksheet scopeのnameを解決し直します。保護解除の承認がないsheetは変更せず、規則を読めなかったcellと既存invalid cellを別のreportへ出します。修復用macroへ直結させず、候補rangeのaddressが前回から変わったときはreview待ちで停止します。
コメント