入力規則もマクロから設定・解除など操作できます。
場合によってセルをロックしたり、ロック解除したりするような場合で、ロック解除した時には入力規則を使ってリスト選択させるような仕組みが必要な時、入力規則のリストを固定で設定してしまうと、ロックしている時にキー入力はできないのにリスト選択ができてしまう不具合に遭遇します。運用上「不具合」ですがExcel上では仕様であって、そのままでは直りません。
このような時は、セルをロック／ロック解除するタイミングで入力規則のリストの設定も追加／削除してやるしかないようです。
(画像をクリックすると、このサンプルがダウンロードできます)
このサンプルでは、B1セル(行1列2)で「保護」「非保護」を変更すると、連動してB2セル(行2列2)、B4セル(行4列2)のセルをロック、ロック解除します。ちなみに「保護」「非保護」を変更して、Tabキーを押していってもらえば、どのセルがロック解除になっているか判ります。
このマクロは、ワークシート上でセル内容が変わったことのイベントで動作させます。このため、「Worksheet_Change」のイベント処理を使います。
'***************************************************************************************************
' 入力規則を操作するサンプル Sheet1(Class)
'
' 作成者:井上治 URL:https://www.ne.jp/asahi/excel/inoue/ [Excelでお仕事!]
'***************************************************************************************************
'変更日付 Rev 変更履歴内容------------------------------------------------------------------------>
'03/07/15(1.00)新規作成
'12/01/21(1.01)修正
'20/02/22(1.10)*.xlsm化、他
'***************************************************************************************************
Option Explicit
'***************************************************************************************************
' ■■■ ワークシートイベント ■■■
'***************************************************************************************************
'* 処理名 ：Worksheet_Change
'* 機能 ：セル値変更イベント
'---------------------------------------------------------------------------------------------------
'* 返り値 ：(なし)
'* 引数 ：Arg1 = Target(Range)
'---------------------------------------------------------------------------------------------------
'* 作成日 ：2003年07月15日
'* 作成者 ：井上 治
'* 更新日 ：2020年02月22日
'* 更新者 ：井上 治
'* 機能説明：
'* 注意事項：
'***************************************************************************************************
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------------------------------------
Const cnsRANGE_B1 = "$B$1" ' イベント判定セル
Const cnsRANGE_B2 = "$B$2" ' 入力規則を制御するセル
Const cnsRANGE_B4 = "$B$4" ' 入力規則を制御しないセル
Const cnsFORMULA = "=INDIRECT(R2C3,FALSE)" ' 入力規則にセットする式
' B1セル以外は処理なし(脱出)
If Target.Address <> cnsRANGE_B1 Then Exit Sub
' シート保護を一旦解除
ActiveSheet.Unprotect
If Range(cnsRANGE_B1).Value = "保護" Then
' B2セルの入力規則を一旦削除
Range(cnsRANGE_B2).Validation.Delete
' B1セルが「保護」ならセルをロック
Range(cnsRANGE_B2).Locked = True
Range(cnsRANGE_B4).Locked = True
Else
' B1セルが「非保護」ならセルをロック解除して入力規則を設定
Range(cnsRANGE_B2).Locked = False
Range(cnsRANGE_B4).Locked = False
' この記述でB2セルに入力規則を設定
With Range(cnsRANGE_B2).Validation
.Delete
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, _
Formula1:="=INDIRECT($C$2,FALSE)"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.IMEMode = xlIMEModeNoControl
.ShowInput = True
.ShowError = True
End With
End If
' シート保護を設定
ActiveSheet.Protect
End Sub
'----------------------------------------<< End of Source >>----------------------------------------
さらにB1セルの内容が「保護」かそうでないかを判断して、セル(B2,B4とも)のロック状態を換え、「保護」でない場合は入力規則を再登録します。
最後にシート保護を再設定して終わります。
これでB2セルについては、「非保護」の時だけリスト選択ができるようになっているのが判ります。ですが、入力規則を設定したままになっているB4セルはロックされているのにもかかわらず、リスト選択でセル内容が変更できてしまいます。ですが、ロックされているため、Deleteキーで消そうとするとエラーになってしまいます。
お判りのように、場合によってセルのロック状態をコントロールするようなケースでは、リスト選択についてもVBAでコントロールしなければならないということになります。