１．入力規則(Validation) †

入力規則にリストを設定し、Dropdownリストから選択できるようにします。

  1. ) メソッド
    メソッド説明構文
    Add入力規則を設定します。expression.Add(Type, AlertStyle, Operator, Formula1, Formula2)
    Delete入力規則を削除します。expression.Delete
    Modify入力規則を変更します。expression.Modify(Type, AlertStyle, Operator, Formula1, Formula2)
  2. ) パラメータ
    1. Type
      入力規則の種類を指定します。XlDVType クラスの定数を使用します。
      定数値説明
      xlValidateInputOnly0すべての値。引数 AlertStyle、Formula1、またはFormula2 を使用します。
      xlValidateWholeNumber1整数
      xlValidateDecimal2小数点
      xlValidateList3リスト。引数 Formula1 を必ず指定します。
      xlValidateDate4日付
      xlValidateTime5時刻
      xlValidateTextLength6文字列
      xlValidateCustom7ユーザー設定。引数 Formula1 を必ず指定します。
    2. AlertStyle
      省略可能です。XlDVAlertStyle クラスの定数を使用します。入力規則でのエラーのスタイルを指定します。
      定数値内容
      xlValidAlertStop1停止
      xlValidAlertWarning2注意
      xlValidAlertInformation3情報
    3. Operator
      省略可能です。XlFormatConditionOperator クラスの定数を使用します。入力規則での演算子を指定します。
      定数値内容
      xlBetween1Formula1とFormula2の間
      xlNotBetween2Formula1とFormula2の間以外
      xlEqual3Formula1と等しい
      xlNotEqual4Formula1と異なる
      xlGreater5Formula1より大きい
      xlLess6Formula1より小さい
      xlGreaterEqual7Formula1以上
      xlLessEqual8Formula1以下
    4. Formula1
      省略可能です。データの入力規則での条件式の最初の部分を指定します。
    5. Formula2
      省略可能です。データの入力規則での条件式の 2 番目の部分を指定します。引数 Operator が xlBetween または xlNotBetween の場合、この引数は無視されます。
  3. ) プロパティー
    プロパティーデータ型内容
    IgnoreBlankBooleanTrue:特定のセル範囲への空白値の入力を許可します。
    InCellDropdownBooleanTrue:ドロップダウン リストを表示します。値の取得および設定が可能です。
    InputTitleStringダイアログボックスのタイトルを設定します。
    InputMessageString入力時メッセージを設定します。
    ErrorMessageStringエラー メッセージを設定します。
    ErrorTitleStringエラータイトルを設定します。
    ShowInputBooleanTrue:入力メッセージが設定されている場合、メッセージが表示されます。
    ShowErrorBooleanTrue:ユーザーが無効なデータを入力すると、エラー メッセージが表示されます。
    IMEModeIntegerIMEのModeを設定します。設定値については次の表を参照して下さい。
    1. IMEMode プロパティ
      定数値内容
      xlIMEModeNoControl0コントロールなし
      xlIMEModeOn1オン
      xlIMEModeOff2オフ (英語モード)
      xlIMEModeDisable3無効
      xlIMEModeHiragana4ひらがな
      xlIMEModeKatakana5カタカナ
      xlIMEModeKatakanaHalf6カタカナ (半角)
      xlIMEModeAlphaFull7全角英数字
      xlIMEModeAlpha8半角英数字
↑

２．注意事項 (実行時エラー'1004') †

コマンドボタン（チェックボックスとかリストボックス等も同様）より、Addメソッドを実行すると以下のエラーが発生します。
「実行時エラー'1004': アプリケーション定義またはオブジェクト定義のエラーです。」
これはコマンドボタンがアクティブ状態になってしまうのが原因のようです。これはあるコマンドボタンがアクティブの状態で、他の方法でマクロを実行した場合もエラーが発生します。 対処方法としては、

  1. ) Cells(1,1).SelectとかRange("A1").SelectをWith expression.Validation の前に記述し、特定のセルをアクティブにする。
  2. ) コマンドボタンのプロパティのTakeFocusOnClickをFalseにして、クリック時にフォーカスがボタンに移動しないよう設定する。
    「http://okwave.jp/qa/q504347.html」「http://o-mita.cocolog-nifty.com/blog/2008/10/excelvba-bf69.html」より
↑

３．リストの文字列設定 †

次のサンプルは入力規則のリスとにA、B、Cを設定した例です。
Formula1にリストの要素をコンマ(,) で区切って設定します。 ここでは入力規則を設定するセルに事前にMessageという名前を登録しています。 また、Formula1にサンプルのように文字列で設定できるのは最長255文字までです。

Sub Validation_Set()
    Dim MsgRange        As Range
    
    Set MsgRange = Range("Message")

    Range("A1").Select          '←コマンドボタンより実行する時必要
    With MsgRange.Validation
        .Delete　　　　　　　　　'←入力規則を削除
                       '----入力規則を設定
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
            Operator:=xlBetween, Formula1:="A,B,C"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
        
    Set MsgRange = Nothing

End Sub
↑

４．入力規則の削除 †

入力内容を空白にして入力規則を削除しています。

Sub Validation_Delete( )
    Dim Clear_Range As Range

    Set Clear_Range = Range("Message")
    Clear_Range = ""
    Clear_Range.Validation.Delete
    Set Clear_Range = Nothing

End Sub
↑

５．リストの内容の取得 †

入力規則のリストを動的に変更するためにその内容（Formula1）を取得します。

Function Validation_Get() As String
    Dim Clear_Range As Range

    On Error Resume Next
    Set Clear_Range = Range("Message")
    
    Validation_Get = Clear_Range.Validation.Formula1
    If Err.Number = 0 Or Err.Number = 1004 Then
        Validation_Get = ""
    Else
        MsgBox Err.Number
    End If

End Function
↑

６．リストのRange設定 †

入力規則の内容が255文字を超える場合は、その内容をワークシートに書出し、その範囲をRangeで設定します。
リストのあるセルの範囲を　Formula1:="=$A1:$A20"　のように列と行で指定する場合、Excel2003までは同じワークシートでなければなりません。別のワークシート内のリストを参照する場合は、名前またはINDIRECT関数を使用します。(「関数 入力規則」参照。)
次のサンプルは入力規則をメッセージとして使用した例です。
パラメータshtのWorksheetにメッセージを追加し、その内容を入力規則のドロップダウンリストに表示します。
一番初めのメッセージの時は入力規則は設定せずにメッセージが2つ以上ある場合に入力規則を設定します。メッセージを表示するセルには、最新のメッセージを表示します。
ここでは入力規則の範囲設定(Formula1)は名前(Name)による最初の1回だけで、メッセージの追加に対して名前の範囲(Range)の再設定で表示するメッセージを増加させています。

  1. ) メッセージリストを作成する1番目の行と列を宣言部で定義します。 
        Const RowMessage As Integer = 1
    Const ClmMessage As Integer = 1
  2. ) ここではメッセージを表示するセルに事前にMessageという名前を登録しています。shtはメッセージのリストを作成するワークシートです。 
    Sub Message_Set(sht As Worksheet, msg As String)
    Dim MsgRange        As Range
    Dim MsgListRange    As Range
    Dim lastRow         As Integer
    
    Set MsgRange = Range("Message")
    
    If MsgRange = "" Then
        MsgRange = msg
        MsgRange.Interior.Color = vbRed
    Else
        If sht.Cells(RowMessage, ClmMessage) = "" Then
            sht.Cells(RowMessage, ClmMessage) = MsgRange
            Range("A1").Select          '←コマンドボタンより実行する時必要
            With MsgRange.Validation
                .Delete
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
                    Operator:=xlBetween, Formula1:="=MsgList"
                .IgnoreBlank = False
                .InCellDropdown = True
                .InputTitle = "メッセージリスト"
                .ErrorTitle = ""
                .InputMessage = ""
                .ErrorMessage = "入力はできません。"
                .ShowInput = True
                .ShowError = True
            End With
        End If
        
        MsgRange = msg
       
        Set MsgListRange = sht.Cells(lExcelMaxRow, ClmMessage)
        lastRow = MsgListRange.End(xlUp).row
        lastRow = lastRow + 1
        sht.Cells(lastRow, ClmMessage) = msg
        sht.Range(sht.Cells(RowMessage, ClmMessage), _
                     sht.Cells(lastRow, ClmMessage)).name = "MsgList"
        
        Set MsgRange = Nothing
    End If

End Sub
  3. ) 入力規則と追加したメッセージリストを削除します。 
    Sub Message_Reset()
    Dim Clear_Range As Range

    Set Clear_Range = Range("Message")
    
    Clear_Range = ""
    Clear_Range.Interior.ColorIndex = xlNone
    Clear_Range.Validation.Delete
    Set Clear_Range = Nothing
    
    Set Clear_Range = Range("MsgList")
    Clear_Range.ClearContents
    Set Clear_Range = Nothing

End Sub
  4. ) ここではマクロでValidationを使用しないで、入力規則をセルに事前に関数として設定した場合(「関数 入力規則」)のメッセージリストを作成するサンプルです。Message_Resetも異なります。「サンプル１」)中のMessage_setはこちらを使用しています。 
    Sub Message_Set(sht As Worksheet, msg As String)
    Dim MsgRange        As Range
    Dim MsgListRange    As Range
    Dim lastRow         As Integer
    
    Set MsgRange = Range("Message")
    
    If MsgRange = "" Then
        MsgRange = msg
        MsgRange.Interior.Color = vbRed
    Else
        If sht.Cells(RowMessage, ClmMessage) = "" Then
            sht.Cells(RowMessage, ClmMessage) = MsgRange
        End If
        
        MsgRange = msg
       
        Set MsgListRange = sht.Cells(lExcelMaxRow, ClmMessage)
        lastRow = MsgListRange.End(xlUp).Row
        lastRow = lastRow + 1
        sht.Cells(lastRow, ClmMessage) = msg
        
        Set MsgRange = Nothing
        Set MsgListRange = Nothing
    End If
    
End Sub
最終更新のRSS
Last-modified: 2014-03-11 (火) 01:58:42 (3692d)