図1●セルの入力規則機能を使って,担当の氏名を選択形式にしている様子
図1●セルの入力規則機能を使って,担当の氏名を選択形式にしている様子
[画像のクリックで拡大表示]
表1●ワークシートで利用できる8種類のイベント・プロシジャ
表1●ワークシートで利用できる8種類のイベント・プロシジャ
[画像のクリックで拡大表示]
リスト1●セルをダブルクリックしたときに,オリジナルの動作の前に実行されるプロシジャ。このプロシジャを使ってセルに文字列を自動入力する
リスト1●セルをダブルクリックしたときに,オリジナルの動作の前に実行されるプロシジャ。このプロシジャを使ってセルに文字列を自動入力する
[画像のクリックで拡大表示]

今月の相談

「セルの入力規則(入力可能な値を制限する機能)を使って,商品の受発注状況をドロップダウンリストから選択入力するようにしました。しかし,思いのほか担当者が選択ミスするケースが多くて困っています。何かいい方法はないでしょうか?」

 どれどれ。商品の状況を「受注」「発送前」「配達中」「配達済」の四つから選択入力するようにしたわけですね。でも,発送前の商品なのに,うっかり「配達中」や「配達済」にしてしまうことがあると。うっかりミスは誰にでもあることなので,ミスをゼロにすることはできませんが,なるべく選択ミスなどをしないように作る側が工夫してあげることは大事ですね。

どれでも選択できるのが
リストの利点かつ欠点

 セルに入力する値を制限したり,ドロップダウンリストからの選択形式にできる「入力規則」は,とても便利な機能です。値の種類がいくつかに決まっている場合は,入力規則であらかじめ入力値を設定しておいて,その中から選択するようにすれば,ユーザーも楽ですし,タイプミスなども防げます。

 例えば,担当者名を入力するセルで「小林」「佐藤」「山田」の三人から選択するようにするには,まず[データ]メニューの[入力規則]をクリックして「データの入力規則」ダイアログを呼び出します。[設定]タブの「入力値の種類」で「リスト」を選択し,「元の値」のフィールドに「小林,佐藤,山田」(カンマは半角)と入力します(図1[拡大表示])。すると,このセルをクリックすると,セルの横にボタン(下向き三角)が現れて担当者名をドロップダウンリストから選択可能になります。

 担当者名のように,それぞれの項目が独立している場合は,この方法で特に問題はありません。しかし今回のケースでは,受注→発送前→配達中→配達済という具合に,各項目に順序があります。こうした項目を単純に四者択一の選択形式にするのはあまりよくありません。なるべく順番通りに選択できるようにしてあげるのが良いシステムと言えるでしょう。

 それにはいくつか方法があります。今回は,ワークシートのイベントを利用してみましょう。ワークシートには,表1[拡大表示]のように8種類のイベント・プロシジャがあります。その中からWorksheet BeforeDoubleClickイベント・プロシジャを使います。このプロシジャは,セルがダブルクリックされると,オリジナルの動作の前に呼び出されます。

 通常セルをダブルクリックすると編集モードになります。このプロシジャはセルが編集モードになる前に呼び出され,実行されます。オリジナルの動作(この場合編集モードになる)を,マクロの中でキャンセルすることも可能です。

イベント・プロシジャは記述する場所に注意

 イベントが発生したときに自動実行されるイベント・プロシジャを利用する場合は,どのモジュールに記述するかに注意してください。標準モジュールに記述してもイベント・プロシジャとしては機能しません。特定のワークシート(Sheet1,Sheet2など)またはブック全体(ThisWorkbook)モジュールのどちらかに記述します。

 シートのモジュールに記述した場合は,そのシートで発生したイベントにだけ反応します。他のシートでイベントが発生しても実行されません。一方,ThisWorkbookモジュールに記述したイベント・プロシジャは,すべてのシートで発生したイベントに反応します。なお,二つのモジュールには共通するイベントと,それぞれオリジナルのイベントがありますので,利用したいイベント・プロシジャがそのモジュールで使えるかどうかは事前に調べておく必要があります。

引数Targetを使ってセルの値を変える

 では,今回のマクロを紹介しましょう(リスト1[拡大表示])。このマクロを実行すると,特定のセルをダブルクリックすることで,入力項目を選択できるようになります。具体的には,商品の状況を設定するセルをダブルクリックすると,最初に「受注」という項目が入力されます。さらにダブルクリックを繰り返すことによって,発送前→配達中→配達済という順に入力項目が変わります。これによって,受注したばかりの商品をいきなり配達済にしてしまうといったミスを防ぐわけです。

リスト1●セルをダブルクリックしたときに,オリジナルの動作の前に実行されるプロシジャ。このプロシジャを使ってセルに文字列を自動入力する

Private Sub Worksheet_BeforeDoubleClick( _
    ByVal Target As Range, Cancel As Boolean)
    If Target.Column = 8 And Target.Row >= 4 Then
        Select Case Target.Value
            Case ""
                Target.Value = "受注"
                Cells(Target.Row, 7).Value = Now
            Case "受注"
                Target.Value = "発送前"
            Case "発送前"
                Target.Value = "配達中"
            Case "配達中"
                Target.Value = "配達済"
                Cells(Target.Row, 12).Value = Now
            Case "配達済"
                Target.Value = ""
                Cells(Target.Row, 12).Value = Empty
                Cells(Target.Row, 7).Value = Empty
            Case Else
                MsgBox "想定外の文字が入力されています", _
                vbInformation
        End Select
        Cancel = True
    End If
End Sub

 コードを順に見ていきましょう。(1)はイベント・プロシジャの宣言文です。プロシジャ名を変更するとイベント・プロシジャとして機能しないので注意してください。引数は二つあります。Targetは,ダブルクリックされたセルをオブジェクトで受け取ります。Cancelはブール型(Boolean)で,オリジナルの動作が停止しているかどうかを受け取ります。この引数がTrueの場合は停止しており,Falseの場合は生きています。

 最初に(2)の条件分岐で,Targetの行番号と列番号から操作対象のセルであるかどうかを判断します。ダブルクリックされたセルが操作対象外のセルである場合は何もしません。

 次に(3)のSelectステートメントで,Targetに入力されている文字によって動作を分けます。文字数0の文字列の場合(4)は,Targetの値を「受注」にします(5)。同時に同じ行のG列(受注日)のセルに当日の日付(年月日と時間)を入力します(6)。

 セルの値が「受注」の場合は,「発送前」に変更します(7)。同様に,「発送前」の場合は「配達中」に,「配達中」の場合は「配達済」に変更します(8)(9)。また,配達中の場合は,同じ行のL列(配達日時)のセルに日付を入力します(10)。「配達済」の場合(11)は,Targetに文字数0の文字列を入力して,同じ行のG列とL列のセルも空白にします(12)。これらのステートメントにより,ダブルクリック時の動作は順繰りに変わることになります。

 セルに想定外の文字列が入力されている場合にはメッセージ・ボックスを表示します(13)。場合によっては,セルの直接入力を禁止して,想定外の文字列が入る余地を取り除く手もあるでしょう。しかし,仕事にアクシデント(イレギュラーな状況)はつきものです。そうした場合に備えて,商品状況のセルに何が起きているかを直接入力できるようにしています。

 最後に,CancelプロパティにTrueをセットして,オリジナルの動作をキャンセルします(14)。これにより,マクロが終了しても編集モードに入ることはありません。

古庄 潤