エクセルVBA セルの時間値をリストボックスへ

Question

時間値の表示形式がおかしくなってしまう現象を解決したいです。

【セル】から時間値を【リストボックス】に表示すると以下のようになっています。

①24時間未満の場合　　※正しく表示できている
10:34:00（セルの値）　→　10:34:00　（リストボックスの値）

②24時間以上の場合　　※誤った表示になっている
68:36:00（セルの値）　→　1900/01/01 20:36:00　（リストボックスの値）

②のようにリストボックスに表示されてしまうのを解消したいです。
どのようなコードを追記すれば良いのでしょうか。
ご教示ください。お願いします。

実際のコードは下記の通りです。
cells(1,9)に時間値が入力され、それをListbox2へAddItemしています。

Worksheets("データ").Cells(1, 9).NumberFormatLocal = "[h]:mm:ss"

ListBox2.List = Array()

Dim j As Date

j = Worksheets("データ").Cells(1, 9).Value


ListBox2.AddItem j

コード

Answer 1

表示そのままをリストにいれたいのならTextが使えます。

Dim s As String

s = Worksheets("データ").Cells(1, 9).Text

ListBox2.AddItem s

Comments

[riko]

ご回答ありがとうございます。
リストボックスに入った後の時間値は、その後も時間値として、
別の時間値と足し算・引き算等していくのですが、Text型にしても計算には支障ないのでしょうか？

[radames1000]

Text型そのままでは計算できませんので、結局別途計算が必要になりますね。

[riko]

お返事ありがとうございます。
関数を定義する等で解決してみます。
いつも簡潔で正しい回答をして頂きありがとうございます。

Answer 2

Option Explicit

Private Sub UserForm_Initialize()
    With Me.ListBox1
        .RowSource = ActiveSheet.Range("A1:A10").Address
    End With
End Sub

Private Sub ListBox1_Click()
    MsgBox Me.ListBox1.Value
    MsgBox Me.ListBox1.Text
End Sub

RowSourceプロパティでリストをセットすると値も使いまわしが効きそうです。

Comments

[riko]

ご回答ありがとうございます。
参考にさせて頂きます！

Answer 3

セルに表示されいるように表示したいなら、

Worksheets("データ").Cells(1, 9).NumberFormatLocal = "[h]:mm:ss"

ListBox2.List = Array()

Dim j As String

j = Worksheets("データ").Cells(1, 9).Text


ListBox2.AddItem j

[h]:mm:ss という書式はエクセル独自のもので、エクセルのセル上でしか使えません。
VBAの Date型 ではそのような表示はできないです。

Textだと表示されている文字列として取得できますので、それをリストに追加すればそのまま表示されます。

Comments

[riko]

ご回答ありがとうございます。
リストボックスに入った後の時間値は、その後も時間値として、
別の時間値と足し算・引き算等していくのですが、Text型にしても計算には支障ないのでしょうか？

[hatena19]

VBA上なら変換する関数を自作しておくいいでしょう。
68:36:00 → 1900/01/01 20:36:00 というように。

[riko]

承知しました。そのようにチャレンジしてみます。
本日は私の質問に２つも回答して頂きありがとうございます。
助けてもらう一方で大変恐縮です。ほんとにありがとうございます！！

Answer 4

Date型を特に指定なしでString型に代入するとその形式になってしまいます。
そのためFormatを使うなどして形式を変更することになりますが、
セルの表示形式の[h]に対応した変換方法が残念ながらFormatにはないので、
時間部分のみ手動で計算することになります。

ListBox2.AddItem Int(j * 24) & ":" & Format(j, "nn:ss") 

Comments

[riko]

ご回答ありがとうございます。
非常に勉強になりました。
助かりました！有り難うございます！！

Answer 5

[h]をhにしてみてください。

Worksheets("データ").Cells(1, 9).NumberFormatLocal = "h:mm:ss"

裏側でおこっていること

セルに 10:34:00 と入力したときには、内部的には
1900/01/00 10:34:00 で保管されています。
試しに NumberFormatLocal = "yyyy/mm/dd hh:mm:ss"にしてみるとわかります。

そうすると、68:36:00は
1900/01/02 20:36:00 という内部的な値になります。
2日(24×2)+20:36:00 で計算あってますね。

[h]の角かっこは24時間以上の表記を認めるという指示です。
NumberFormatLocal = "[h]:mm:ss" にしたときに
なぜ、68:46:00にならないのかは、Microsoftに聞いてみるしかないと思います。

Comments

[riko]

ご回答ありがとうございます。
２４時間以上のデータも取り扱うので
68時間という値は　68時間としてリストボックスに追加したかったの［ｈ］という形にしておりました。