For文を使ってComboBoxへ登録する　【VBA在庫管理#16】

毎年コタツを買うか悩み結局買わないを繰り返す、どうもoReです。

今回は、For文を無理くり使っていきます。(笑)

For文を使ってComboBoxへ登録
ComboBox1から設定シートへ出力
メーカー名の重複確認処理
まとめ

For文を使ってComboBoxへ登録

【VBA在庫管理#13】で、部品登録フォームのコンボボックスにメーカー名を登録しました。

　　↓コードはこちら

'ユーザーフォーム初期化
Private Sub UserForm_initialize()
　　With ComboBox1
　　　　.AddItem “アサヒビール株式会社”
　　　　.AddItem “三和酒類株式会社”
　　　　.AddItem “Marlboro”
　　End With
End Sub

気付いていると思いますが、これだと取り扱うメーカーが増えるたびに
コンボボックスに登録するプログラムも追加していくことになります。

.AddItem “新たなメーカー名”←追加していく

非常に面倒臭いですよね?

それでは、面倒臭くなくしていきましょう。

シートを挿入→シート名を『設定』としましょうか。

セルA1に『メーカー名登録』と入力します。

セルA2～A4にメーカー名を入力します。

部品登録フォームのUserForm_initialize()を書き換えます。

　　↓こんな感じ

'ユーザーフォーム初期化
Private Sub UserForm_initialize()
　　Dim LastRow As Long, i As Long　'変数LastRowとiはLong型よ

　　ComboBox1.Clear　'コンボボックス1の登録データクリア
　　With Sheet3
　　　　LastRow = .Cells(Rows.Count, 1).End(xlUp).Row　'LastRowに設定シートのA列最終行数を入れる
　　　　For i = 2 To LastRow　'2～設定シートのA列最終行数まで繰り返し処理
　　　　　　ComboBox1.AddItem .Cells(i, 1)　'コンボボックス1に登録
　　　　Next i
　　End With
End Sub

このようにしておけば、LastRowに最終行数4が入り
2～4まで繰り返し処理します。

繰り返し処理の流れとして

①変数のiに2が入る。

②コンボボックス1に、設定シートのCells(2, 1)(セルA2)のデータが登録される。

③次に変数のiに3が入る。

④コンボボックス1に、設定シートのCells(3, 1)(セルA3)のデータが登録される。

⑤次に変数のiに4(設定シートのA列最終行数)が入る。

⑥コンボボックス1に、設定シートのCells(4, 1)(セルA4)のデータが登録される。

という流れになります。

設定シートA列にメーカー名を追加・削除して動作確認してみましょう。

ちゃんとコンボボックス1に登録できてますか?

これで、コンボボックスに登録するプログラムを追加する必要がなくなりました。

ComboBox1から設定シートへ出力

コンボボックスに登録するプログラムを追加する必要がなくなりましたが、
設定シートのA列にメーカー名を入力しなければいけません。

部品登録フォームのコンボボックス1に入力して、Enterキーを押したら、
設定シートのA列に出力するようにします。

KeyDown　KeyCode13を使用して出力します。

KeyDown?KeyCode?の方は→【VBA在庫管理#12】を見てね

　　↓こんな感じ

'設定シートにメーカー名登録処理
Private Sub ComboBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
　　Dim LastRow As Long, i As Long

　　If KeyCode = 13 Then　'エンターキーが押された場合
　　　　If ComboBox1 = "" Then　'コンボボックス1未入力の場合
　　　　　　Exit Sub　'処理終了
　　　　Else
　　　　　　With Sheet3
　　　　　　　　LastRow = .Cells(Rows.Count, 1).End(xlUp).Row
　　　　　　　　.Cells(LastRow + 1, 1) = ComboBox1.Text　'設定シートA列最終行数+1にコンボボックス1のデータを入れる(.Textは書かなくてもOK)
　　　　　　End With
　　　　End If
　　End If
End Sub

動作確認してみてください。

コンボボックス1のデータがEnterキーを押すと、
設定シートのA列最終行+1に出力してますか?

これで、設定シートのA列にメーカー名を
入力する必要がなくなりました。

メーカー名の重複確認処理

設定シートのA列にメーカー名を入力する必要がなくなりましたが、
このままでは、すでに登録されているメーカー名も登録されてしまいます。

すでに登録されているメーカー名は登録されないようにしましょう。

　　↓プログラムを追加します。

'設定シートにメーカー名登録処理
Private Sub ComboBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
　　Dim LastRow As Long, i As Long

　　If KeyCode = 13 Then
　　　　If ComboBox1 = "" Then
　　　　　　Exit Sub
　　　　Else
　　　　　　With Sheet3
　　　　　　　　LastRow = .Cells(Rows.Count, 1).End(xlUp).Row
　　　　　　　　For i = 2 To LastRow　'2～設定シートのA列最終行数まで繰り返し処理
　　　　　　　　　　If .Cells(i, 1) = ComboBox1.Text Then　'設定シートのA列のi行にコンボボックス1と同じメーカー名があった場合
　　　　　　　　　　　　Exit Sub　'処理終了
　　　　　　　　　　Else　'設定シートのA列のi行にコンボボックス1と同じメーカー名がなかった場合
　　　　　　　　　　　　If i = LastRow Then　'iが終値になった場合(最終行まで同じメーカー名がなかった場合)
　　　　　　　　　　　　　　.Cells(LastRow + 1, 1) = ComboBox1.Text
　　　　　　　　　　　　　　MsgBox "登録しました。"
　　　　　　　　　　　　　　Call UserForm_Initialize　'部品登録フォーム初期化(コンボボックス1再登録)
　　　　　　　　　　　　End If
　　　　　　　　　　End If
　　　　　　　　Next i
　　　　　　End With
　　　　End If
　　End If
End Sub