VBA コンボボックスの使い方と文字列縦上下中央揃えも実現

前回記事で、「ユーザーフォームのテキストボックス文字列を縦上下中央揃え」をなんとか設定できました。今回は、コンボボックスの使い方を解説しながら「文字列の縦上下中央揃え」を検証します。

くるみこ
くるみこ

今回はコンボボックスについて勉強します。一緒に「文字列の縦配置」についてもTextBox同様に調整できるかどうか検証していきますよ(^^)/

「コンボボックス」ってドロップダウンリストがついているんですよね。それってすごくかっこいいんですよね(^^) 早く教えてください。お願いします!

【この記事でわかること
・コンボボックスの使い方がわかります
・TextBoxと同じように文字列の縦上下中央揃えを設定する方法がわかります
・リストへの登録方法やセル範囲への書き込み方法がわかります

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

くるみこ
くるみこ

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

コンボボックスの使い方

「コンボボックス」は「テキストボックス」と「リストボックス」が合体したコントロールです。

コンボボックスを配置します

・まずは前回のユーザーフォームに設置していたTextBox二つをコンボボックスに置き換えました。

・設定値①と②をコンボボックスに置き換えています。プルダウンボタンが確認できます。
文字列表示が上付きになっているのが確認できます!
これはTextBoxと同じようにすれば表示位置を変更できそうなので後半で解説します。
・コントロールの設置方法は下の過去記事を参照してください。

コンボボックスの主なプロパティを確認します

オブジェクト名を変更しておきます

・デフォルトでは「ComboBox1」のようにオブジェクト名+NOになっています。
・VBAで操作するときのために、わかりやすく短い名前に変更しておきます。
・安易ですが、「Cmb」+NO にオブジェクト名に変更しました
・既にあるオブジェクト名と同じ名前は使用できないので「数字」が自動付加されます。

MatchEntryプロパティ

コンボボックスでリストの項目を検索する際の規則を設定します。値の取得が可能です。

定数動作
fmMatchEntryFirstLetter01部分検索(前方一致)
FmMatchEntryComplete1完全一致検索
FmMatchEntryNone2検索をしない

ShowDropButtonWhenプロパティ

・コンボボックスのドロップダウンボタンの表示タイミングを設定します。

定数内容
fmShowDropButtonWhenNever0ボタンを表示しません。
fmShowDropButtonWhenFocus1フォーカスされた場合だけボタンを表示します。
fmShowDropButtonWhenAlways2ボタンを常に表示します。(既定値)

Styleプロパティ

・コンボボックスで値を選択する方法を設定します。

定数内容
fmStyleDropDownCombo0テキスト領域に値を直接入力したりドロップダウンリストから
値を選択することができます。(既定値)
fmStyleDropDownList2テキスト領域に値の入力はできません。
ドロップダウンリストから値を選択する必要があります。

BoundColumnプロパティ

・複数列設定している場合、どの列の値をコンボボックスの値とするかを設定します。
・既定値は「1」です。「1」以上の指定した列の値をコンボボックスに割り当てます。

ColumnCountプロパティ

・コンボボックスに表示する列の数を設定します。既定値は1です。

ColumnHeadsプロパティ

・コンボボックスに列見出しを表示するかしないかを設定します。
・「False」=列見出しを表示しません(規定値)。「True」=列見出しを表示します。

ListRowsプロパティ

・コンボボックスに一度に表示できるデータの最大行数を設定します。(規定値は8です)

RowSourceプロパティ

・コンボボックスのリストとしてリンクするセル範囲を指定します。
(記述例)Sheet1!B2:B11 ← Sheet1のB2:B11のセル範囲を指定する場合の記述方法です。

コンボボックスの値をセル範囲へ入力する 

・コンボボックスのドロップダウンリストを設定する方法は複数あります。
・まずは基本的なAddItemメソッドを使う方法を解説します。

AddItemメソッドを使用して設定する方法

テキストボックスに入力した文字列を、リストに項目を登録する方法です。

AddItem メソッド

【構文】オブジェクト.AddItem ( Item[, Index] )
    例)ComboBox1.AddItem “項目”
・オブジェクトと Item は必須です。Index に「0」を指定すると項目の先頭に表示します。

イベントにAddItemで登録するコードを設定します

テキストボックス部分に入力された文字列は、Textプロパティで取得できます
・取得できた文字列を、AddItemで登録します。

'コンボボックスのEnterイベント
Private Sub Cmb1_Enter()
    With Cmb1
        If .Text <> "" Then
            .AddItem .Text
        End If
    End With
End Sub

・これでテキストボックスに入力した文字列が登録されるようになりました。

・でも、画像でわかるとおり同じ項目が複数登録されてしまいました。

同じ項目は登録しないようにする

・登録前に重複をチェックするようにコードに手を加え変更します。

'テキスト部の文字列を重複せずにItemに登録
Private Sub Cmb1_Enter()
    Dim i As Long       'ループカウンター用
    Dim flg As Boolean  '重複チェック用フラグ
    With Cmb1
        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

・同じItemが存在しているかどうかチェックしてから登録しています。
・これで重複しないように設定できました。

コンボボックスの選択項目をセルに登録する

・登録用のコマンドボタンを追加設置してクリックイベントに次のコードを書きます。
・コマンドボタンのオブジェクト名は「cmdSave」に変更しています。
・同じくキャプションも「登録」に変えています。

'設定値をワークシートに書き込む
Private Sub cmdSave_Click()
    Dim eRow As Long
    With Worksheets("設定値")
        eRow = .Cells(Rows.Count, 1).End(xlUp).Row + 1
        .Cells(eRow, 1).Value = Cmb1.Text
        .Cells(eRow, 2).Value = Cmb2.Text
        .Cells(eRow, 3).Value = txtSet3.Value
        .Cells(eRow, 4).Value = txtPW.Value
    End With
End Sub

・実行しているGIF画像で書き込みが成功していることがわかります。
・でも、同じ登録が書き込まれているのが確認できます。これは少し問題です!
・これも重複しないようにチェックしてから書き込むように考えます。
・D列に「生成PW」を書き込むようにしていしたが、ここを変更します。
・設定値①~③を連結したテキストを書き込むように修正してこの値で重複チェックします。

重複を回避するために変更したコード

'設定値をワークシートに書き込む
Private Sub cmdSave_Click()
    Dim eRow As Long
    Dim i As Long       'ループカウンター用
    Dim flg As Boolean  '重複チェック用フラグ
    Dim rng As Range
    Dim strSet As String
    strSet = Cmb1.Text & Cmb2.Text & txtSet3.Text
    With Worksheets("設定値")
        eRow = .Cells(Rows.Count, 1).End(xlUp).Row + 1
        Set rng = Range(Cells(2, 4), .Cells(eRow, 4))
        If strSet = "" Then Exit Sub
        '同じItemが存在するかチェックする
        For i = 1 To rng.Count
            If rng(i).Value = strSet Then
                flg = True  '存在時はflgをTrueに
                Exit For    'ループを抜ける
            End If
        Next
        'flgがFalseの場合は登録する
        If flg = False Then
            .Cells(eRow, 1).Value = Cmb1.Text
            .Cells(eRow, 2).Value = Cmb2.Text
            .Cells(eRow, 3).Value = txtSet3.Value
            .Cells(eRow, 4).Value = strSet
        Else
            MsgBox "重複データのため登録しませんでした!"
        End If
    End With
End Sub

・下のGIF画像で重複時にメッセージを表示して登録しない動作を確認できます。

セル範囲からコンボボックスにリストを設定する

・セル範囲に保存した設定値をコンボボックスのリストに登録します。
・登録はユーザーフォームのイニシャライズ時に読み込むように設定します。

Private Sub UserForm_Initialize()
    'TextBoxをイニシャライズするプロシージャを呼び出す
    Call TextBox_Initialize
    'ComboBoxのItemをセットするプロシージャを呼び出す
    Call cbmBox_ItemSet
End Sub
'ComboBoxのアイテムをセットする
Sub cbmBox_ItemSet()
    Dim eRow As Long
    Dim i As Long, j As Long
    eRow = Worksheets("設定値").Cells(Rows.Count, 1).End(xlUp).Row
    Dim Dic As Object, Keys As Variant
    Dim Item As Variant, Items As Variant
    Dim cmbNo() As Variant      'ComboBox用
    Dim ctl As Control          'コントロール用
    Dim ncmb As Long            'ComboBox用カウンター
    '配列初期化
    ReDim Preserve cmbNo(0)
    For Each ctl In fmPW_Make.Controls
        If TypeOf ctl Is msforms.ComboBox Then
            ncmb = ncmb + 1
            '配列に要素を追加(コントロール)
            ReDim Preserve cmbNo(ncmb)
            Set cmbNo(ncmb) = ctl
        End If
    Next
    For j = 1 To ncmb
        '重複登録できないDictionaryオブジェクトを利用する
        Set Dic = CreateObject("Scripting.Dictionary")
        With Worksheets("設定値")
            Items = .Range(.Cells(2, j), .Cells(eRow, j)).Value
        End With
        On Error Resume Next    '同一データ時のエラー回避用
        For Each Item In Items
            If Not Item = Empty Then    '空白は登録しない
                Dic.Add Item, Item
            End If
        Next
        On Error GoTo 0
        Keys = Dic.Keys
        For i = 0 To Dic.Count - 1
            cmbNo(j).AddItem Keys(i) 'リストにItemを登録する
        Next i
        Set Dic = Nothing
    Next j
End Sub

・「UserForm_Initialize」からCallして「cbmBox_ItemSet」を呼び出す方式にしました。
Dictionaryオブジェクトを利用してItemが重複しないように登録していきます。
・コンボボックスの数に応じてループしてすべてに設定していきます。

コンボボックスのテキスト部の縦上下中央揃え

TextBoxで使用したコードを利用します

'最初(表示前)に実行される
Private Sub UserForm_Initialize()
    'TextBoxをイニシャライズするプロシージャを呼び出す
    Call TextBox_Initialize
    'ComboBoxをイニシャライズするプロシージャを呼び出す
    Call ComboBox_Initialize
    'ComboBoxのItemをセットするプロシージャを呼び出す
    Call cbmBox_ItemSet
End Sub
'ComboBoxをイニシャライズする
Sub ComboBox_Initialize()
    Dim dmyL As msforms.Label   'ダミーのラベル作成用
    Dim i As Long
    Dim cmbNo() As Variant     'TextBox用
    Dim ncmb As Long            'TextBox用カウンター
    Dim ctl As Control          'コントロール用
    '配列初期化
    ReDim Preserve cmbNo(0)
    For Each ctl In fmPW_Make.Controls
        If TypeOf ctl Is msforms.ComboBox Then
            ncmb = ncmb + 1
            '配列に要素を追加(コントロール)
            ReDim Preserve cmbNo(ncmb)
            Set cmbNo(ncmb) = ctl
        End If
    Next
    For i = 1 To ncmb
        'ダミーラベルを追加
        Set dmyL = fmPW_Make.Controls.Add("Forms.Label.1")
        With cmbNo(i)
            .Font.Size = 11     'フォントサイズ指定
            .Height = .Font.Size + 10   '高さ調整
'            .MultiLine = False 'このプロパティは無い
            'TextBoxのプロパティをラベルに適用させる
            dmyL.Top = .Top
            dmyL.Left = .Left
            dmyL.Height = .Height
            dmyL.Width = .Width
            dmyL.BackColor = .BackColor '背景色
            'TextBox風のエフェクトに
            dmyL.SpecialEffect = fmSpecialEffectSunken
            dmyL.ZOrder 1 '1=fmBottom 最背面に表示(0=fmTopで最前面)
            'TextBoxのプロパティ調整
            .SpecialEffect = fmSpecialEffectFlat 'フラットに変更
            .Width = .Width - 3     'ラベルの内側になるように
            .Height = .Height - 4   '高さも同様
            .Left = .Left + 1.5     'Widthの3の半分
            .Top = .Top + 3         'ここで表示位置を微調整する
        End With
        Set cmbNo(i) = Nothing
        Set dmyL = Nothing
        Next
End Sub

「UserForm_Initialize()」イベントプロシージャに、Call ComboBox_Initialize のプロシージャを呼び出すコードを追加しています。
ComboBox_Initialize」の処理で変更した点はつぎのとおりです。
・24行目、コンボボックスには.MultiLineプロパティが無いからコメントアウトしています
・39行目、Topの値を「3」に調整しています
設定値③もテキストボックスからコンボボックスに変更しています。
(変更に伴い、既出のコードも影響する部分は修正しています。)
・実行画像はつぎのとおりです。文字列の配置を縦上下中央揃えにできています。

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

スポンサーリンク

まとめ(おわりに)

・いかがでしたでしょうか?
「いつも汎用でだれでも使えて活用できるように考えてVBAを使う」というポリシーを念頭に記事を書いています。
・今回も、記事内で使用したコードのサンプルファイルを登録していますのでご利用ください。
今までの記事のサンプルも登録していますのでよろしければお使いください(^^)

まとめと感想など

くるみこ
くるみこ

無事コンボボックスも設定できました。いかがでしたか?

コンボボックスでも縦上下中央揃えに見えるようにできましたね(^^)

「テキストボックス」のコードを流用できるなんてすごく楽でしたね(^^♪
リストの活用もなんとか理解できました今まで勉強してきたおかげです!

くるみこ
くるみこ

勉強して覚えたことを活用できるとすごく楽しくなってくる気持ちは良くわかります(^^) 次回はその他のコントロールについてもっと勉強しましょう!

【今回わかったことは】
・コンボボックステキスト部の縦上下中央揃えもTextBoxと同じ方法で実現できることがわかりました
・コンボボックス表示文字列を重複せずにリストに追加する方法とセル範囲に書き込む方法がわかりました
・セル範囲からコンボボックスのリストに一意のリストを設定する方法がわかりました

今後の記事について

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

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

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

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