VBAでオプションボタンによってコンボボックスの内容を変える方法

エクセルVBAのユーザーフォームでアンケートシートを作っています。
オプションボタン3つとコンボボックス1つで、和暦西暦とそれに対応する年数を表示したいと思っています。

オプションボタン３つのオブジェクト名はそれぞれ、
showa
heisei
seireki
となっており、コンボボックスは
bd_year
としています。


下記のように作ってみたのですが、うまくいきません。
どなたか、教えていただけませんか。
よろしくお願いします。


Private Sub bd_year_DropButtonClick()
Dim A1 As String, B1 As String, C1 As String
Dim i As Integer

A1 = v
For v = 1 To 64
Next v
B1 = w
For w = 1 To 30
Next w
C1 = x
For x = 1925 To 2020
Next x

If showa.Value = True Then
Me.bd_year.List = A1
End If
If heisei.Value = True Then
Me.bd_year.List = B1
End If
If seireki.Value = True Then
Me.bd_year.List = C1
End If

End Sub

No.1 ベストアンサー 回答者： cj_mover 回答日時： 2015/03/31 16:58

こんにちは。

ご提示のように、bd_year_DropButtonClick イベントを使う
方法でも、出来なくはないのですが、珍しいやり方ですし、扱いが難しくなります。
普通は、オプションボタンのイベントから、コンボボックスのリストを変更します。

コンボボックスの_DropButtonClick イベントの難点は、
ドロップボタンを押した時だけではなくて、
表示中のリストから選択した時にも発生することや、
表示中のリストから再度選択し直したい時にも
ドロップボタンを押すことで余計なイベントが発生し、
不必要にリストの再設定をすることになるからです。
どちらかというと、無駄な処理をする回数の方が、
必要な処理をする回数よりも多くなってしまいます。
無駄な処理を回避することも可能ですが、
不自然な位、冗長な記述になります。
一番のデメリットは、メンテナンスに困った時に、
助けてくれる人が殆どいないであろう程に風変わりだということです。
私としても_DropButtonClick イベントを奨める訳にはいかないので、
オプションボタンの_Change イベントを
代替え案として提示します。
_Click イベントではなく、_Change イベントを奨める理由も、
同様に、無駄な処理を省いて煩雑さを軽減する意図です。
以下の記述を活かす場合は
現在お使いの　Private Sub bd_year_DropButtonClick()
を、同時並行に使用することは出来ませんので、
コメントブロックするか、削除するか、してから、
試すようにしてください。
修正を加える必要があるとすれば、"年"を指す数値の部分だけです。


Private Sub heisei_Change()
Dim i As Long
　　If heisei.Value = False Then Exit Sub
　　With Me.bd_year
　　　　.Clear
　　　　For i = 1 To 30
　　　　　　.AddItem i
　　　　Next i
　　End With
End Sub

Private Sub seireki_Change()
Dim i As Long
　　If seireki.Value = False Then Exit Sub
　　With Me.bd_year
　　　　.Clear
　　　　For i = 1925 To 2020
　　　　　　.AddItem i
　　　　Next i
　　End With
End Sub

Private Sub showa_Change()
Dim i As Long
　　If showa.Value = False Then Exit Sub
　　With Me.bd_year
　　　　.Clear
　　　　For i = 1 To 64
　　　　　　.AddItem i
　　　　Next i
　　End With
End Sub

この回答へのお礼

cj_moverさん

ご回答ありがとうございました。
DropButtonClickの方法は、スタンダードなやり方ではなかったのですね。
勉強になります。

教えていただいた方法で問題なくできました！
ありがとうございました。

お礼日時：2015/04/01 09:32

No.2 回答者： WindFaller 回答日時： 2015/03/31 20:03

こんばんは。

なるべく、分かりやすく、そして元のコードの雰囲気を残したつもりです。
元のコードは、もう少しテクニックが足りなかったところだったように思います。

'//
Private Sub bd_year_DropButtonClick()
　Dim op As OLEObject
　Dim gName As String　'元号
　Dim Ar() As Variant　'配列用変数
　Dim i As Long, j As Long, k As Long, v As Long
　ReDim cmbYear(2, 2)
　cmbYear(0, 0) = "showa": cmbYear(0, 1) = 1: cmbYear(0, 2) = 64
　cmbYear(1, 0) = "heisei": cmbYear(1, 1) = 1: cmbYear(1, 2) = 30
　cmbYear(2, 0) = "seireki": cmbYear(2, 1) = 1925: cmbYear(2, 2) = 2020
　
　For Each op In Me.OLEObjects
　　If TypeName(op.Object) = "OptionButton" Then
　　　If op.Object.Value = True Then
　　　　gName = op.Name
　　　Exit For
　　　End If
　　End If
　Next
　i = Switch(gName = "showa", 0, gName = "heisei", 1, gName = "seireki", 2)
　
　j = cmbYear(i, 2) - cmbYear(i, 1)
　ReDim Ar(0 To j)
　k = 0
　For v = cmbYear(i, 1) To cmbYear(i, 2)
　 Ar(k) = v　　'配列を使う
　 k = k + 1
　Next v
　Me.bd_year.List = Ar()　'そのまま配列を入れる
　
End Sub

この回答へのお礼

WindFallerさん

ご回答ありがとうございました。
WindFallerさんの方法でもうまく動きました。
For Each op In Me.OLEObjects　から始まる文は使ったことがありませんでした。
勉強になりました。

ありがとうございます。

お礼日時：2015/04/01 09:35