Worksheet_Change4桁数字の入力を時間に変換

Worksheet_Change時間入力をカンタンにする

4桁数字の入力を時間に変換 11回

時間入力の手間をなくす

この記事で学べる内容

Worksheet_Changeイベントを使って「入力直後にセルを書式変換する」処理を自動化する方法を理解する
Intersect(Target, Range("D:D,E:E")) を使って、時間入力用の列だけを処理対象にする方法を学ぶ
IsNumeric関数とInStr関数を組み合わせて「数値で、かつコロンが含まれていないときだけ」処理を実行する条件式の書き方を習得する
Target.Value = Format(Target.Value, "00:00") で「1800 → 18:00」のように時間入力を省力化するテクニックを学ぶ
一度入力したセルを空にしたときに、NumberFormatLocalを"G/標準"に戻しておく理由と、そのコードの書き方を理解する
3桁・4桁の数値入力を想定したイベントマクロでも、あとから修正できるようにするための分岐設計を学ぶ
複数列（D列・E列など）を一括指定してイベント処理する方法を複数パターン（Range("D4:E34")／Columns("D:E")／Union）で確認する
「コロンを打つ手間をなくす」ような入力支援マクロを業務シートに組み込むアイデアを得る

さまざまなデータで時間の入力作業というものがあります。

今回のサンプルも時間をエクセルに理解させるために、「：（コロン）」を間にいれて入力するようになっています。これが一番の手間ですね。数字だけの入力なら片手でできますが、「：（コロン）」を間に入れるために、両手を使わなければいけないからです。
今回はその作業を楽にするようにイベントを使ってみましょう。

Worksheet_Change

1. Sheet1に時間を入力する範囲があります。これを範囲限定するのに、複数の方法があります。
まず、 Intersectメソッドで「ある範囲Targetが範囲Range("D4:E34")に入っているかどうか」を判定する方法です。あるいは、範囲を列として指定することもできます。
Intersect(Target,Range(Columns(4), Columns(5))) ,または
Intersect(Target,Range("D:D, E:E"))
Intersect(Target, Columns("D:E"))
Intersect(Target, Union(Columns(4), Columns(5)))
のようにも書けます。きちんと指定したい場合は、範囲に名前をつけておきその名前で指定することもできます。

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("範囲につけた名前")) Is Nothing Then
        '実行するコード
    End If
End Sub

2. 次に '実行するコードに書くコードです。
数値の入力ですが、入力するセルはデフォルトの標準としています。
そこに3桁あるいは4桁の数値を入力します。
またIF文を使って、入力された値が数値で、中に「：」が含まれない場合に限定します。
ここで使うのは、IsNumeric関数です。数値として扱えるかどうかを判定します。
そしてInStr関数でTarget内に引数で指定した：コロンを探して、0であればというその値をユーザー定義で時間に設定します。

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("D:D,E:E")) Is Nothing Then
        If IsNumeric(Target.Value) And InStr(Target.Text, ":") = 0 Then
        Target.Value = Format(Target.Value, "00:00")
        End If
    End If
End Sub

このコードをSheet1に設定して、数値を入力します。 1800と入力すると、18：00と時間に表示されました。

いかがでしょうか。時間入力の手間を省くイベントです。Worksheet_Changeで書式設定して時間表示するマクロを紹介しました。

時間表示のセルの変更

3. さらに手を加えて、時間表示のセルの変更の場合をかんがえます。
入力された時間を訂正しようとすると、18：00：00のように時間表示になっています。直接訂正することができます。

4. ただし、一旦入力されたデータを消去して、再入力する方が楽かもしれません。その時にはコードを追加する必要があります。
理由は、3桁4桁の数値で入力するためには、セルの書式が標準である必要があるからです。
もし、 Target.Value = ""ならは、書式を "G/標準" にすると設定します。そして一旦このプロシージャを終了させます。

    If Target.Value = "" Then
        Target.NumberFormatLocal = "G/標準"
    Exit Sub
    End If

修正も考慮したWorksheet_Changeのコード

時間を3桁4桁数値で入力するイベントのコードです。

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Value = "" Then
        Target.NumberFormatLocal = "G/標準"
    Exit Sub
    End If
    If Not Intersect(Target, Range("D:D,E:E")) Is Nothing Then
        If IsNumeric(Target.Value) And InStr(Target.Text, ":") = 0 Then
        Target.Value = Format(Target.Value, "00:00")
        End If
    End If
End Sub