Excel VBAです.ComboBoxへのリスト登録
今日後輩から 複数の ComboBox へのリスト記入で,ComboBox の数分だけ記述しているので,すっきり記入する方法がないか尋ねられた.実は,自分も困ってた.自分は諦めてたけど,尋ねられたら気になって,色々考えてこんなのを考えた.配列宣言を,別なモジュールにまとめてグローバル宣言したら,上手く行きました.' --- UserForm 側 ---Option ExplicitOption Base 1'Public data1(3) As String'Public data2(5) As String'ココに,置くとエラーになるので,別モジュールでGLOBAL宣言します.Private Sub UserForm_Initialize()data1(1) = "data1の1番目"data1(2) = "data1の2番目"data1(3) = "data1の3番目"data2(1) = "data2の1番目"data2(2) = "data2の2番目"data2(3) = "data2の3番目"data2(4) = "data2の4番目"data2(5) = "data2の5番目"'data2(x) comboboxNUMBER = 2: stopNUMBER = 5: presetNUMBER = 1 ComboBoxInitializeAndPreset data2(), comboboxNUMBER, stopNUMBER, presetNUMBER'data1(x) comboboxNUMBER = 1: stopNUMBER = 3: presetNUMBER = 3 ComboBoxInitializeAndPreset data2(), comboboxNUMBER, stopNUMBER, presetNUMBEREnd Sub' --- UserForm 側 ココまで ---' --- Module1 側 ---Option ExplicitSub ComboBoxInitializeAndPreset(itemARRAYname() As String, comboboxNUMBER As Integer, stopNUMBER As Integer, presetNUMBER As Integer)Dim i, j As Integer j = 0 For i = 1 To stopNUMBER With UserForm1.Controls("ComboBox" & comboboxNUMBER) .AddItem itemARRAYname(i), j End With j = j + 1 Next UserForm1.Controls("ComboBox" & comboboxNUMBER).Value = itemARRAYname(presetNUMBER)End Sub' --- Module1 側 ココまで ---' --- Module2 側 ---Option ExplicitOption Base 1Global data1(3) As StringGlobal data2(5) As StringGlobal stopNUMBER As IntegerGlobal presetNUMBER As IntegerGlobal comboboxNUMBER As Integer' --- Module2 側 ココまで ---© 6ちゃん!