入力規則を設定する:Excel VBA入門

スポンサードリンク

はじめに　　　　topへ

入力規則のドロップダウンリストを連動させるコードの例です。
一般機能で、入力規則のリストで、セル範囲を名前で定義して参照する方法を利用しています。
具体的にはA2セルには｢リスト｣で「=項目リスト」、B2セルでは「リスト」で「=Indirect(A2)」とする方法です。
- しかし、このままではエラーとなるので数式を「=IF(A2="",A2,INDIRECT(A2))」としてあります。
実行時には以下のような連動が可能となります。
- A2セルで「野菜、肉、果物」が選択できます。
- B2セルではA2セルの値に応じて選択項目が変化します。
  
  A2が「野菜」のとき A2が「肉」のとき A2が「果物」のとき

セル範囲に名前を定義する　　　　topへ

最初にSheet2に以下のように入力しておきます。そして、「項目リスト」および各セル範囲に名前を定義します。

A1:C1に「項目リスト」、A2:A4に「野菜」、B2:B4に「肉」、C2:C3に「果物」と名前を定義します。

コード例

Sub name_1()
　　Dim lCol As Long, lRow As Long
　　Dim i As Long, nName As String
　　　　On Error Resume Next
　　　　With Sheets("Sheet2")
　　　　　　lCol = .Range("A1").End(xlToRight).Column
　　　　　　ActiveWorkbook.Names("項目リスト").Delete
　　　　　　ActiveWorkbook.Names.Add Name:="項目リスト", _
　　　　　　　　RefersTo:=.Range(.Cells(1, 1), .Cells(1, lCol))
　　　　　　'----名前の定義
　　　　　　For i = 1 To lCol
　　　　　　　　lRow = .Cells(1, i).End(xlDown).Row
　　　　　　　　nName = .Cells(1, i).Value
　　　　　　　　ActiveWorkbook.Names(nName).Delete
　　　　　　　　.Range(.Cells(1, i), .Cells(lRow, i)).CreateNames Top:=True
　　　　　　Next i
　　　　End With
End Sub

なお、リストを追加したりした時にも対応できるようになっています。
- D列に新たなリストを入力し、マクロを実行すると選択肢が増えました。
  
  A2セルの状態 B2セルの状態

入力規則を設定する　　　　topへ

マクロの自動記録でコードを生成すると、ほとんどそのままで利用できます。
なお、リストにするセル範囲はあらかじめSheet2!A1:C1を「項目リスト」と名前を定義しておきます。そうでないとエラーになりますので、上のname_1というコードを最初に実行しています。

コード例

Sub Macro1()
　　name_1
　　With Range("A2").Validation
　　　　'--入力規則を削除
　　　　.Delete
　　　　'--入力規則を設定
　　　　.Add Type:=xlValidateList, _
　　　　　　AlertStyle:=xlValidAlertStop, _
　　　　　　Operator:=xlBetween, _
　　　　　　Formula1:="=項目リスト"
　　　　　　.IgnoreBlank = True
　　　　　　.InCellDropdown = True
　　　　　　.InputTitle = ""
　　　　　　.ErrorTitle = ""
　　　　　　.InputMessage = ""
　　　　　　.ErrorMessage = ""
　　　　　　.IMEMode = xlIMEModeNoControl
　　　　　　.ShowInput = True
　　　　　　.ShowError = True
　　End With
　　'--B2セルへ入力規則を設定
　　With Range("B2").Validation
　　　　.Delete
　　　　.Add Type:=xlValidateList, _
　　　　　　AlertStyle:=xlValidAlertStop, _
　　　　　　Operator:=xlBetween, _
　　　　　　Formula1:="=IF(A2="""",A2,INDIRECT(A2))"
　　　　　　.IgnoreBlank = True
　　　　　　.InCellDropdown = True
　　　　　　.InputTitle = ""
　　　　　　.ErrorTitle = ""
　　　　　　.InputMessage = ""
　　　　　　.ErrorMessage = ""
　　　　　　.IMEMode = xlIMEModeNoControl
　　　　　　.ShowInput = True
　　　　　　.ShowError = True
　　End With
End Sub

Type:=xlValidateList　　入力値のタイプ「リスト」に設定しています。
AlertStyle:=xlValidAlertStop　　エラーメッセージのスタイルを「停止」に設定しています。
Operator:=xlBetween　　　入力値の値の範囲「間」になっていますが、リストにしているのでこれは無意味なものです。
Formula1:="=項目リスト"　条件式1は名前「項目リスト」のセル範囲としています。名前を定義していることが前提です。
.IgnoreBlank = True　　　　セル範囲への空白値の入力を許可します。
.InCellDropdown = True　　ドロップダウンリストを表示します
.InputTitle = ""　　　　　　　入力時メッセージ　ダイアログボックスのタイトルは空欄のままです。
.ErrorMessage = ""　　　　エラーメッセージは空欄のままです。　
.IMEMode = xlIMEModeNoControl　IMEコントロールはコントロールなしです
.ShowInput = True　　　　入力時メッセージが設定されていないので、Trueですがメッセージは表示されません。
.ShowError = True　　　　ユーザーが無効なデータを入力すると、エラーメッセージが表示されます

いろんな設定項目がありますので、必要でない部分を削除すると、以下のようにすることもできます。

Sub Macro1()
　　name_1
　　With Range("A2").Validation
　　　　'--入力規則を削除
　　　　.Delete
　　　　'--入力規則を設定
　　　　.Add Type:=xlValidateList, _
　　　　　　Formula1:="=項目リスト"
　　End With
　　'--B2セルへ入力規則を設定
　　With Range("B2").Validation
　　　　.Delete
　　　　.Add Type:=xlValidateList, _
　　　　　　Formula1:="=IF(A2="""",A2,INDIRECT(A2))"
　　End With
End Sub

B2セルに何か入力してあるとき、A2で何か選択します。その時A2と異なるグループであるときにB2を空欄にする例です。
たとえば、B2で「りんご」を選択しています。ついで、A2で「肉」を選択すると・・・これは矛盾するのでB2の「りんご」を消します。

Sheet1にコードを書きます。

Private Sub Worksheet_Change(ByVal Target As Range)
　　Dim c As Range
　　　　If Not (Application.Intersect(Target, Range("A2:B2")) Is Nothing) Then
　　　　name_1
　　　　Application.EnableEvents = False
　　　　If Target.Address() = "$A$2" Then
　　　　　　Set c = Sheets("Sheet2").Range(Target.Value).Find(Range("B2").Value, lookat:=xlWhole)
　　　　　　If c Is Nothing Then
　　　　　　　　Range("B2").Value = ""
　　　　　　End If
　　　　End If
　　　　Application.EnableEvents = True
　　End If
End Sub

A2:B10に同じように入力規則を追加したい・・・・というご質問がありました。（2012/9/29追記)
更に、A列を[Delete]キーで空白にしたときにエラーで止まるといったケースに対処したコードを下記に書いておきます。

Sub Macro2()
　　name_1
　　With Range("A2:A10").Validation
　　　　'--入力規則を削除
　　　　.Delete
　　　　'--入力規則を設定
　　　　.Add Type:=xlValidateList, _
　　　　　　Formula1:="=項目リスト"
　　End With
　　'--B2セルへ入力規則を設定
　　With Range("B2:B10").Validation
　　　　.Delete
　　　　.Add Type:=xlValidateList, _
　　　　　　Formula1:="=IF(A2="""",A2,INDIRECT(A2))"
　　End With
End Sub

Sheet1のコードは下記のようにしてみました。

Private Sub Worksheet_Change(ByVal Target As Range)
　　Dim c As Range
　　　　If Not (Application.Intersect(Target, Range("A2:B10")) Is Nothing) Then
　　　　name_1
　　　　Application.EnableEvents = False
　　　　　　If Target.Column = 1 Then
　　　　　　　　If Target.Value = "" Then
　　　　　　　　　　Target.Offset(0, 1).Value = ""
　　　　　　　　Else
　　　　　　　　　　Set c = Sheets("Sheet2").Range(Target.Value).Find(Target.Offset(0, 1).Value, lookat:=xlWhole)
　　　　　　　　　　If c Is Nothing Then
　　　　　　　　　　　　Target.Offset(0, 1).Value = ""
　　　　　　　　　　End If
　　　　　　　　End If
　　　　　　End If

　　　　　　If Target.Column = 2 Then
　　　　　　　　If Target.Value = "" Then
　　　　　　　　　　Target.Offset(0, -1).Value = ""
　　　　　　　　End If
　　　　　　End If
　　　　Application.EnableEvents = True
　　　　End If
End Sub

Validation オブジェクト　　　　topへ

メソッド
- Add メソッド：入力規則を設定します
- Delete メソッド：入力規則を削除します
- Modify メソッド：入力規則を変更します。
  構文：expression.Modify(Type, AlertStyle, Operator, Formula1, Formula2)

プロパティ

Typeプロパティ：入力規則の種類を指定します。XlDVType クラスの定数を使用します

定数	値	内容
xlValidateInputOnly	0	すべての値引数 AlertStyle、引数 Formula1、または引数 Formula2 を使用します。
xlValidateWholeNumber	1	整数
xlValidateDecimal	2	小数点
xlValidateList	3	リスト引数 Formula1 を必ず指定します。
xlValidateDate	4	日付
xlValidateTime	5	時刻
xlValidateTextLength	6	文字列
xlValidateCustom	7	ユーザー設定引数 Formula1 を必ず指定します。

AlertStyleプロパティ：省略可能です。XlDVAlertStyle クラスの定数を使用します。入力規則でのエラーのスタイルを指定します。

定数	値	内容
xlValidAlertStop	1	停止
xlValidAlertWarning	2	注意
xlValidAlertInformation	3	情報

Operatorプロパティ：省略可能です。XlFormatConditionOperator クラスの定数を使用します。入力規則での演算子を指定します。

定数	値	内容
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以下

Formula1：省略可能です。データの入力規則での条件式の最初の部分を指定します。
Formula2：省略可能です。データの入力規則での条件式の 2 番目の部分を指定します。引数 Operator が xlBetween または xlNotBetween の場合、この引数は無視されます。
IgnoreBlank プロパティ：特定のセル範囲への空白値の入力を許可します(True)。ブール型 (Boolean) の値を使用します。
InCellDropdown プロパティ：ドロップダウンリストを表示します(True)。値の取得および設定が可能です。ブール型 (Boolean) の値を使用します。
InputTitle プロパティ：入力時メッセージ　ダイアログボックスのタイトルを設定します。文字列型 (String) の値を使用します。
ErrorMessage プロパティ：エラーメッセージを設定します。文字列型 (String) の値を使用します。

IMEMode プロパティ

定数	値	内容
xlIMEModeNoControl	0	コントロールなし
xlIMEModeOn	1	オン
xlIMEModeOff	2	オフ (英語モード)
xlIMEModeDisable	3	無効
xlIMEModeHiragana	4	ひらがな
xlIMEModeKatakana	5	カタカナ
xlIMEModeKatakanaHalf	6	カタカナ (半角)
xlIMEModeAlphaFull	7	全角英数字
xlIMEModeAlpha	8	半角英数字

ShowInput プロパティ：入力メッセージが設定されている場合、メッセージが表示されます(True)。ブール型 (Boolean) の値を使用します。
ShowError プロパティ：ユーザーが無効なデータを入力すると、エラーメッセージが表示されます(True)。ブール型 (Boolean) の値を使用します。

スポンサードリンク

よねさんのWordとExcelの小部屋｜Excel(エクセル)講座の総目次｜Excel(エクセル) VBA入門：目次｜入力規則を設定する

PageViewCounter
　 Counter
　Since2006/2/27

A2が「野菜」のとき	A2が「肉」のとき	A2が「果物」のとき


A2セルの状態	B2セルの状態

入力規則を設定する:Excel VBA入門

はじめに topへ

セル範囲に名前を定義する topへ

入力規則を設定する topへ

Validation オブジェクト topへ

はじめに　　　　topへ

セル範囲に名前を定義する　　　　topへ

入力規則を設定する　　　　topへ

Validation オブジェクト　　　　topへ