[[20230923093854]]　『1234の数値を入力して12：34と表示、かつ時間の計』（湯豆腐）　ページの最後に飛ぶ

『1234の数値を入力して12：34と表示、かつ時間の計算がしたいです。』（湯豆腐）

毎度お世話になります。
出勤簿をExcelで作成しているのですが、
指定のセルの入力を「1234」と打つと「12：34」と表示されるようにしたいです。
セルの書式設定では時間計算ができなくなってしまうので、マクロで検索して下記のコードにたどり着きました。

Private Sub Worksheet_Change(ByVal Target As Range)
Dim t As String
On Error Resume Next
t = Target.Value
If Application.Intersect(Target, Range("A1,C1:D2")) Is Nothing Then Exit Sub
If Len(t) = 3 Then t = "0" & t
If Len(t) = 2 Then t = "00" & t
If Len(t) = 1 Then t = "000" & t
If Len(t) <> 4 Then Exit Sub
With Target
.NumberFormatLocal = "h:mm;@"
.Formula = Left(t, 2) & ":" & Right(t, 2)
End With
End Sub

だいたい変換できたのですが、
600→0.：25　　1200→00：.5　　1800→0.：75　2400→0：01　3000→1.:25　　3600→01:.5
となってしまいます。
※夜勤があるので36：00くらいまでは残業を含めて想定しています。

コードの修正もしくは他のコードで同様の事が出来ればと思います。
どうかご教授願います。

＜使用 Excel：Excel2019、使用 OS：Windows10 ＞

 イベント連鎖してるからですね。

 Application.EnableEvents = False　’★追加
 .Formula = Left(t, 2) & ":" & Right(t, 2)
 Application.EnableEvents = True  ’★追加

 それと、値の代入は「Formula」でもいいけど普通は「Value」ですね。

（まる2021） 2023/09/23(土) 10:34:12

 24時超えについては、
 「3600」を「36:00」と表示するには、表示形式の部分を「[h]:mm」とすればOK。

 「3600」を「12:00」と表示するには、
 If Application.Intersect(Target, Range("A1,C1:D2")) Is Nothing Then Exit Sub
 If CLng(t) >= 2400 Then t = CStr(CLng(t) - 2400) '★追加
 でも、これだと「当日/翌日」の区別がつかないですね。
（まる2021） 2023/09/23(土) 10:51:36

まる2021様
有難うございます！うまくいきました。
表示は3600の表示はどちらでも良かったのでこのままで大丈夫そうです。

単品ではうまく動いたのですが、元々あったPrivate Sub Worksheet_Changeのコードに合わせたら動かなくなってしまいました。

Private Sub Worksheet_Change(ByVal Target As Range)

    If Intersect(Target, Range("A1:E1")) Is Nothing Then
        Exit Sub
    Else
        Range("A5").Copy
        Range("A6:A47").PasteSpecial Paste:=xlPasteFormulas ' 日付5行をコピーして6～47行へペースト
        Application.CutCopyMode = False

        Cells(Weekday(DateSerial(Range("A1"), Range("E1"), 1)) + 5, 1) = 1 '年月から1日の曜日のシリアル値を取得して5行（タイトル行）足したセルに1と入力する
    End If

    Dim t As String　’ここから追加
On Error Resume Next
t = Target.Value
If Application.Intersect(Target, Range("A1:A2,B1:B2")) Is Nothing Then Exit Sub
If Len(t) = 3 Then t = "0" & t
If Len(t) = 2 Then t = "00" & t
If Len(t) = 1 Then t = "000" & t
With Target
If Len(t) <> 4 Then Exit Sub
.NumberFormatLocal = "h:mm;@"
Application.EnableEvents = False
 .Value = Left(t, 2) & ":" & Right(t, 2)
 Application.EnableEvents = True
End With

End Sub

ifと違って何かクッションが必要なのでしょうか？
（湯豆腐） 2023/09/23(土) 11:13:33

すいません、追加したコードが間違っていました。

Private Sub Worksheet_Change(ByVal Target As Range)

    If Intersect(Target, Range("A1:E1")) Is Nothing Then
        Exit Sub
    Else
        Range("A5").Copy
        Range("A6:A47").PasteSpecial Paste:=xlPasteFormulas ' 日付5行をコピーして6～47行へペースト
        Application.CutCopyMode = False
        Cells(Weekday(DateSerial(Range("A1"), Range("E1"), 1)) + 5, 1) = 1 '年月から1日の曜日のシリアル値を取得して5行（タイトル行）足したセルに1と入力する
    End If　
    Dim t As String　’ここから追加
On Error Resume Next
t = Target.Value
If Application.Intersect(Target, Range("F5:K47,M5:O47")) Is Nothing Then Exit Sub
If Len(t) = 3 Then t = "0" & t
If Len(t) = 2 Then t = "00" & t
If Len(t) = 1 Then t = "000" & t
With Target
If Len(t) <> 4 Then Exit Sub
.NumberFormatLocal = "h:mm;@"
Application.EnableEvents = False
 .Value = Left(t, 2) & ":" & Right(t, 2)
 Application.EnableEvents = True
End With
End Sub

それぞれ違うセルを指定しています。

（湯豆腐） 2023/09/23(土) 11:15:24

 Application.EnableEvents = Falseの前にDebug.Print入れたら、イミディエイトウインドウに何が表示されますか？
 入力した数値が表示されますか？

 Debug.Print t '★追加
 Application.EnableEvents = False

 「t = Target.Value」を　「t = Target.Text」　としたら、どうなりますか？
（まる2021） 2023/09/23(土) 11:29:54

 あ、ひょっとして、コピペした値に対しての処理なんですかね？
 それなら、話は変わってきます。

 すみません。午後から出かけるので、退席します。他の回答者をお待ちください。
（まる2021） 2023/09/23(土) 11:47:21

まる2021様

＞Application.EnableEvents = Falseの前にDebug.Print入れたら、イミディエイトウインドウに何が表示されますか？

 入力した数値が表示されますか？
 Debug.Print t '★追加
 Application.EnableEvents = False
→以下の様に表示されています。
0:00
0:00
0:00

＞「t = Target.Value」を　「t = Target.Text」　としたら、どうなりますか？
→変わらずマクロは作動せず、イミディエイトウィンドウも変わりません。

＞あ、ひょっとして、コピペした値に対しての処理なんですかね？

 それなら、話は変わってきます。
→コピペした値に対しての処理…というのが分かりません。
空欄のセルもしくは式の入っているセルに対して直接入力しようとしています。

（湯豆腐） 2023/09/23(土) 13:18:24

 他の人がレスをつけてくれてると思ってましたが...orz

 もう一度、読み直したら、なんとなく理解できました。
 以下のように入力したセルの場所に応じて、処理を分けるという認識で合ってますか？

 (1)Range("A1:E1")の値を変更したら、コピー処理
 (2)Range("F5:K47,M5:O47")の値を変更したら、4桁以下の数値を時間に変更処理

 それと、もう1点、時間に変更する場合
 「100」なら「1:00」、「59」なら「0:59」でいいと思いますが、
 「60」～「99」の時はどうしますか？

 ※出先なので、夜まで他の人のコメントが付かなければ帰ってから考えます。

（まる2021） 2023/09/23(土) 15:17:22

内容がタイトルと合わなくなってきたので別スレッドを作成しました。
タイトルの件は無事に解決致しました。
まる2021様有難うございました。
（湯豆腐） 2023/09/23(土) 15:18:58

まる2021様
すいません、丁度返信が被ってしまったようです；

＞(1)Range("A1:E1")の値を変更したら、コピー処理
＞(2)Range("F5:K47,M5:O47")の値を変更したら、4桁以下の数値を時間に変更処理
こちらの認識で合っています。
60～99は時間の入力でわざわざ分表記にする人はいないと思うので考えなくても大丈夫です。

出先にもかかわらずお気遣い頂き有難うございます。
（湯豆腐） 2023/09/23(土) 15:22:10

コメント返信：

[ 一覧(最新更新順) ]

[[20230923093854]] 『1234の数値を入力して12：34と表示、かつ時間の計』（湯豆腐） ページの最後に飛ぶ

[[20230923093854]]　『1234の数値を入力して12：34と表示、かつ時間の計』（湯豆腐）　ページの最後に飛ぶ