VBAのトグルボタンでのマクロについて質問です

onのときに
B列に文字を入力した際
D列に入力した時刻が入力され

offのときにonの機能がoffになり
D列とE列に3桁もしくは4桁の数字を打った際に
コロンを省略して時刻が入力されるマクロを作りましたが

onの場合は問題ないですが
offの場合入力がうまくいかない為教えてください

具体的にはtargetのオブジェクトがないか
入力しても00:00になってしまいます


ソースコード

Private Sub ToggleButton1_Click()
With ToggleButton1
If .Value Then
'トグルボタンONの処理
.Caption = "自動入力　ON"
Application.EnableEvents = True
MsgBox "自動入力が　ONになりました", vbInformation
Else

'トグルボタンOFFの処理
.Caption = "自動入力　OFF"
Application.EnableEvents = False
MsgBox "自動入力が　OFFになりました", vbInformation

If Intersect(Target, Range("D:E")) 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 If
End With
End Sub

一応裏で常駐させているイベントのコードも記しておきます

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 2 Then Exit Sub
Application.EnableEvents = False
Target.Offset(, 2).Value = Time
Application.EnableEvents = True
End Sub


よろしくお願いします！

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

• 

  トグルボタンがonの時に
  Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Column <> 2 Then Exit Sub
  Application.EnableEvents = False
  Target.Offset(, 2).Value = Time
  Application.EnableEvents = True
  End Sub
  が働いて
  
  offで上記のコードが動かなくなるようになればokです
  D列とE列に3桁もしくは4桁の数字を打った際に
  コロンを省略して時刻が入力されるところは常に動いていてokです
  
  また、
  Private Sub Worksheet_Change(ByVal Target As Range)
  に入れて動かすと
  時刻を入力した際が1912/3/1/ 0:00:00になってしまいます

    補足日時：2022/10/11 07:50

• 

  回答ありがとうございます
  
  Worksheet_Change()に記述してみたところ
  自動で時刻が入力されるのは問題ないですが
  手打ちで時刻を入力した際1903/1/15 0:00:00と
  絶対に0:00になってしまいました

  No.1の回答に寄せられた補足コメントです。 補足日時：2022/10/11 07:53

No.3 ベストアンサー 回答者： Qchan1962 回答日時： 2022/10/11 19:18

#2です・・が

myTime_input　の内容で
ToggleButton1＝Falseの時、単に.NumberFormatLocal = "G/標準"だと
ダメかなと思いましたので　下記に書き直しておきます

Sub myTime_input(ByVal Target As Range)
If Intersect(Target, Range("D:E")) Is Nothing Or Target.Count > 1 Then Exit Sub
Dim v As String
Dim nf As String
Application.EnableEvents = False
With Target
If Not ToggleButton1 Then
If .Text = .Value Then
v = .Text
Else
If .Text = "0:00" Then v = .Value Else v = .Text
End If
If IsDate(v) = True Then nf = "h:mm" Else nf = "G/標準"
If v <> "" Then .Value = v
If nf <> "" Then .NumberFormatLocal = nf
Else
If .Value <> "" And IsNumeric(.Value) Then
If .Value < 2400 And .Value Mod 100 < 60 Then
.Value = TimeSerial(Int(.Value / 100), .Value Mod 100, 0)
.NumberFormatLocal = "h:mm"
Else
MsgBox "入力値が不正です"
.ClearContents
End If
End If
End If
End With
Application.EnableEvents = True
End Sub

.Text = "0:00"は"h:mm"なので
既に別質問で解決されたようなのでスルーでOKです

No.2 回答者： Qchan1962 回答日時： 2022/10/11 14:05

こんにちは

横から失礼します
＞時刻を入力した際が1912/3/1/ 0:00:00になってしまいます
書式設定の問題では？

掲示のコードの場合、ボタンを押下した時に選択セルの値を処理するようになっていますが、この場合の要件定義は不足しているように感じます

例えば、ボタンを押下時の処理
ONにする時
ToggleButton1=True　Target 　123？　12:3　どう処理しますか？
123？＞＞"h:mm"　　12:3＞＞そのまま
OFFにする時
ToggleButton1=False Target　123？　12:3　
123？はONになっているからありえないかも知れませんが・・
12:3＞＞123？　どう処理しますか？

セルに書き込んだ場合の処理これは定義済み・・

ボタンを押下時選択セルの値をどのように処理するかで色々加える必要があると思いますが、
ボタンを押下時　値を変更しなくて良ければ例として、こんな感じ？

Private Sub ToggleButton1_Click()
With ToggleButton1
If .Value Then
'トグルボタンONの処理
.Caption = "自動入力　ON"
MsgBox "自動入力が　ONになりました", vbInformation
Else
'トグルボタンOFFの処理
.Caption = "自動入力　OFF"
MsgBox "自動入力が　OFFになりました", vbInformation
End If
End With
'Call myTime_input(ActiveCell) '必要か不明(要件不足)
End Sub

Sub myTime_input(ByVal Target As Range)
If Intersect(Target, Range("D:E")) Is Nothing Or Target.Count > 1 Then Exit Sub
Application.EnableEvents = False
With Target
If ToggleButton1 Then
If .Value <> "" And IsNumeric(.Value) Then
If .Value < 2400 And .Value Mod 100 < 60 Then
.Value = TimeSerial(Int(.Value / 100), .Value Mod 100, 0)
.NumberFormatLocal = "h:mm"
Else
MsgBox "入力値が不正です"
.ClearContents
End If
End If
Else
.NumberFormatLocal = "G/標準"
End If
End With
Application.EnableEvents = True
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("D:E")) Is Nothing Or Target.Count = 1 Then Call myTime_input(Target)
If Target.Column <> 2 Then Exit Sub
Application.EnableEvents = False
Target.Offset(, 2).Value = Time
Application.EnableEvents = True
End Sub

No.1 回答者： fujillin 回答日時： 2022/10/10 18:17

こんばんは

＞D列とE列に3桁もしくは4桁の数字を打った際に～
というのは、トグルボタンをクリックしたときに処理してほしいわけじゃないですよね？
でも、ご提示のコードは「ToggleButton1_Click()」の中にかかれているので、その時しか処理されません。

しかも、
＞具体的にはtargetのオブジェクトがないか～
そのプロシージャではtargetは未定義なので、通常は0とみなされます。
オブジェクトとして定義されてもいないので、target.Countはエラーになると考えられます。

ですので、「D列とE列に3桁もしくは4桁の数字を打った際に～」の処理は、「Worksheet_Change()」等の中に記述すべきものと思われます。
また、「ToggleButton1_Click()」の中で、
＞Application.EnableEvents = False
の処理を行ったりしているようですが、その後はChange Eventが発生しなくなりますので、上記のChangeの処理も動作しなくなります。
（Trueに設定し直せば、イベントは発生するようになります）


※ 実際になさりたいことの内容やタイミングを整理して考えれば、全体をどのように構成しておけば良いのかがわかると思います。