(続）エクセルでVBAを使い、時間入力10：30：45（10時30分45秒）の：コロン省略したい。

始業と終業の時間を入力し勤務時間を計算しております。
時間は時：分：秒まで入力します。
例えば始業時間はA1に8：30：25 、終業時間はB1に17：00：45と入れております。
コロン（：）を省略できるととても楽になります。8：30：25は83025と入力、17：00：45は170045と入力して、
表示にはコロンが入り、セルの書式としては時刻と認識し時間差を（時間：分：秒）と計算出来ればありがたいです。
よろしくお願い致します。

VBAを使った（時間：分）のコロンを入力しないで、かつ表示にはコロンが入り、セルの書式としては時刻と認識し時間計算が出来る例は検索できました。
下記です。 t****さんありがとうございました。
ですがそれを秒まで拡大し（時間：分：秒）に対応できるVBAに変更できず困っております。
何卒よろしくお願い致します。

-----------------------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range) 'この行から
If Intersect(Target, Range("A:B")) Is Nothing Or Target.Count > 1 Then Exit Sub
With Target
If .Value <> "" Then
If IsNumeric(.Value) Then
If .Value < 2400 And .Value Mod 100 < 60 Then
Application.EnableEvents = False
.Value = TimeSerial(Int(.Value / 100), .Value Mod 100, 0)
.NumberFormatLocal = "h:mm"
Application.EnableEvents = True
Else
MsgBox "入力値が不正です"
.Select
.ClearContents
End If
End If
End If
End With
End Sub 'この行まで
-----------------------------------------------------------------------------

質問者からの補足コメント

• ご回答ありがとうございます。　上記２点の変更だけでは駄目のようです。

  No.2の回答に寄せられた補足コメントです。 補足日時：2017/04/20 15:31

• すみません、追加の質問をさせてください。
  下記の理由ため、
  朝6時の場合30：00：00と入力することも可能としたいのですが、
  御教授いただければ幸いです。よろしくお願い致します。
  
  日をまたいだ勤務時間の場合
  例えば開始時刻が22：35：20、終了時刻が6：36：25の時に
  勤務時間の計算をする場合、終了時刻を30：36：25と入力できれば
  計算が楽になるので。
  
  以上よろしくお願いします。

    補足日時：2017/04/21 09:23

No.5 ベストアンサー 回答者： ママチャリ 回答日時： 2017/04/22 10:22

変換ロジックを関数化して実装してみました。

「8:30:25」「83025」どちらの入力でも対応できているはずです。
ちなみに、２未満を入力すると日とみなし、２以上を入力すると秒とみなして表示されます（この辺はやってみて下さい）。

Private Sub Worksheet_Change(ByVal Target As Range) 'この行から
If Intersect(Target, Range("A:B")) Is Nothing Or Target.Count > 1 Then Exit Sub
With Target
If .Value <> "" Then
Application.EnableEvents = False
.Value = MyTimeValue(.Value)
If .Value = "" Then
MsgBox "入力値が不正です"
.Select
Else
.NumberFormatLocal = "[h]:mm:ss"
End If
Application.EnableEvents = True
End If
End With
End Sub 'この行まで

Function MyTimeValue(MyTime As Variant) As Variant
Dim t As Variant
Dim d As Variant
If IsNumeric(MyTime) And MyTime < 2 Then
MyTimeValue = MyTime
Exit Function
End If
On Error GoTo Fin
t = Split(Format(MyTime, "000:00:00"), ":")
d = Int(t(0) / 24)
t(0) = t(0) Mod 24
MyTimeValue = d + TimeSerial(t(0), t(1), t(2))
Fin:
End Function

この回答へのお礼

ご回答どうもありがとうございました。
おかげ様で問題解決できました。

お礼日時：2017/04/22 16:05

No.4 回答者： WindFaller 回答日時： 2017/04/21 11:45

>勤務時間の計算をする場合、終了時刻を30：36：25と入力できれば

それは、アップした後に気づきました。以下は、#3と基本構造は変わっていません。昔、一度やったことがあったことも思い出し、日付を足せばできます。
一旦、.Value = 文字の時間 で貼り付けて、再変換する方法か、どちらかだと思います。こちらは、あえてVBAらしくしました。

#3 には間違いがありましたので、ここでお詫びします。
>　　　.NumberFormatLocal = "h:mm"
>日付値はどちらでもよいですが、時間値は、入力の前に入れるのが良いようです。
今回、この方式では、まったく別な数字が出ることが分かりました。発言の訂正をいたします。

書式は統一して使うようにしました。
.NumberFormatLocal = "[h]:mm:ss"　'書式

'//
Private Sub Worksheet_Change(ByVal Target As Range)
　Dim myTime1 As Variant, myTime2 As Variant, myDay As Variant
　If Intersect(Target, Range("A:B")) Is Nothing Then Exit Sub
　If Target.Count > 1 Then Exit Sub
　If Target.Value = "" Then Exit Sub
　If Not IsNumeric(Target.Value) Then Exit Sub
　With Target
　　myValue = .Text
　　If Len(.Value) = 3 Or Len(.Value) = 4 Then
　　　myTime = Format(.Value, "0:00")
　　ElseIf Len(.Value) > 4 And Len(.Value) <= 6 Then
　　　myTime = Format(.Value, "0:00:00")
　　Else
　　　MsgBox "文字長が範囲外で時間に変換できません。", vbExclamation
　　　Exit Sub
　　End If
　End With
　'-------主な変更部分----
　　myTime1 = Left(myTime, 2)　'時間
　　myTime2 = Mid(myTime, 4)　'分・秒
　　myDay = Int(myTime1 / 24)　'日付を取り出す
　　myTime1 = CLng(myTime1) - myDay * 24　'残りの時間
　　myTime = myTime1 & ":" & myTime2　'元に戻す
　 With Target
　　If IsDate(myTime) Then
　　　Application.EnableEvents = False
　　　.Value = myDay + TimeValue(myTime)
　　　.NumberFormatLocal = "[h]:mm:ss"　'書式
　　　Application.EnableEvents = True
　　Else
　　　MsgBox "その数値は正しくありません。", vbExclamation
　　　.ClearContents
　　End If
　End With
End Sub

この回答へのお礼

御回答どうもありがとうございました。

お礼日時：2017/04/22 16:07

No.3 回答者： WindFaller 回答日時： 2017/04/20 20:23

私も考えてみました。

秒まで入れる時と、時間・分までの時、両方が入ることができるように考えてみました。
時間・分が不要なら、その部分は、取り去ればよいです。
　If Len(.Value) = 3 Or Len(.Value) = 4 Then
　　　myTime = Format(.Value, "0:00")　　'分まで入力
　ElseIf
上二行を削除して、ElseIf ->If に変える
それから、
　　　.NumberFormatLocal = "h:mm"
日付値はどちらでもよいですが、時間値は、入力の前に入れるのが良いようです。

'//
Private Sub Worksheet_Change(ByVal Target As Range)
　Dim myTimee As Variant
　If Intersect(Target, Range("A:B")) Is Nothing Then Exit Sub
　If Target.Count > 1 Then Exit Sub
　If Target.Value = "" Then Exit Sub
　If Not IsNumeric(Target.Value) Then Exit Sub
　With Target
　　myValue = .Text
　　If Len(.Value) = 3 Or Len(.Value) = 4 Then
　　　myTime = Format(.Value, "0:00")　　'分まで入力
　　ElseIf Len(.Value) > 4 And Len(.Value) <= 6 Then
　　　myTime = Format(.Value, "0:00:00")　'秒まで入力
　　Else
　　　Exit Sub
　　End If
　　If IsDate(myTime) Then
　　　Application.EnableEvents = False
　　　.NumberFormatLocal = "h:mm:ss" '秒までに変更
　　　.Value = TimeValue(myTime)
　　　Application.EnableEvents = True
　　Else
　　　MsgBox "その数値は時間値として正しくありません。", vbExclamation
　　　.ClearContents
　　End If
　End With
End Sub

No.2 回答者： GooUserラック 回答日時： 2017/04/20 15:10

No.1 の修正です

.Value = TimeSerial(Int(.Value / 100), .Value Mod 100, 0)
.NumberFormatLocal = "h:mm"
上の２行を
.Value = CDate(Format(Target.Value, "00:00:00"))
.NumberFormatLocal = "h:mm:ss"
に変更するだけではダメですか？

この回答へのお礼

ご回答ありがとうございます。上記変更と6行目にある2400を240000に変更することにより解決しました。誠にありがとうございました。

お礼日時：2017/04/20 16:09

No.1 回答者： GooUserラック 回答日時： 2017/04/20 15:08

.Value = TimeSerial(Int(.Value / 100), .Value Mod 100, 0)

.NumberFormatLocal = "h:mm"
上の２行を
.Value = CDate(Format(Target.Value, "00:00:00"))
.NumberFormatLocal = "hh:mm:ss"
に変更するだけではダメですか？