ExcelWork.info

Excel(エクセル)のマクロ・VBA・関数・操作などのメモ

入力規則(エラーメッセージの設定)Validation オブジェクト

   

無効値を入力時に、エラーメッセージの表示

Validation.ShowError プロパティTrue に設定すると、ユーザーが無効なデータを入力した時、入力規則でのエラーメッセージが表示されます。


【書式】

object.ShowError = boolean


引数・戻り値

  • object  ・・・  対象となる Validation オブジェクトを指定します。
  • boolean  ・・・  ブール型(Boolean)の値(True または、False)を指定します。



入力規則でのエラーメッセージの種類を取得

Validation.AlertStyle プロパティ は、入力規則でのエラーメッセージの種類を長整数型(Long)の値(XlDVAlertStyle 列挙型 を使用)を返します。取得のみ可能です。設定は、Validation.Add メソッド で行います。


【書式】

object.AlertStyle


引数・戻り値

  • object  ・・・  対象となる Validation オブジェクトを指定します。



入力規則のエラーメッセージのタイトルを設定

Validation.ErrorTitle プロパティ で、エラー発生時に表示されるダイアログボックスのタイトルを設定することができます。


【書式】

object.ErrorTitle = string


引数・戻り値

  • object  ・・・  対象となる Validation オブジェクトを指定します。
  • string  ・・・  エラーダイアログボックスに表示されるタイトルを設定します。文字列型(String)の値を使用します。(※ 値の取得および設定が可能です。)



入力規則のエラーメッセージを設定

Validation.ErrorMessage プロパティ で、エラーメッセージの設定を行います。


【書式】

object.ErrorMessage = string


引数・戻り値

  • object  ・・・  対象となる Validation オブジェクトを指定します。
  • string  ・・・  エラーメッセージの設定を行います。文字列型(String)の値を使用します。(※ 値の取得および設定が可能です。)



入力規則(エラーメッセージ)使用例

サンプルVBAソース


実行結果

範囲外の値を入力

Validation プロパティ 使用例4


 - セルの操作 , , , ,

Comment

  1. Table より:

    何回も質問ばかりで申し訳ないのですが、
    宜しくお願い致します。

    Sheet2のA2~A32まで値が入っていて名前の定義で大項目としています。
    Sheet2のB2~B32まで値が入っていて名前の定義で中項目としています。
    Sheet2のC2~C32まで値が入っていて名前の定義で小項目としています。
    Sheet2のD2~D32まで値が入っていて名前の定義で規格としています。

    Sheet1のA11~A41とA111~A141まで、大項目のデータの入力規則のリストになっています。
    Sheet1のB11~B41とB111~B141まで、中項目のデータの入力規則のリストになっています。
    Sheet1のC11~C41とC111~C141まで、小項目のデータの入力規則のリストになっています。
    Sheet1のD11~D41とD111~D141まで、規格のデータの入力規則のリストになっています。
    Sheet1のE11~E41とE111~E141までは、数量を入力するセルです。
    Sheet1のF11~F41とF111~F141までは、金額を入力するセルです。

    Sheet1のH11~H41とH111~H141まで、大項目のデータの入力規則のリストになっています。
    Sheet1のI11~I41とI111~I141まで、中項目のデータの入力規則のリストになっています。
    Sheet1のJ11~J41とJ111~J141まで、小項目のデータの入力規則のリストになっています。
    Sheet1のK11~K41とK111~K141まで、規格のデータの入力規則のリストになっています。
    Sheet1のL11~L41とL111~L141までは、数量を入力するセルです。
    Sheet1のM11~M41とM111~M141までは、金額を入力するセルです。

    以降、Sheet1は省略させて頂きます。
    このときに、例としてA11~A41で説明させて頂きます。

    A11に値が入力されていないとB11,C11,D11,E11,F11は、入力できずに
    「大項目を入力して下さい」とエラーメッセージがでるようにしたいです。
    また、リスト以外の値を入力すると「リストから選択して下さい」と
    エラーメッセージがでるようにしたいです。

    B11に値が入力されていないと、C11,D11,E11,F11は、入力できずに
    「中項目を入力して下さい」とエラーメッセージがでるようにしたいです。
    また、リスト以外の値を入力すると「リストから選択して下さい」と
    エラーメッセージがでるようにしたいです。

    C11に値が入力されていないと、D11,E11,F11は、入力できずに
    「小項目を入力して下さい」とエラーメッセージがでるようにしたいです。
    また、リスト以外の値を入力すると「リストから選択して下さい」と
    エラーメッセージがでるようにしたいです。

    D11に値が入力されていないと、E11,F11は、入力できずに
    「規格を入力して下さい」とエラーメッセージがでるようにしたいです。
    また、0以上で小数点第2位の数値でなければ「小数点第2位までの数値を入力して下さい」と
    エラーメッセージがでるようにしたいです。
    セルの書式設定で数値でも構いません。

    E11に値が入力されていないと、F11は、入力できずに
    「数量を入力して下さい」とエラーメッセージがでるようにしたいです。
    また、0以上の正の整数でなければ「整数を入力して下さい」とエラーメッセージがでるようにしたいです。

    すべてのセルですが、エラーメッセージが出た値は、再試行又はキャンセルを押すと消えるようにしたいです。

    A12~F12以降の行もも同様にしたいのですが、可能でしょうか?
    また、列と行の挿入及び削除を行っても、参照するところを可変にすることも
    可能でしょうか?

    データの入力規則のリストで、=IF(A11=””,””,B列)と式を入れてみたのですが、

    1.A11に値があると、B11はリストから選べ、リスト外の値はエラーメッセージが出る。
    2.A11に値がないと、B11はリストは、何も表示されずリスト外の値が入力できてしまいます。

    下手糞な説明で申し訳ございませんが、宜しくお願い致します。

    • サイト管理人 より:

      コメントありがとうございます。
      他にいい方法があるかもしれませんが、とりあえず、思いついた方法を書きます。

      「数値の入力」の制限(0以上の小数、正の整数など)には入力規則を使用します。
      「セルの入力の有無を確認し、入力されていない場合は、エラーメッセージを表示」を実行するには、Worksheet のイベントプロシージャを使います。
      「小数点」に関しては、書式設定を使用します。

      1.書式設定
      「数量」を入力するセルには、数値・小数点2桁の書式設定を行います。

      2.入力規則を設定
      すでに設定されている入力規則に加えて、「数量」には、小数点数で 0以上を「金額」には、整数で 0以上を設定します。(設定済みであれば省略してください。)
      また、入力規則の参照リストを可変にするには、「大項目」の場合、「名前の管理」の参照範囲に以下の数式を入力します。

      =OFFSET(Sheet2!$A$2,0,0,COUNTA(Sheet2!$A:$A),1)

      【OFFSET関数】基準で設定したセルから、指定した行数・列数をシフトした位置にあるセルの参照値を返します。
      OFFSET(基準,行数,列数,[高さ],[幅])
      【COUNTA関数】範囲に含まれる空白でないセルの数を返します。
      COUNTA(値1,[値2,…])

      ※マクロで入力規則を設定する場合は「Validation.InCellDropdown プロパティ」等を使用します。
      このサイトにも説明があります(あまり詳しく説明していませんが)。

      ※手動で入力規則を設定する場合の詳細は以下のページを参照してください。
      入力規則(その1) 使い方・概要
      入力規則(その2) データ入力をドロップダウンリストの値に制限する
      入力規則(その3) 数値のみ入力(整数・小数点・日付・時刻など)
      入力規則(その4) 入力する文字列の長さ(文字数)を制限
      入力規則(その5) 他のセルや数式を利用して許容値を設定する

      3.WorkSheet のイベントプロシージャを設定

      ※以下のマクロは、シートモジュールに書きます。

      ※以下のマクロは、シートモジュール、標準モジュールどちらに書いてもOKです。

      ※メッセージボックスの設定(マクロ)に関しては以下のページで紹介しています。
      メッセージボックスを表示(MsgBox関数)

      以上です。

      ※コメントの返事を書き込んだらページのレイアウトが崩れてしまったので修正しました。

  2. Table より:

    管理人様

    ありがとうございます。

    「再試行」を押した時の処理ですが、入力規則のエラーの再試行ボタンのように
    値を反転するには、どう記述すれば良いのでしょうか?
    教えて下さいませ。
    宜しくお願い致します。

  3. Table より:

    管理人様

    何度もすいません。
    ——————————————————————————–

    Select Case Target.Column
    Case 2, 9 ‘B列・I列
    Call ShowErrMsg(Target, “大項目”)
    Case 3, 10 ‘C列・J列
    Call ShowErrMsg(Target, “中項目”)
    Case 4, 11 ‘D列・K列
    Call ShowErrMsg(Target, “小項目”)
    Case 5, 12 ‘E列・L列
    Call ShowErrMsg(Target, “規格”)
    Case 6, 13 ‘F列・M列
    Call ShowErrMsg(Target, “規格”, 0, -2)
    Call ShowErrMsg(Target, “数量”)

    —————————————-

    上記コードの部分ですが

    Case 3, 10 ‘C列・J列
    大項目が入力されていないと、
    「大項目を入力して下さい」とエラーメッセージ。
    大項目が入力されていて、中項目が入力されていないと
    「中項目を入力して下さい」とエラーメッセージ。

    Case 4, 11 ‘D列・K列
    大項目が入力されていないと、
    「大項目を入力して下さい」とエラーメッセージ。
    大項目が入力されていて、中項目が入力されていないと
    「中項目を入力して下さい」とエラーメッセージ。
    大項目・中項目が入力されていて、小項目が入力されていないと
    「小項目を入力して下さい」とエラーメッセージ。

    Case 5, 12 ‘E列・L列
    大項目が入力されていないと、
    「大項目を入力して下さい」とエラーメッセージ。
    大項目が入力されていて、中項目が入力されていないと
    「中項目を入力して下さい」とエラーメッセージ。
    大項目・中項目が入力されていて、小項目が入力されていないと
    「小項目を入力して下さい」とエラーメッセージ。
    大項目・中項目・小項目が入力されていて、規格が入力されていないと
    「規格を入力して下さい」とエラーメッセージ。

    Case 6, 13 ‘F列・M列
    大項目が入力されていないと、
    「大項目を入力して下さい」とエラーメッセージ。
    大項目が入力されていて、中項目が入力されていないと
    「中項目を入力して下さい」とエラーメッセージ。
    大項目・中項目が入力されていて、小項目が入力されていないと
    「小項目を入力して下さい」とエラーメッセージ。
    大項目・中項目・小項目が入力されていて、規格が入力されていないと
    「規格を入力して下さい」とエラーメッセージ。
    大項目・中項目・小項目・規格が入力されていて、数量が入力されていないと
    「数量を入力して下さい」とエラーメッセージ。

    以上のように処理するには、ElseIf又はSelect Caseだと思うのですが
    記述方法がわかりません。
    宜しければ教えて下さいませ。

    • サイト管理人 より:

      「再試行」を押したときの処理も含めて修正しました。
      SendKeys メソッドを使用すると手動でキーボード入力を行うのと同じ結果を得ることができます。
      ここでは、[F2]キーを入力後、[←] で文字の先頭に移動し、[Shift]キーを押しながら[→]キーで
      文字の最後まで移動させています。(※ただ結果としてエラーになるので入力できませんが・・・)

      あとソースの動作確認はしていますが、再帰プロシージャ(自分自身を呼び出すプロシージャ)を使用しているので、マクロ実行前にExcel等保存したうえで実行してください。
      (※ソースにミスがあると無限ループに入ってしまうことがあるので注意してください。)

      ・以下のマクロはシートモジュールに記述。

      以下のマクロは、シートモジュールまたは標準モジュールに記述。

      • Table より:

        管理人様

        ありがとうございました。
        希望通りの処理ができました。
        素晴らしいです。

  4. Table より:

    管理人様

    またまた質問です。
    すいません。

    別のシートの別のセルなのですが
    C16,C20,C24セルにエラーメッセージを出したいのですが、

    全角5文字以内でなければ、”全角5文字以内で入力して下さい!”と
    エラーメッセージが出るようにしたいです。
    できればRange(“A1,A10)形式でお願いしたいです。

    エラーメッセージの時の処理は、以前と同じがいいです。

    Select Case result
    ‘「再試行」ボタンをクリックした時
    Case vbRetry
    target.Cells.Activate
    Application.SendKeys _
    “{F2}” & _
    “{Left ” & Len(ActiveCell.Value) & “}” & _
    “+{Right ” & Len(ActiveCell.Value) & “}”

    ‘「キャンセル」ボタンをクリックした時
    Case vbCancel
    target.Cells = “”
    target.Cells.Activate

    宜しくお願い致します。

    • サイト管理人 より:

      コメントありがとうございます。

      C16,C20,C24セルに入力した時、
      全角文字以外の場合、5文字以下でない場合
      にエラーメッセージ「全角5文字以内で入力して下さい!」を表示
      ということでいいでしょうか?

      全角文字かどうかのチェックには、正規表現(RegExp オブジェクト)を使っています。
      正規表現に関しては、まだ作成途中ですが、
      VBA で正規表現を使う(RegExp オブジェクトのプロパティ)
      VBA で正規表現を使う(RegExp オブジェクト)
      で紹介しています。

      ※以下のマクロは、シートモジュールに書きます。

      ※以下のマクロは、シートモジュール、標準モジュールどちらに書いてもOK。

      • サイト管理人 より:

        上記ソースの追加(修正)です。
        「半角カタカナ」を入力した場合もエラーを表示する場合は、上記11行目を以下のように修正して下さい。

        ‘(11行目)
        re.Pattern = “[\x01-\x7E]” ‘全角文字以外

        ‘(修正後)
        re.Pattern = “[\x01-\x7Eヲ-゚]” ‘全角文字以外

        • Table より:

          管理人様
          ありがとうございます。

          全然関係のないほかのセルをコピーすると、下記のエラーが出ます。

          実行時エラー 13:
          型が一致しません。

          デバッグを押すと下記のコードにいきます。

          If target = Range(“C16”) Or target = Range(“C20”) _
          Or target = Range(“C24”) Then

          見ていただけないでしょうか?
          宜しくお願い致します。

          • サイト管理人 より:

            すみません。エラー処理を追加してください。

  5. Table より:

    管理人様
    たびたびすいません。

    一番最初の質問のところなのですが
    下記の様にコードを書き直したのですが
    K列~O列、S列~W列は、大丈夫なのですが
    AA列~AE列がエラーになります。
    offsetだと思うのですが、どのように修正すれば
    良いのでしょうか?

    標準モジュール

    errArray = Array(“大項目”, “中項目”, “小項目”, “規格”, “数量”)
    If target.Column > 10 Then
    num = target.Column – 9
    Else
    num = target.Column
    End If

    If target.Cells.Offset(0, offsetCol) = “” Then
    If target.Cells “” Then
    result = MsgBox(Prompt:=errArray(num + offsetCol – 9) & “を入力してください!”, _
    Buttons:=vbRetryCancel + vbCritical, Title:=”警告”)

    シートモジュール

    Case 11, 19, 27 ‘K列・S列・AA列
    Call ShowErrMsg(target, -1)

    Case 12, 20, 28 ‘L列・T列・AB列
    Call ShowErrMsg(target, -2)

    Case 13, 21, 29 ‘M列・U列・AC列
    Call ShowErrMsg(target, -3)

    Case 14, 22, 30 ‘N列・V列・AD列
    Call ShowErrMsg(target, -4)

    Case 15, 23, 31 ‘O列・W列・AE列
    Call ShowErrMsg(target, -5)

    お手数をお掛けいたしますが、宜しくお願い致します。

    • サイト管理人 より:

      「ここから」~「ここまで」の部分を修正してください

Message

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

  関連記事

文字の向きを表す値を取得・設定する(Orientation プロパティ)

Orientation プロパティ Orientation プロパティで、文字列 …

ハイパーリンク(Hyperlink オブジェクト)

Hyperlink オブジェクト Hyperlinks コレクション のメンバー …

罫線の設定(Borders プロパティ)

罫線の設定 罫線を一括(上下左右すべて)で設定するには、Borders プロパテ …

直前のセル・直後のセルを参照する(Previous プロパティ・Next プロパティ)

Previous プロパティ・Next プロパティ Previous プロパティ …

オートフィルタ(日付フィルタ)AutoFilter メソッド

日付フィルタを使用してレコードを抽出する Excel 2007 から「日付フィル …

RemoveDuplicates メソッド 使用例1a
重複するデータを削除(RemoveDuplicates メソッド)

RemoveDuplicates メソッド 指定したセル範囲から重複データを含む …

文字列の一部の書式を変更(Characters プロパティ)

文字列の一部を参照(Characters プロパティ) Characters プ …

オートフィルタの状況を確認(AutoFilterMode, FilterMode)

AutoFilterMode プロパティ AutoFilterMode プロパテ …

並べ替え(Sort オブジェクト, SortFields コレクション)

Sort オブジェクトによる並べ替え Excel 2007 以降のバージョンでの …

セルに値を設定する・セルの値を参照する(Value2 プロパティ)

Value2 プロパティ Value プロパティとの違いは、Value2 プロパ …