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

質問

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

この投稿のマルチメディアは削除されているためご覧いただけません。

回答No.6

KenKen_SP · ベストアンサー

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

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

お礼

KenKen_SP様
ご丁寧なご回答ありがとうございます。
今晩にでも試してみようと思います。
私は参考書を片手に意味を考えながらやっておりますので
時間がかかります。
本当に助かります。ありがとうございます。
それにしても同じ様な動きでもやり方がいろいろあるのですね。
勉強になります。

補足

KenKen_SP様
遅くなりましたが、実行してみました。
とてもすごいです！！
自分で時間の引き算も足してみました。
' // ComboBox2 がフォーカスを失うとき実行されるイベント
'
Private Sub ComboBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean)

' フォームを閉じるフラグが立っている場合、即終了
If m_fFormClose Then Exit Sub

Dim s As String
s = Trim$(ComboBox2.Text)
If Len(s) Then
' 時刻以外が入力されたらフォーカス遷移をキャンセルさせる
Cancel = Not IsTime(s)
End If
’自分で足したところ
TextBox1.Text = Format((TimeValue(ComboBox2.Text) - TimeValue(ComboBox1.Text)), "h:mm")

End Sub
私の考えていたような動きになっているようです。
本当に助かりました。またご縁がありましたらよろしくお願いします。

回答No.7

rukuku

私が
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
の違いを試してください。
両方いっぺんに設定すると分かりにくいので、片方ずつ設定してみてください。

お礼

rukuku様
丁寧にご説明頂き本当にありがとうございます。
のちほど試してみようと思います。
こちらも参考書を片手に意味を調べながら実行しておりますが、
なかなか？？です。
がんばります。

補足

rukuku様こんにちは。
AfterUpdateとChangeの違いを感じてみました。なるほでです。
先日の返答が他の方への返答が入ってしまっていました。大変失礼しました。
(1)やはり引き算がうまくできません。

(2)
Private Sub ComboBox3_AfterUpdate()
If IsDate(ComboBox3) Then MsgBox = TimeValue(ComboBox3)
End Sub
これを実行すると「代入式の左辺の関数呼び出しは、バリアント
型またはオブジェクト型の値を返さなければなりません」と
メッセージがでます。
お時間がありましたらよろしくお願いします。

回答No.5

KenKen_SP

レス遅くなりました。#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 の方向で検討してみてください。

回答No.4

rukuku

＞ただ、リストに無い時間（例えば13：15）が手入力できません。
＞入力できるようにする方法はありますか？

コンボボックスの、MatchRequired が Trueになっていませんか？
これがTrueになっていると、リストにない値は入力できません。

時刻として認識できる形式で入力されれば、シリアル値として扱うこともできます。以下のコードを試してみてください。
Private Sub ComboBox3_AfterUpdate()
If IsDate(ComboBox3) Then MsgBox = TimeValue(ComboBox3)
End Sub

お礼

rukuku様、度々ありがとうございます。
＞ただ、リストに無い時間（例えば13：15）が手入力できません。
＞入力できるようにする方法はありますか？

は .Style = fmStyleDropDownList を fmStyleDropDownComboに変えたら入力できるようになりました。
ただ、カーソルは入るのですが、数字がうまく入力できません。
１文字目が変になりその後もうまく入力できません。

引き算の方は
Private Sub ComboBox1_Change()
' 値が変更される度に書式を変更する
On Error Resume Next
ComboBox1.Value = Format$(ComboBox1.Value, "h:mm")
End Sub
で文字列になってしまう（？）ので
ComboBox1.Value = Format$(ComboBox1.Value, "h:mm")
のコードを抜くと計算できます。
初心者に毛が生えた程度の私には難しいようです。
作りたいユーザーフォームはイメージできているのですが
VBAを作ることは難しいです。

回答No.3

rukuku

＞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

回答No.2

KenKen_SP

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

補足

KenKen_SP様　ありがとうございます。
希望通り表示できました。
ただ、リストに無い時間（例えば13：15）が手入力できません。
入力できるようにする方法はありますか？
よろしくお願いします。

回答No.1

rukuku

こんばんは

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

これを使えば、コンボボックスには時刻形式で表示しつつ、計算ではシリアル値を使うことができます。すなわち、左側の列を表示用、右側の列を計算用に使います。（表示用の時刻には「’」をつけておきます。）

補足

rukuku様　ありがとうございます。
エクセル2000です。無事に表示できました。
さらに質問で恐縮ですが、ComboBox2もComboBox1と同じように作り
ListBox1に　ComboBox2　－　ComboBox1　（引き算）
を自動表示したい場合のListBox1の
Private Sub ListBox1_Change()はどのようになりますでしょうか？

もしご存知でしたらよろしくご教授ください。