シートへの暗記と時間表記

Question

前提・実現したいこと

暗記・出力についてです。
現在暗記が上手くいっていません。
現状、画像のように時間はシート上でセルが空の場合「0：00：00」表示になっており、実働時間・備考は「0」となっています。
シートのセルの書式設定は時刻は全て「文字列」、実働時間は時間計算がしたいので「時刻」備考は「標準」です。
この場合でのFormat関数の記述の仕方もよく解らず、解決策があれば教えて頂きたいです・・・。

【解決したいこと】
・「0：00：00」表記ではなく、24時間制の「00：00」表記にしたい
・空白のテキストボックスに数字を入力、或いは数字を書き換えて更新押しても、セルへ保存されない
・備考は入力がない場合のデフォルトは空白(0はいらない)

発生している問題・エラーメッセージ

エラーメッセージ

該当のソースコード

'標準フォーム

Option Explicit

Type attendance
    starttime As Date          '始業時刻
    endtime As Date          '終業時刻
    breakstart As Date        '休憩時間のはじめ
    breakend As Date        '休憩時間のおわり
    worktime As Single      '実働時間
    note As Single            '備考
End Type


'以下ユーザーフォーム

Private monthly() As attendance   '構造体の配列を宣言

Private Sub CommandButton1_Click()   '更新ボタン
  Call AttendanceSave
  'Call SetAttendance      'ボタン押すたびに「0：00：00」と「0」に上書きされてしまうため、今は無効状態
End Sub

Public Sub AttendanceSave()    '勤怠保存
    ReDim monthly(1 To 31)

    Dim i As Long
    For i = 1 To 31
        monthly(i).starttime = Cells(i + 1, 3)
        monthly(i).endtime = Cells(i + 1, 4)
        monthly(i).breakstart = Cells(i + 1, 5)
        monthly(i).breakend = Cells(i + 1, 6)
       ' monthly(i).worktime = Cells(i + 1, 7)　　　　'自動計算されるようにしたいため入力は無し
        monthly(i).note = Cells(i + 1, 8)
    Next i

End Sub

Public Sub SetAttendance()    '勤怠出力

    Dim i As Long
   Sheets(cmbMonth.Value).Activate
    For i = LBound(monthly) To UBound(monthly)
       Me.Controls("txtStarttime" & i).Value = monthly(i).starttime
       Me.Controls("txtEndtime" & i).Value = monthly(i).endtime
       Me.Controls("txtBreakstart" & i).Value = monthly(i).breakstart
       Me.Controls("txtBreakend" & i).Value = monthly(i).breakend
       Me.Controls("txtWorktime" & i).Value = monthly(i).worktime
       Me.Controls("txtNote" & i).Value = monthly(i).note
    Next i
 
End Sub


Private Sub UserForm_Initialize()

Dim i As Integer

    '当年、来年の西暦をリストに設定
    For i = 0 To 1
        cmbYear.AddItem Year(Date) + i
    Next
    cmbYear.Value = Year(Date) '当年を初期値に


    For i = 1 To 12 'シートは1から12まで
        cmbMonth.AddItem i
    Next
    cmbMonth.Value = Month(Date) '当月を初期値に
    '初期値を設定することで cmbMonth_Change が呼び出される

    'フォームを画面センターに表示
    Me.StartUpPosition = 2

 
    '当年を初期設定←とりあえず先頭
    cmbYear.ListIndex = 0
    
End Sub

Private Sub cmbYear_Change()　　　　　'年選択プルダウン
    SetWeekLabel
    'SetData
End Sub

Private Sub cmbMonth_Change()　　　　　　'月選択プルダウン

 Debug.Print cmbMonth.Value
    Worksheets(cmbMonth.Value).Select '先に月シートを選択してから
    '曜日設定やテキストへの転記をする

    SetWeekLabel '曜日設定
    Call AttendanceSave '選択したシートデータを構造体配列へ
    Call SetAttendance '構造体配列からテキストへ
    
End Sub

試したこと

ここに問題に対して試したことを記載してください。

補足情報（FW/ツールのバージョンなど）

・構造体を使用してデータを格納しています
・正規表現を使って24時間制にしています。朝の8時や深夜2時などは「08」、「02」です。
・備考はシート側で「12345」などの数字を入力した場合、出力されます。
「テスト」などの文字はエラーになってしまいます。

Answer 1

・時間表記
Format関数はエクセルの書式設定がそのまま使えます。書式設定のユーザー定義を覗くと実用的な設定例がたくさん出てくるので、わたしはFormat関数を使う際、たびたびそこを参照しています。
今回であればFormat(monthly(i).worktime,"h:mm")みたいな感じです。

・セルへの保存
そもそもセルへの保存をしている処理が見当たりませんが……？
難しい処理ではないので、素直にシート→ユーザーフォームの逆順で処理すればいいと思います。

・備考のデフォルトを空白にする
monthly.noteが数値型であるSingle型で宣言されているので、空白は0として扱われます。
補足にある「文字列を入力するとエラーになる」も同じ原因です。Single型に文字列は入れることができないためにエラーになります。
備考をどのように扱うかによっても多少変わりますが、monthly.noteはString型かVariant型で宣言しておけばいいのではないでしょうか。

Comments

[milk1218]

ありがとうございます・・！Format(monthly(i).starttime, "hh:mm")(他も同様)にしたら出来ました！備考に関しても文字表記できるようになりました・・！
実働時間はデータ型をVariant やDateと色々試したのですが「00：00」としか表示されず、これはセル上で=IF(C2>D2,D2+1-C2-(F2-E2),D2-C2-(F2-E2))の計算をしているのが原因でしょうか・・・？

保存に関してなのですが、Public Sub AttendanceSave() の部分がそうですね・・。

[Usirow]

>実働時間
AttendanceSaveでは実働時間に該当する行がコメントアウトされていますが、これは外しているでしょうか？
コメントアウトしたままだと変数のデフォルト値が入るので、テキストボックスには空白か０が入ると思います。
また予期せぬ挙動を防ぐため、セルの値を取得する際は、Cells().Valueという風にValueを明示した方が良いと思います。

>保存
コードを読む限り、AttendanceSaveは「シート→構造体」の転記のように見えます。
続いてSetAttendanceが「構造体→テキストボックス」となっているので、二つ合わせて「シート→構造体→テキストボックス」という風にデータの転記が行われていると解釈しています。

ですので、シートへの保存を行いたい場合は、この逆に「テキストボックス→構造体→シート」という方向にデータを転記していく処理が必要なはずです。

[milk1218]

ありがとうございます・・・！テキストボックスで弄るこ、セル上の計算を出力するからと勘違いしてコメントアウトしていました・・。コメントアウト消したら直りました！

SetAttendanceの出力とは逆の処理ということですね。試しに
monthly(i).starttime = Cells(i + 1, 3).Valueの部分を
Cells(i + 1, 3).Value = Me.Controls("txtStarttime" & i).Value
でやってみたら数字はセルに入ったのですが、月を切り替えたりユーザーフォームを閉じると
「00：00」になるのは、構造体に格納出来ていないということでしょうか・・？

[Usirow]

その処理の仕方ですと構造体に入れる過程をすっとばしているため、構造体に格納できていないのは当然かと思います。

0になってしまうのがユーザーフォームなのかシートなのかわかりませんが、cmbMonth_ChangeとCommandButton1_Clickとで同じプロシージャを呼んでいることが原因ではあると思います。
これらは真逆の処理をするものだと思うので、それぞれ違うプロシージャを呼びだしているか、プロシージャ内で分岐していないとおかしいのではないかと考えています。

[milk1218]

ご丁寧にご説明ありがとうございます・・・！

cmbMonth_ChangeとCommandButton1_Clickの部分をもう少し調整しようと思います！
ありがとうございました・・・！！