EXCEL　VBAのコンボボックスで日だけを表示する方法

Question

EXCELのVBAを使って経費の打ち込みをするマクロを作っています
その中で、コンボボックスで日付を選択するようにしました
コンボボックスに表示する日付はセルから「RowSource」で取り出しました
セルの日付の入力形式は「2008/10/1」です
そのセルの書式設定で表示形式を「ユーザー定義で d (日だけ表示)」としています
なので、コンボボックスで選択して表示する際も日だけを表示したいのですがうまくいきません
ComboBox1 = Format(ComboBox1, "d")
とすると、コンボボックス内で数字がちらちらしてランダムに数字が表示されてしまいます
ComboBox1 = Format(ComboBox1, "m月d日")
この形だと、きちんと「10月1日」の様に表示されます

Private Sub UserForm_Initialize()

With ComboBox1
.RowSource = "sheet1!A1:A5"　'日付のセル

End With
End Sub

Private Sub ComboBox1_Change()
ComboBox1 = Format(ComboBox1, "m月d日")
End Sub

日だけを表示する方法をご教授ください

cj_mover · Accepted Answer

こんにちは、＃４です。
ご丁寧なレスをどうも。

えーと、、、
ComboBox から シリアル値を返す必要はないようですね。

〉別解２ですが、５行だとうまくいくのですが、知識不足で、一月分の３１行にするときにどこを変更すればよいか分からず３１行では試せませんでした…
〉この方法も面白いので試してみたいと思うのですが。
〉ちなみに、表示される日付は2008/9/1の形から9月1日等の表示に変えることができるのでしょうか？

"一月分の３１行"ということは、
日付型の値で、「日にちのみ」が表示されたセルが、
月の初め（１日）から末日まで、連続（欠落なく）して
A5:A35（または、A5:A32、A5:A34）の範囲にある、
ということですよね？

◆別解２、改、
ComboBox1　のリストを２列にして、
選択するリストの表示は「m 月 d 日」、
選択後の.Value（.Text）は「日にちのみ」にする
（※シリアル値が必要な場合は、合成することになります）
（一応、締め日がズレても[26日から翌25日とか]対応する筈です）

Private Sub UserForm_Initialize()
    Dim vA
    Dim i As Integer
    Dim dpm

' ' 当月の日数を求める
    dpm = Worksheets("Sheet1").Range("A5").Value
    dpm = Day(DateSerial(Year(dpm), Month(dpm) + 1, 1) - 1)

' ' A5から下へ当月の日数分の範囲の値をバリアント型の変数vAに格納（内部的にDate型）
　vA = Worksheets("Sheet1").Range("A5:A" & dpm + 4).Value

' ' vAを一括で　String型の「m 月 d 日」に変換（ワークシート関数のTEXTを使用）
　vA = Application.Text(vA, "m"" 月 ""d"" 日""")

    ReDim Preserve vA(1 To dpm, 1 To 2)
    For i = 1 To dpm
　vA(i, 2) = Day(vA(i, 1))
' ' vA(i, 2) = i　' でいいのかも？　
    Next i

ComboBox1.List = vA
ComboBox1.TextColumn = 2

End Sub

一応、お尋ねの答えとしては、上のようになります。
（A列に誤（未）入力がある場合をケアしていません。）

１ヶ月分の日付を文字列にしたもの（「日にちのみ」と「m 月 d 日」）をリストにするのなら、
セル範囲にある日付を追いかけなくてもいいような気もしますね(＾＾;)。

◆別解２、改、改、
わざわざ書くほどでもありませんけど、、、
（こちらは、１日から末日、専用）

Private Sub UserForm_Initialize()
    Dim dpm
    Dim m As Long
    Dim vA
    Dim i As Integer
dpm = Worksheets("Sheet1").Range("A5").Value
m = Month(dpm)
dpm = Day(DateSerial(Year(dpm), m + 1, 1) - 1)
    ReDim vA(1 To dpm, 1 To 2)
    For i = 1 To dpm
vA(i, 1) = m & " 月 " & i & " 日 "
vA(i, 2) = i
    Next i
ComboBox1.List = vA
ComboBox1.TextColumn = 2
End Sub


それでは、また

参考URL：http://www.clayhouse.jp/array/array.htm

xls88 · Answer

下記のようにすればいいようです。
ComboBox1.Value = Format(ComboBox1.Value, "d""日""")

imogasi · Answer

質問者のやり方に現状のエクセルVBAでは無理があるように思った。
（１）セルsheet1!A1:A5の書式を、エクセルのシートの機能の書式で「ｄ」としておくと、コンボのドロップダウンのアイテムの表示も日数字にの１，２，３・・になること。
（２）しかしアイテムの１つを選択したとき、ボックスには、何もしないと日付シリアル値の数字が表示される。例３９ｘｘｘ
（３）シートのセルに、選択したアイテムを表示するときは、コンボボックスの値を数値化して= Val(ComboBox1)、そのセルの書式を日付の表示形式にしておくと2008/9/3のように表示され、セルの値も日付シリアル値になって、後には日付としてそのまま使える。
日付シリアル値は数値であり、コンボやテキストボックスは文字列を返すのでVALはやむを得ないと思う。
ーー
問題はコンボで選択したときコンボに表示されるものを、日数字だけにする仕組みは無いのではないかな。
コンボに、エクセルシートでは出来る、日付のフォーマットを設定するプロパティが見当たらないから。（注）
もしFormat関数でコンボボックスの値を、日だけ表示すると、連結するセルに値（日付）をセットするときに、何年＋何月を添えて、日付に再構成しなくてはならないのではないか。
それならコンボは、日数字だけ選択させるほうがすっきりする。
良く年、月、日を独立したコンボて選択させている仕組みをWEBなどで見かける。
ーー
それと_Change()イベントは注意しないと、コンボにプログラムで値をセットすると、_Change()イベントが起こり、ぐるぐる回りになって
おかしくなる場合があるので使用は慎重に。
それ対しては、Application.EnableEvents = Falseが使える場合がある。
ーー
上記（注）の部分は１００％は自信はないが、上記で私が言っていることは判ってもらえるでしょうか。

pkh4989 · Answer

こんにちは。
以下のように変更してみてください。

Dim eFlg　　　 As Boolean

Private Sub UserForm_Initialize()
　　With ComboBox1
　　　　.RowSource = "sheet1!A1:A5"　　　　　　　　 '日付のセル
　　End With
End Sub

Private Sub ComboBox1_Change()
If eFlg = False Then
　　　　eFlg = True
　　　　ComboBox1 = Format(ComboBox1, "d")　　　　　'ここでイベントが発生するので
　　　　　　　　　　　　　　　　　　　　　　　　　　'１回のみ処理出来るように制御が必要
　　　　eFlg = False
　　End If
Application.EnableEvents = True
End Sub
'又は
Private Sub ComboBox1_Click()
　　ComboBox1 = Format(ComboBox1, "d")
End Sub

cj_mover · Answer

こんにちは

 ComboBoxの.Valueを変更すれば、
再び_Changeイベントが発生します。
原理的には無限ループ
（無限ネスト？というか循環参照に近い状態）
が起きています。


□現状を活かすとすれば、

◆ ComboBox1_Change　イベントの再帰的な呼び出し
を（一度だけで）抜ける方法として、

Private Sub ComboBox1_Change()
Static flg As Boolean
　If Not flg Then
　　flg = True
　　　With ComboBox1
　　　.Value = Format(.Value, "d")
　　　End With
　　flg = False
　End If
End Sub

ただ、個人的には、こういう方法は選ばないと思います。
なんとなく、手数の割りに安心出来ないような気が、、、
蛇足ですが、本件に Application.EnableEvents は無関係です。


他、
□UserForm　Load後の　参照元のセルの値変更を無視できるなら、
（或いは、ComboBox1　の　.List　をUpdateするプログラムを別に用意するとして）
_Change　イベントに依らない方法として、.RowSourceは指定せずに、

◆別解１、
ComboBox1　のリストが最初から「日にちのみ」でも構わないなら、

Private Sub UserForm_Initialize()
    Dim vA
vA = Worksheets("Sheet1").Range("A1:A5").Value
vA = Application.Text(vA, "d")
ComboBox1.List = vA
End Sub

・　バリアント型の変数vAに、Sheet1!A1:A5　の値を配列として格納し、
・　vAを一括で　String型の「日にちのみ」に変換し（ワークシート関数のTEXTを使用）、
・　ComboBox1　の　.List　プロパティーに、配列としての　vA　を代入。

◆別解２、
ComboBox1　のリストを２列にして、選択するリストの表示は通常の日付、
.Valueの戻り値は「日にちのみ」にする方法として、
リストの１列目は年月日、２列目は「日にちのみ」にしておく。

Private Sub UserForm_Initialize()
    Dim vA
    Dim i As Integer
vA = Worksheets("Sheet1").Range("A1:A5").Value
    ReDim Preserve vA(1 To 5, 1 To 2)
    For i = 1 To 5
vA(i, 2) = Day(vA(i, 1))
    Next
ComboBox1.List = vA
ComboBox1.TextColumn = 2
End Sub

・　バリアント型の変数vAに、Sheet1!A1:A5　の値を配列として格納し、
・　vAの（値を変えずに）サイズを２列に変更して、
・　vAの２列めに「日にちのみ」を格納し、
・　ComboBox1　の　.List　プロパティーに、配列としての　vA　を代入。
・　.List　の　２列めが　.Valueになるように指定。

という方法もあります。
この（２列にした）場合は、_Change　イベント内などで、
ret = ComboBox1.List(ComboBox1.ListIndex, 0) * 1
とすれば、シリアル値に戻すこともできます（この場合はVal関数は難あり）。


□実行時の手数を減らす意味では、
UserForm_Initialize　は　ご提示（.RowSource）のままで、

◆ComboBox（または　ListBox）に　「日にちのみ」のリストを表示させて、
　_Changeイベントから
　表示用に隣接させた　TestBox（または　Label）の　.Value（.Caption）を
　操作する手も考えられるでしょうか。（同時にListBoxを非表示にするなど）
（Controlが増えることに若干抵抗はあるけれど）



.RowSource
を指定する場合は、参照される側のセル（日付型）の
「表示」（.Listへ）
と
「値」　（,Valueへ）
のそれぞれが反映されるようで、
その点の混乱を解消するには、.Listプロパティーに
「値」（配列）を直接指定する必要があるようですね。

この混乱のことを問題にされているように受けての回答でした。

一番すっきりさせる方法としては、参照される側のセルの値を
整数型にしておく方法かとも思うのですが、、、

EXCEL VBAのコンボボックスで日だけを表示する方法

下記のようにすればいいようです。

質問者のやり方に現状のエクセルVBAでは無理があるように思った。

こんにちは。

こんにちは

こんにちは、＃４です。

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

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

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

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

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

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

EXCEL　VBAのコンボボックスで日だけを表示する方法