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

質問

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

日だけを表示する方法をご教授ください

回答No.5

cj_mover · ベストアンサー

こんにちは、＃４です。
ご丁寧なレスをどうも。

えーと、、、
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

お礼

詳しく教えてくださり感謝します。
dpm = Day(DateSerial(Year(dpm), Month(dpm) + 1, 1) - 1)
で、マクロを使ってその月の日数をもとめることができるんですね。
今までマクロを使っていないときは２９日～３１日のセルを関数で
=IF(DAY(A32+1)<>29,"",A32+1)
=IF(DAY(A32+2)<>30,"",A32+2)
=IF(DAY(A32+3)<>31,"",A32+3)
として、無い月はセルに表示しないようにしていました。
なのでコンボボックスで日を表示するときもセルの関数で表示しないというのをそのまま利用していましたが、マクロでその月の日数を数えれるのはいいですね。

ＶＢＡについてはまだまだ初心者なので回答していただいた内容で解読できていないところもあるので、理解できるようにもう少し勉強してみたいと思います。

ありがとうございました。

回答No.4

cj_mover

こんにちは

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プロパティーに
「値」（配列）を直接指定する必要があるようですね。

この混乱のことを問題にされているように受けての回答でした。

一番すっきりさせる方法としては、参照される側のセルの値を
整数型にしておく方法かとも思うのですが、、、

お礼

ご回答ありがとうございます！
おっしゃる通りコンボボックスで無限ループにはまっているようでした。
ComboBox1_Change()のところを、「一度だけで抜ける方法」で解決することができました。
別解１でもうまくいきました。
今のところ別解１を採用させていただこうかと思います。
別解２ですが、５行だとうまくいくのですが、知識不足で、一月分の３１行にするときにどこを変更すればよいか分からず３１行では試せませんでした…
この方法も面白いので試してみたいと思うのですが。
ちなみに、表示される日付は2008/9/1の形から9月1日等の表示に変えることができるのでしょうか？
よければおしえてください。

cj_moverさんの回答でうまく解決できそうです。
ありがとうございました。

このあとさらに付け加えていこうと思っています。
選択したセルに数値が入っている場合は、その数値にテキストボックスの数値を加算するようにしたいと思っています。
また分からないときには新しい質問で質問させてください。
よろしくお願いいたします。

回答No.3

pkh4989

こんにちは。
以下のように変更してみてください。

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

お礼

ご回答ありがとうございます！
回答してくださった内容を使用してみましたが残念ながら最初と同じ結果になってしまいました。
コンボボックスのマクロが何度も働いているようです。
私の理解不足かもしれませんが今回この方法はうまくいきませんでした…。
しかし、貴重な回答をありがとうございました。

回答No.2

imogasi

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

お礼

回答してくださりありがとうございます！
＞日付のフォーマットを設定するプロパティが見当たらない
私も正攻法の仕方があるのではないかと調べてみたのですが見当たりませんでした。
何も設定しないとシリアル値が出るので、何とかしたいと思うのですが。
なぜ、日付にこだわるかというと月ごとに日付が増減して表示されるようにしているからです。

質問ではマクロの一部を簡略化して載せたので、まだ未完成な全文を載せておきます。

Private Sub UserForm_Initialize()
Dim ws As Worksheet
With ComboBox1
.ColumnCount = -1
.ColumnWidths = -1
.RowSource = "sheet1!A5:A35" '日付のセルを選択
End With

Set ws = Sheets("Sheet1")
With Me.ComboBox2
.Column = ws.Range("S3", ws.Cells(3, Columns.Count).End(xlToLeft)).Value '横列の項目を選択
End With
Set ws = Nothing
End Sub

Private Sub CommandButton1_Click()
Dim ctrl As Control, tst1 As String, txt2 As String
Dim ws As Worksheet, r As Long, c As Long
Set ws = Sheets("sheet1")
For Each ctrl In Me.Controls
Select Case ctrl.Name
Case "ComboBox1", "ComboBox2", "TextBox1"
If Me.Controls(ctrl.Name).Value = "" Then
txt1 = txt1 & ctrl.Name & vbLf
Else
txt2 = txt2 & Me.Controls(ctrl.Name).Value & vbLf
End If
End Select
Next
If Len(txt1) > 0 Then
MsgBox "以下の値を入力してください" & vbLf & txt1, vbExclamation
Exit Sub
Else
ret = MsgBox("以下の値を入力します" & vbLf & txt2, vbOKCancel)
If ret <> vbOK Then Exit Sub
r = Me.ComboBox1.ListIndex + 5
c = Me.ComboBox2.ListIndex + 19
ws.Cells(r, c) = Me.TextBox1.Text 'テキストボックスの内容を選択したセルへ入れる
End If
Set ws = Nothing

End Sub

Private Sub ComboBox1_Change()
ComboBox1 = Format(ComboBox1, "d") 'ココが問題の部分です
End Sub

問題の部分を "m月d日"
とし
日付のセルの表示形式を○月○日の形にするとテキストボックスの値を入力するセルをきちんと選択できます。
しかし、セルの表示形式を　d とするとうまくいかないので、コンボボックスの表示形式も　d の形にそろえてみようとしています。

回答No.1

xls88

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

お礼

早速のご回答ありがとうございます！
この場合「○○日」のように表示されますよね？
2008/10/1であれば「1日」と。
この形はうまくいきました。

今回はできれば数字だけ表示したいのですが。
「１」のようにしたいのです。
「ComboBox1.Value = Format(ComboBox1.Value, "d")」とすると、マクロが変な動きをする(選択した日＋１３で表示する)ので、解決策か別の方法を教えていただけないでしょうか。

よろしくお願いします。