excelVBA コンボボックスに時刻で表示したい

Question

いつもお世話になります。お知恵を貸してください。
ユーザーフォームにコンボボックスがあります。
リストの中身はプロパティウィンドウのRowSourceでSheetにある時間表を指定しています。
そのリストは7：00,7:30,8:00…と時刻をh:mmで表示してあります。
コンボボックスには7:00と表示されているのですが、実際選ぶとシリアル値で表示されてしまいます。
[終了時間]－[開始時間]の計算もしたいので、シリアル値は必要だと思いますが、表示はｈ：ｍｍで表示する方法を教えてください。
よろしくお願いします。

KenKen_SP · Accepted Answer

ついでに、入力チェックや入力補助機能なども含めたサンプル。

1.　フォームに ComboBox1、CommandButton1 を配置する
2.　Sheet1のA1:A20に RowSource に設定するデータを入力する
3.　以下のソースをフォームにペーストにテスト

Option Explicit

' // フォームを閉じる場合のフラグ
Private m_fFormClose As Boolean

' // Userform が初期化される時実行されるイベント
'
Private Sub UserForm_Initialize()
　　
　　With Me.ComboBox1
　　　　.List = Application.Text(Range("Sheet1!A1:A20").Value, "h:mm")
　　　　' IME を無効化し全角文字などが入力されないようにする
　　　　.IMEMode = fmIMEModeDisable
　　　　' 最大５文字までしか入力できなくする
　　　　.MaxLength = 5
　　End With

End Sub

' // Userform を閉じる時実行されるイベント
'
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
　　m_fFormClose = True
End Sub

' // ComboBox がフォーカスを失うとき実行されるイベント
'
Private Sub ComboBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
　　
　　' フォームを閉じるフラグが立っている場合、即終了
　　If m_fFormClose Then Exit Sub
　　
　　Dim s As String
　　s = Trim$(ComboBox1.Text)
　　If Len(s) Then
　　　　' 時刻以外が入力されたらフォーカス遷移をキャンセルさせる
　　　　Cancel = Not IsTime(s)
　　End If

End Sub

' // ComboBox キーが押されたとき実行されるイベント
' // ANSI コードまたはシフト JIS コードに対応する文字キーで発生
'
Private Sub ComboBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
　　
　　' 時刻入力のため以外のキー入力以外は無効化する
　　If Not Chr(KeyAscii) Like "[0-9:]" Then
　　　　KeyAscii = 0
　　End If
　　
End Sub

' // ComboBox キーが押されれ、キーを離したとき実行されるイベント
'
Private Sub ComboBox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
　　
　　' 以下は ComboBox のマッチング機能を利用し、「：」記号の入力を
　　' スキップさせる入力補助を行う
　　
　　If KeyCode <> vbKeyBack Then ' BackSpace キーは除外
　　　　If Me.ComboBox1.SelText Like ":*" Then
　　　　　　Me.ComboBox1.SelStart = Me.ComboBox1.SelStart + 1
　　　　　　Me.ComboBox1.SelLength = 2
　　　　End If
　　End If
　　
End Sub

' // CommandButton をクリックしたとき実行されるイベント
'
Private Sub CommandButton1_Click()
　　' C1 セルへ記入
　　With Cells(1, "C")
　　　　.NumberFormat = "h:mm"
　　　　.Value = CDate(Me.ComboBox1.Value)
　　End With
End Sub

' // 内部チェック関数-時刻書式
'
Private Function IsTime(ByVal s As String) As Boolean
　　
　　If IsDate(s) And (s Like "#:##" Or s Like "##:##") Then
　　　　IsTime = True
　　Else
　　　　MsgBox "時刻を入力して下さい", vbCritical
　　End If

End Function

rukuku · Answer

私が
Private Sub ComboBox3_Change()
ではなく、
Private Sub ComboBox3_AfterUpdate()
を使用した理由を説明していませんでした。

Changeでは、１字入力するごとにプログラムが起動してしまいます。
そのためにプログラムが思うように動いていないのだと思います。

一通り入力を終えて、確定した時点で始めてプログラムを実行するために「AfterUpdate」を使用しました。


Private Sub ComboBox1_AfterUpdate()
MsgBox "Update"
End Sub
と
Private Sub ComboBox1_Change()
MsgBox "Change"
End Sub
の違いを試してください。
両方いっぺんに設定すると分かりにくいので、片方ずつ設定してみてください。

KenKen_SP · Answer

レス遅くなりました。#2 はボツの方向で。

RowSource を使うとこういう部分が面倒ですね・・ComboBox にアイテムを
セットするには、RowSource の他、

　　・List プロパティー
　　・AddItem メソッド

といった方法もあります。次は、List プロパティーを使った例です。

Private Sub UserForm_Initialize()

　　ComboBox1.List = Application.Text(Range("Sheet1!A1:A20").Value, "h:mm")

End Sub

これなら、わざわざ Change イベントで書式化する必要はありません。
なぜなら最初から書式化した「文字列」をセットしているのですから。

そもそも、TextBox や ComboBox、ListBox などのコントロールの値は
文字列です。それを四則演算したい場合は、数値なりシリアル値として
変換してやらねばなりません。例えば、補足にある

　　「ComboBox2　－　ComboBox1」

という計算を時刻の引き算をしたいなら、

　　TimeValue(ComboBox2.Text) - TimeValue(ComboBox1.Text)

とします。

私は、RowSource プロパティーは全く使わないので詳しくありませんが、
どうもセル参照のようですね。。データのコピーを ComboBox にセット
するのではなく、セルへリンクが張られてるような感じかと。
数値やシリアル値の場合、面倒そうなので 上記 List プロパティーか、
AddItem の方向で検討してみてください。

rukuku · Answer

＞ただ、リストに無い時間（例えば13：15）が手入力できません。
＞入力できるようにする方法はありますか？

コンボボックスの、MatchRequired が Trueになっていませんか？
これがTrueになっていると、リストにない値は入力できません。

時刻として認識できる形式で入力されれば、シリアル値として扱うこともできます。以下のコードを試してみてください。
Private Sub ComboBox3_AfterUpdate()
If IsDate(ComboBox3) Then MsgBox = TimeValue(ComboBox3)
End Sub

rukuku · Answer

＞ComboBox2もComboBox1と同じように作り
＞ListBox1に　ComboBox2　－　ComboBox1　（引き算）
＞を自動表示したい場合のListBox1の
＞Private Sub ListBox1_Change()はどのようになりますでしょうか？

リストボックスのイベントからは、コンボボックスのchangeは拾えません。コンボボックスのchangeを使います。

Private Sub ComboBox1_Change()
ListBox1.Value = ComboBox2 - ComboBox1
End Sub

Private Sub ComboBox2_Change()
ListBox1.Value = ComboBox2 - ComboBox1
End Sub

KenKen_SP · Answer

Change イベントで書式化するとか。以下、新規でのスモールサンプル。

1.　フォームに ComboBox1、CommandButton1 を配置する
2.　Sheet1のA1:A20に RowSource に設定するデータを入力する
3.　以下のソースをフォームにペーストにテスト

Private Sub UserForm_Initialize()
　　With Me.ComboBox1
　　　　.RowSource = "Sheet1!A1:A19"
　　　　.Style = fmStyleDropDownList
　　End With
End Sub

Private Sub ComboBox1_Change()
　　' 値が変更される度に書式を変更する
　　On Error Resume Next
　　Me.ComboBox1.Value = Format$(Me.ComboBox1.Value, "h:mm")
End Sub

Private Sub CommandButton1_Click()
　　' C1 セルへ記入
　　With Cells(1, "C")
　　　　.NumberFormat = "h:mm"
　　　　.Value = Me.ComboBox1.Value
　　End With
End Sub

rukuku · Answer

こんばんは

Excelのバージョンを教えてください。

手元にある2000で探してみた結果、「表示形式」のようなプロパティはなさそうです。後のバージョンで追加されたかどうかは確認していません。

しかし、バージョン2000でも、「表示」と「実際の計算に使う値」を分けることができます。

以下のことを試してみてください
1.　Sheet1に以下のように入力
　　　　　A　　　　　B
　　1　　零時　　　0:00
　　2　　六時　　　6:00
　　3　　十二時　　12:00
　　4　　十八時　　18:00

2.コンボボックスのプロパティを以下のように設定
　　RowSource → Sheet1!A1:B7
　　BoundColumn → 2

3.コンボボックスのあるフォームのコードに下記を記述
　　Private Sub ComboBox1_Change()
　　  MsgBox ComboBox1.Value
　　End Sub

これを使えば、コンボボックスには時刻形式で表示しつつ、計算ではシリアル値を使うことができます。すなわち、左側の列を表示用、右側の列を計算用に使います。（表示用の時刻には「’」をつけておきます。）

excelVBA コンボボックスに時刻で表示したい

ついでに、入力チェックや入力補助機能なども含めたサンプル。

この回答への補足

私が

この回答への補足

レス遅くなりました。

＞ただ、リストに無い時間（例えば13：15）が手入力できません。

＞ComboBox2もComboBox1と同じように作り

Change イベントで書式化するとか。

この回答への補足

こんばんは

この回答への補足

似たような質問が見つかりました

このQ&Aを見た人はこんなQ&Aも見ています

関連するカテゴリからQ&Aを探す

このQ&Aを見た人がよく見るQ&A

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング