ワークシートのセルに特定のデータを入力する

2011年8月05日

～入力規則の設定とマクロの記録～

ワークシートのセルに特定のデータを入力したいことが良くあります。

たとえば、会員名、取引先会社名、住所のうちの県名や市名、町名など。あらかじめ用意しておいてリストから選択して入力出来れば簡単になります。


こんなとき［入力規則］を使えばいいことはみなさん知っていると思います。

■入力規則の設定は以下のようにすればできます。

［データ］－［データの入力規則］－［データの入力規則］を押します。

次の画面で、［入力値の種類］から［リスト］を選択。

［元の値］の右側のアイコンをクリックして、シート上に予め作っておいた入力値リストの範囲をドラッグして指定します。

次に[OK]を押せば完了です。

Excel2010までは(2007は使ってませんのでわかりませんが、Excel2003までは)、入力規則の元になるリストとして別のシートにあるものを使うのはひと苦労でした。

Excel2010からはどこのシートでも同じように簡単にできるようになりました。

こんどは、マクロVBAからこれを設定する方法について解説します。

Excelのシート上での操作をマクロでやりたいとき、いちどその操作を［マクロの記録］でやってみて、Excelがどんなマクロコードを生成するか確認してみると、自分でプログラムを作成するときの参考になります。

［マクロの記録］では、シート上での操作をマクロプログラムに落とすので、そのプログラムは、特定のシートであったり、特定のセルに対する操作に限定されたものになります。

しかし、少しの修正で汎用性のあるプログラムに改良することができることもあります

ではやってみましょう。

■まず、［開発］-[マクロの記録]を押します。

すると。次の画面が表示されます。

通常はそのまま[OK]を押します。


次に入力規則の設定操作を行います。すでに説明した入力規則の設定操作をしてください。

操作が終了したら［記録終了］を押してください。

■生成されたマクロプログラムを見てみましょう。

［開発］－［VisualBasic］を押し、

マクロの記録画面で［マクロ名］に記載されていたマクロ名（上の例では「Macro2」）のプロシージャを探します。

左側ペインの、いま開いているワークブックの下の［標準モジュール］の下の［Module1］（数字の部分は、環境により異なります）をダブルクリックし、右側ペインから、[マクロ名が[Macro2]の場合、［sub Macro2()］を探してください。

Sub Macro2()

' Macro2 Macro

'

  With Selection.Validation

        .Delete

        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _

        xlBetween, Formula1:="=Sheet2!$E$2:$E$6"

        .IgnoreBlank = True

        .InCellDropdown = True

        .InputTitle = ""

        .ErrorTitle = ""

        .InputMessage = ""

        .ErrorMessage = ""

        .IMEMode = xlIMEModeNoControl

        .ShowInput = True

        .ShowError = True

    End With

End Sub

このマクロプロク［ラムでは、Selection とSheet2!$E$2:$E$6 の部分が特定のセル範囲を指しています。

これを個別に指定できるようにプログラムを変更したものが、次のリストです。

Public Sub set_Input_Rule(trgtCell As Range, wrksht As Worksheet, ruleRange As Variant)

     'trgtCell  : 入力規則を設定するセル

    'wrksht    : 入力規則の元になるリストがあるワークシート

    'ruleRange : 入力規則の元になるリストの範囲名

 

    With trgtCell.Validation '←　変更箇所［１］

        .Delete

        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _

            Operator:=xlBetween, _

            Formula1:="=" & wrksht.Name & "!" & ruleRange '←　変更箇所［2］

        .IgnoreBlank = True

        .InCellDropdown = True

        .InputTitle = ""

        .ErrorTitle = ""

        .InputMessage = ""

        .ErrorMessage = ""

        .IMEMode = xlIMEModeNoControl

        .ShowInput = True

        .ShowError = True

    End With

 End Sub

またこのプロシージャをセル範囲を指定して呼び出すプロシージャが、次のリストです。

Public Sub set_rule()

    Call set_Input_Rule(ActiveCell, Worksheets("Sheet2"), "E2:E6")

 End Sub

この例では固定セル・シートをプロシージャに渡していますが、任意のセルやシートに変更するのは容易にできるでしょう。

マクロの記録は非常に応用のきくやり方です。ぜひこれを覚えて、いつも同じ手順で操作する定型業務をボタン一発で実行できるマクロをつくりましょう。仕事の効率が格段にアップしますよ。

なお、マクロをボタンに割り当てる方法は別の記事で解説したいと思います。