Excel VBA コンボボックスとセル範囲の連携を再設定する

さて、「コンボボックステキスト部の縦上下中央揃え」も実現できました。そして一意なリストを追加したりセル範囲に書き込む方法も取得できました。今回は、セル範囲のデータとコンボボックスとの連携について検証して再設定します。

くるみこ
くるみこ

もう少しコンボボックスについての勉強を継続しましょう。今回はコンボボックスの文字列とセル範囲のデータとの連携について検証していきます(^^)/

3つ設置した「コンボボックス」の連携もですよね(^^) だんだん面白くなってきました。よろしくお願いします!

【この記事でわかること
・コンボボックスの文字列とセル範囲のデータとの連携方法を再設定します
複数のコンボボックス文字列の重複チェック方法がわかります

前回記事のおさらいは、下のカードをクリックすれば開きます(^^ゞ

くるみこ
くるみこ

前回記事では、ユーザーフォームに設置したコンボボックスの文字列縦表示位置を上下中央揃えにする方法とリストの使用方法を解説しています。よければ覗いてみてね(^^)/

設置コンボボックスの使用目的を確認します

設定値①~③のラベル表記で3つの「コンボボックス」を設置しました。今更ですが、改めて使用目的を明確にしてラベル表記も変更していきます。「PW生成ツール」として機能させることを目的としていることを忘れないようにしていきます。

「パスワード管理」については過去記事を参照してください。

それぞれのコンボボックスの目的設定

・前回、重複チェックに一意のデータとして「Key」を設定して判断しました。間違いではありませんが、パスワードを管理する上では通常は次のような項目になっていると思います。

順番に書いていくと
分類(サイト、PC、クラウド、メール、SNS、ソフト、アプリ等の分類)
・設定値①名称
(サイト名、URLなど)
・設定値②ID
(ユーザー名、アカウント名等ログイン用に登録したもの)
・設定値③mKey
(自分だけが知る個別のマスターkeyをいくつか)
◎実際のパスワードはこれらの情報から生成したパスワードを登録します

設定値②IDは重複がありますよね。設定値③mKeyも重複可能です。
けど設定値①名称は重複してはいけないですね。①が重複しているということは、登録が複数になっているということです。①も一意にする必要があるということですね。

「分類」についても重複は可能ですが、分類」+①「名称」の重複はあり得ません

以上を踏まえると、分類」+①「名称」の重複をチェックして②「ID」や③「mKey」に変更があったら書き換えるかどうか問い合わせて判断を仰ぐ必要があります

UserFormコンボボックスを調整

・新たに「分類≫」を新設し、オブジェクト名を「Cmb1」としました。
・設定値①を →「名称に変更し、オブジェクト名を「Cmb2」としました。
・設定値②を →「IDに変更し、オブジェクト名を「Cmb3」としました。
・設定値③を →「mKeyに変更し、オブジェクト名を「Cmb4」としました。

・調整したUserFormがこちらです。

重複チェックを見直したVBAコード

「登録」ボタンクリックイベントのコード

'重複をチェックして設定値をワークシートに書き込む
Private Sub cmdSave_Click()
    Dim eRow As Long    '最終行
    Dim i As Long       'ループカウンター用
    Dim rng1 As Range, rng2 As Range 'セル範囲
    Dim strSet As String    'Key文字列(A+B+C+D)
    Dim strName As String   '名称文字列(B列)
    Dim re As Long
    Call cmdPW_Click    'PW生成する
    With Worksheets("設定値")
        eRow = .Cells(Rows.Count, 2).End(xlUp).Row + 1
        strName = Cmb1.Text & Cmb2.Text
        If strName = "" Then MsgBox "「名称」がありません!": Exit Sub
        strSet = Cmb1.Text & Cmb2.Text & Cmb3.Text & Cmb4.Text
        If strSet = "" Then MsgBox "「Key」がありません!": Exit Sub
        Set rng1 = Range(.Cells(2, 5), .Cells(eRow, 5))
        '同じItemが存在するかチェックする
        For i = 1 To rng1.Count
            If rng1(i).Value = strSet Then
                MsgBox "重複データのため登録しませんでした!"
                Exit Sub
            End If
        Next
        'Keyに重複が無かった場合、分類+名称で再チェック
        Set rng1 = Range(.Cells(2, 1), .Cells(eRow, 1))
        Set rng2 = Range(.Cells(2, 2), .Cells(eRow, 2))
        '同じ名称が存在するかチェックする
        For i = 1 To rng1.Count
            If rng1(i).Value & rng2(i).Value = strName Then
                re = MsgBox("同じ名称があります!書き換えますか?", _
                                        vbYesNo + vbExclamation)
                If re = vbYes Then
                    'データを上書きさせるため
                    eRow = i + 1 'eRowにセルの行番号を代入
                Else: Exit Sub
                End If
            End If
        Next
        '指定セルにデータを書き込む(追加は最下部)
        .Cells(eRow, 1).Value = Cmb1.Text
        .Cells(eRow, 2).Value = Cmb2.Text
        .Cells(eRow, 3).Value = Cmb3.Text
        .Cells(eRow, 4).Value = Cmb4.Text
        .Cells(eRow, 5).Value = strSet
    End With
End Sub

コードを解説

・9行目、「Call cmdPW_Click」でパスワードを生成させています。
・11行目、セル範囲の最終行下の行を取得しています。
・12行目で、「名称」コンボボックスの文字列を取得しています。
・13行目では、「名称」に値が無かった場合、処理を終了させて抜けます。
・14行目は、4つのコンボボックスの文字列を連結して変数「strSet」に代入しています。
・15行目で、「strSet」に値が無かった場合、処理を終了させて抜けます。
・16行目で、「Key」列のセル範囲を変数にセットしています。
・18~23行目のループでセル範囲のデータとの重複をチェックし、重複の場合抜けます。
以降は重複が無かった場合の処理
・25行目で、「分類」列のセル範囲を変数「rng1」にセットしています。
・26行目で、「名称」列のセル範囲を変数「rng2」にセットしています。
・28~38行目のループ処理で「分類」+「名称」の重複をチェックしています。
・29行目は、重複があった場合の分岐処理です。
・30行目は、重複していた場合にデータを上書きするかどうかを問い合わせます
・34行目では、上書きが選択された場合、上書きする行番号を変数「eRow」に代入します。
・40~44行目で、各コンボボックスの文字列を「eRow」のセルに書き込んでいます。

その他のコード見直し

・共通している各コンボボックスのイベント処理内に書かれたコードについては、次のように、共通部分を「Subプロシージャ化」して「Call」するように変更しました。

'テキスト部の文字列を重複せずにItemに登録
Private Sub Cmb2_Enter()
    Call Cmb_Enter(Cmb2)
End Sub

'テキスト部の文字列を重複せずにItemに登録
Sub Cmb_Enter(cmbNo As ComboBox)
    Dim i As Long       'ループカウンター用
    Dim flg As Boolean  '重複チェック用フラグ
    With cmbNo
        If .Text = "" Then Exit Sub
        '同じItemが存在するかチェックする
        For i = 0 To .ListCount - 1 'Listは0から始まるので
            If .List(i) = .Text Then
                flg = True  '存在時はflgをTrueに
                Exit For    'ループを抜ける
            End If
        Next
        'flgがFalseの場合は登録する
        If flg = False Then .AddItem .Text
    End With
End Sub

・ここまでで「コンボボックス」関係の動作検証は終了します。

・今回は以上で終了します。

スポンサーリンク

まとめ(おわりに)

・いかがでしたでしょうか?
・今回は、動作確認のGIF画像はサイズが大きくなるため設置していません。コードをコピペして使用するか、サンプルファイルを利用して確認してみてください(^^)
・今回も、記事内で使用したコードのサンプルファイルを登録していますのでご利用ください。
今までの記事のサンプルも登録していますのでよろしければお使いください(^^)

まとめと感想など

くるみこ
くるみこ

ひとまず「コンボボックス」関係の動作設定は完了です。

パスワード管理に利用できるように一歩進めましたね(^^)

「Key」列以外での複数コンボボックスのデータ重複をチェックする方法も勉強できました(^^♪

くるみこ
くるみこ

次回こそ、その他のコントロールについて勉強していきますね!

【今回わかったことは】
・コンボボックスの文字列とセル範囲のデータとの連携方法
・複数のコンボボックスの重複チェックと連携方法

今後の記事について

今回の記事はいかがだったでしょうか。皆さまのお役に立てたなら幸いです(^^;
「汎用でだれでも使えて活用できるように考えてVBAを使う」というポリシーで、記事を継続して書いていきたいと思っています。どうぞよろしくお願いしますm(_ _)m

【検討中の今後の記事内容は・・・・・】
・実務に役立つものを提供できるよう常に検討しています(^^ゞ
・その他雑記的に「プチネタなど」もいろいろ考えていきたいと思います・・・・・
・今後の記事にもご期待ください(^^)/

記事のサンプルファイルをダウンロードできます

今回の記事のサンプルをダウンロードできるようにしています
過去の記事で使用したサンプルファイルがダウンロードできるページを設置しています
こちら(このリンク先)からご利用ください