VBA 複数のコンボボックスに同一の処理を行なう

Question

お世話になっております。ユーザーフォームのコンボボックス(cmbExp1からcmbExp9)を9個ほど設置してDatabaseのI列のリストの最終行（ｃMaxRowとしました）までの同一のリスト一覧をコンボボックスに表示させたいと思い、下記のコードを記載しましたが、Forで指定された変数は既に使われていますというエラーが出てしまいました。

原因としては他のコンボボックスの設定でFor文を設定してしまっていることや9個のコンボボックスのループの書き方が繰り返しが多く、構文的にエラーが出やすい状態になっているかとは思いますが、エラーの出にくい書き方を教えていただけましたら有難いです。

Private Sub UserForm_Initialize()

'gain the last row
     Dim cMaxRow As Integer
     cMaxRow = Cells(Rows.Count, 1).End(xlUp).Row
     
     'setting initial No.
     If cMaxRow > 10 Then
            txtNoDay.Value = Range("A" & cMaxRow).Value + 1
        End If
    
     'setting initial date
     txtDay.Value = Day(Date)
     txtMonth.Value = Month(Date)
     txtYear.Value = Year(Date)
     
     
     'comboBox add name of Maker
     Dim i As Integer
     For i = 3 To 5
         cmbMaker.AddItem Worksheets("Database").Range("A" & i).Value
    
    
    'comboBox add Category of descriptions for each expense
    
    For i = 3 To cMaxRow
         cmbExp1.AddItem Worksheets("Database").Range("I" & i).Value
        
      
    For i = 3 To cMaxRow
         cmbExp2.AddItem Worksheets("Database").Range("I" & i).Value
         
    
    For i = 3 To cMaxRow
         cmbExp3.AddItem Worksheets("Database").Range("I" & i).Value
        
    
    For i = 3 To cMaxRow
         cmbExp4.AddItem Worksheets("Database").Range("I" & i).Value
             
    
    For i = 3 To cMaxRow
         cmbExp5.AddItem Worksheets("Database").Range("I" & i).Value
    
    
    For i = 3 To cMaxRow
         cmbExp6.AddItem Worksheets("Database").Range("I" & i).Value
    
    
    For i = 3 To cMaxRow
         cmbExp7.AddItem Worksheets("Database").Range("I" & i).Value
    
    
    For i = 3 To cMaxRow
         cmbExp8.AddItem Worksheets("Database").Range("I" & i).Value
    
    
    For i = 3 To cMaxRow
         cmbExp9.AddItem Worksheets("Database").Range("I" & i).Value
         
    Next i
    
    
End Sub

Comments

[meg_]

コードのどこでエラーが出ていますか？

[asian-ayam]

情報が足りず、申し訳ございません。For i = 3 To 5の箇所からエラーが出てしまいました！

Answer 1

Forで指定された変数は既に使われていますというエラーが出てしまいました。

エラーの原因はNextが不足しているからです。
For と Next は一対一対応していないとダメです。

    For i = 3 To cMaxRow
         cmbExp1.AddItem Worksheets("Database").Range("I" & i).Value
    Next i '※追加

    For i = 3 To cMaxRow
         cmbExp2.AddItem Worksheets("Database").Range("I" & i).Value
    Next i '※追加


'以下同様

構文的にエラーが出やすい状態になっているかとは思いますが、エラーの出にくい書き方を教えていただけましたら有難いです。

シート上のリストをコンボボックスに設定する場合は、RowSourceプロパティかListプロパティを使うとループを使わずに1行で設定できます。
Listプロパティのコード例は mattuwanさんが回答しているので、RowSourceを使ったコード例を。

    cmbMaker.RowSource = "Database!A3:A5"

    Dim cMaxRow As Long
    cMaxRow = Worksheets("DataBase").Cells(Rows.Count, "I").End(xlUp).Row

    Dim i As Lomg
    For i = 1 to 9
        Me("cmbExp" & i).RowSource = "Database!I2:I" & cMaxRow
    Next

Comments

[asian-ayam]

初心者にも分かりやすいコードを記載して下さり、有難うございます！おかげさまでキレイにまとまりました！

Answer 2

変数iは何度使ってもいいですが、
Forの数だけNextも要ります。

Private Sub UserForm_Initialize()
    Dim i As Long
    Dim cMaxRow As Long
    
    cMaxRow = Cells(Rows.Count, 1).End(xlUp).Row

    'comboBox add Category of descriptions for each expense
    For i = 3 To cMaxRow
        cmbExp1.AddItem Worksheets("Database").Range("I" & i).Value
    Next

    For i = 3 To cMaxRow
        cmbExp2.AddItem Worksheets("Database").Range("I" & i).Value
    Next
End Sub

長いので省略しました。。。

というか、もっと簡単に書けます。

Private Sub UserForm_Initialize()
    Dim i As Long
    Dim Rng As Range
    
    With ThisWorkbook.Worksheets("DataBase")
        Set Rng = .Range(.Range("I3"), .Cells(.Rows.Count, "I").End(xlUp))
    End With
    
    For i = 1 To 3
        Me.Controls("ComboBox" & i).List = Rng.Value
    Next
End Sub

オブジェクト名等はそちらに合わせて変更してください。
試しに新しいコンボボックスを3個配置して作って、試しました。

Comments

[asian-ayam]

ご回答下さり、有難うございます！