Excelで：（コロン）を省略した入力と表示

質問

効率よく時間入力したく、：（コロン）を省略した入力方法を
VBAの機能を使用して設定したのですが、
『0：30』を入力すると『：30』となってしまいます（0時台以外は問題なく表示）。

＜コード内容は下記となります＞
----------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)

Dim i As Integer, j As Integer
Dim sTime As String
Dim iTime As Integer
On Error Resume Next

For i = 3 To 52
For j = 6 To 7

sTime = CStr(CInt(Cells(i, j).Value))
Cells(i, j).Value = Left(sTime, Len(sTime) - 2) + ":" + Right(sTime, 2)

Next j
Next i

End Sub
----------------------------------------------------------

＜入力、表示の条件といたしましては...＞
○　コロンを省略して入力（1100と入力→11:00と表示、030と入力→0：30と表示）
○　24：00を過ぎた時間を25:00、26:00…と表示
○　入力後の値の認識は、可能であれば時刻、ダメなら文字列

どなたかExcel VBA詳しい方、教えて頂ければ幸いです。

回答No.4

real beatin（@realbeatin） · ベストアンサー

こんにちは。

24:00,25:00,26:00は[時刻]ではないので、
今回の課題は、すべて[時間]を扱う処理という理解でいます。
　24時間未満の場合は、[時刻]値、
　24時間以上の場合は、[日付＋時刻]値、
として[時間]を表現してみます。
セルに表示されるのは、"[h]:mm"形式の時間です。
[文字列]値は集計やフィルタリング等の邪魔なので、使いません。

> 効率よく時間入力したく、：（コロン）を省略した入力方法を
という基準（方向付け）で判断し、こちらで適当な仕様を選んでいます。
この課題は本来、コントロールを使って実現するのが妥当です。
セル範囲への入力を基に処理するのは、若干無理気味で、
必ず何等かの制約を受けることになります。
簡易な方法で"効率よく"入力する「代償」と考えてください。
如何に制約を軽くするか、全体の設計について時間を掛けて考えました。
ひとつの結論として、同じセルに再度入力したい場合は、
一度セルを消去してからでないと機能しない、という一点に、
制約を絞ることが出来ました。
この点は、現場で周知を図る必要があります。
それ以外の部分は、
例えば、通常の（コロン付きの正しい[時間]）入力方法でも
正しく確定されるようにするなど、なるべく自然な作業を
維持できるように意図して、仕様を追加しました。

以下、動作仕様説明。

> ○　コロンを省略して入力（1100と入力→11:00と表示、030と入力→0：30と表示）
→　整数値入力の場合、十の位が[0-5]であれば、すべて
　　コロンを挟んで[時刻]値or[日付＋時刻]値へ変換。
→　コロンを挟んでも[時刻]or[日付＋時刻]として評価できない値であれば、
　　何もしないでそのまま。
→　元々[時刻]値が入力されていた場合は、
　　何もしないでそのまま。
→　"0"や"30"など３桁未満の整数値に対しても処理する仕様を選んだ。
'○　24：00を過ぎた時間を25:00、26:00…と表示
→　整数値入力の場合、十の位が[0-5]であれば、すべて
　　コロンを挟んで[日付＋時刻]値へ変換。
→　コロンを挟んでも[日付＋時刻]として評価できない値であれば、
　　何もしないでそのまま。
→　"24:00"等、24時間以上の[時間]値を入力した場合、
　　Excelの仕様"[h]:mm:ss"表示を"[h]:mm"に変更する。
'○ 入力後の値の認識は、可能であれば時刻、ダメなら文字列
→　コロンを挟むか、そのままかで
　　[時刻]or[日付＋時刻]として評価できる場合は、
　　[時刻]or[日付＋時刻]に統一。
→　但し、以上の仕様を実現する為、
　　入力時の[セルの書式][表示形式]は[標準]
　　である場合のみ、正しく機能するような仕様を選んだ。
　　同じセルに再度入力をする場合は、一旦セル値を消去すれば、
　　[表示形式]を[標準]に戻し、次の入力では正しく機能する。
他
→　処理を実行するのは F3:G52 の範囲の値変更（確定）に限定した。
→　理論上の無限ループを回避するよう正した。
→　複数セル範囲の値変更（コピペや[Ctrl+Enter]入力）
　　に対しても処理されるような仕様を選んだ。

尚、この記述をお使いになる前に、一度、
対象セル範囲の[セルの書式][表示形式]を[標準]に戻しておく必要があります。
言わずもがなですが、同じ処理を狙った他の記述との併用は一切出来ません。

' ' ----------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myTarget As Range
Dim c As Range
Dim vTemp As Variant
Dim sTemp As String

　　Set myTarget = Intersect(Target, Range("F3:G52"))
　　If myTarget Is Nothing Then Exit Sub
　　For Each c In myTarget
　　　　vTemp = c.Value
　　　　sTemp = c.Text
　　　　If sTemp = "" Then
　　　　　　c.NumberFormat = "General"
　　　　Else
　　　　　　If Not IsDate("2015/1/1 " & sTemp) Then
　　　　　　　　If IsNumeric(sTemp) Then
　　　　　　　　　　If "00" & sTemp Like "*#[0-5]#" Then
　　　　　　　　　　　　If vTemp > 2400 Then c.NumberFormat = "[h]:mm"
　　　　　　　　　　　　Application.EnableEvents = False
　　　　　　　　　　　　c.Value = Format(sTemp, "0:00")
　　　　　　　　　　　　Application.EnableEvents = True
　　　　　　　　　　End If
　　　　　　　　Else
　　　　　　　　　　If c.NumberFormat = "[h]:mm:ss" Then c.NumberFormat = "[h]:mm"
　　　　　　　　End If
　　　　　　End If
　　　　End If
　　Next
　　Set myTarget = Nothing
End Sub
' ' ----------------------------------------------------------

お礼

素人質問にも関わらず、動作仕様説明や細やかなご説明までして頂き、
大変感謝しております。
これを機に勉強励みます。
有難うございました。

回答No.3

mshr1962

まず一つ For・・・Nextに対して　Worksheet_Change　を使うと無限ループになります。
押しボタンにマクロを割り当てるようにした方がいいと思いますけど。。。
後、同じセルに連続して行うと0:00へと近づくので制限として30分以上の場合実行する等設けたほうがいいでしょう。

○　コロンを省略して入力（1100と入力→11:00と表示、030と入力→0：30と表示）
Cells(i, j).Value　= Format(Cells(i, j).Value , "00:00" )

○　24：00を過ぎた時間を25:00、26:00…と表示
Cells(i, j).NumberFormatLocal = "[h]:mm"

－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
Private Sub TIMECHANGE()

Dim i As Integer, j As Integer
On Error Resume Next

For i = 3 To 52
For j = 6 To 7

If IsNumeric(Cells(i, j).Value) And Cells(i, j).Value >= 30 Then
Cells(i, j).Value = Format(Cells(i, j).Value, "00:00")
Cells(i, j).NumberFormatLocal = "[h]:mm"
End If

Next j
Next i

End Sub

回答No.2

dogs_cats

NO1です。
修正します。
sTimeが文字列宣言されていましたのでstimeで数値比較は出来ませんでした。
分の桁は必ず２桁あるのでしょうか？
現状9分は:9と表示すると思いますが、0:09表示とするのでしたら下記となります。

sTime = CStr(CInt(Cells(i, j).Value))

if　Cells(i, j).Value<10 then
Cells(i, j).Value="0:" & right("0" & sTime,2)
elseif Cells(i, j).Value<100 then
Cells(i, j).Value="0:" & Right( sTime, 2)
else
Cells(i, j).Value = Left(sTime, Len(sTime) - 2) + ":" + Right(sTime, 2)
end if

お礼

早い段階での回答、またその後の修正の回答まで頂き有難うございました。

回答No.1

dogs_cats

エクセルの数値では先頭に0がつく事がありません。表示形式で0を表示させる事は可能ですが、数値としては0時台は１～59の整数となりますので作成されたコードでは　:59　という表示になります。
セルの数値が100以下（60以下でも良いですが）の場合は0をつけるようにすれば良いと思います。

sTime = CStr(CInt(Cells(i, j).Value))

if sTime<100 then Cells(i, j).Value ="0:" + Right(sTime, 2) else
Cells(i, j).Value = Left(sTime, Len(sTime) - 2) + ":" + Right(sTime, 2)