Excel VBAで5連動のコンボボックスを作成
目次
5連動のコンボボックスを作成
こんにちは。伊川(@naonaoke)です。
なぜかしら、Exxcelに慣れてくるとコンボボックスを連動させたくなります。
一般的な方法は、INDIRECT関数を使って連動コンボボックスを作成します。
しかし、このINDIRECT関数を利用して連動コンボボックスを作成するのは、汎用性がありません。
上の記事は、MATCH関数とOFFSET関数を利用したコンボボックスの作成方法を紹介しました。
3年前の動画ですが、いまだに嬉しいコメントを頂いています。
では、VBAで連動のコンボボックスを作成するには、どのようにするのか？
それが今回の記事の内容になります。
今回のお題
- 2連動以上のコンボボックスを作成する
- VBAで連動のコンボボックスを作成する
今回の必要な知識
- SQL文の知識
- ユーザーフォームを作成する知識
連動コンボボックス
巷では、連動コンボボックスは、大抵が2連動までです。
それは、3連動以上はお金になります。
ACCESSも２連動までは紹介されています。
しかし、3連動のコンボボックス作成を紹介している記事は、めったにありません。
今回は、思い切って5連動まで作成します。
5連動コンボボックス 作業手順 その1 データベース確認
このようなデータベースがあるとします。
最近、よく質問がくるのですが、一意のキーを設定できない質問がよく来ます。
その質問の内容が、顧客別単価を設定するような場合がよくあります。
上の記事で紹介していますが、今回は、主キーは絶対に設定できません。
さて、どのような方法があるでしょうか？
実務であるあるの事例
このような事は実務であるあるです。
- 言葉をつなげて、一意のキーを作る
- 配列を使う但し、動的配列でないとダメ
- なぜなら、メンテナンスが面倒になるから
しかし、今回は、SQL文を使いますので、コピペで対応できます。
5連動のコンボボックス 作業手順 その2 事前の準備
各コンボボックスにこのようなオブジェクト名を設定します。
オブジェクト名は、下記のように設定します。
上記の部分に設定をします。
5連動コンボボックス 作業手順 その3 コードの解説
使う変数を一番上部に設定します。
上部に設定するだけで、同一プロシージャ内で使えます。
最初の顧客名のコードを紹介します。
Private Sub cmb顧客名_Enter()
Me!cmb顧客名.Clear
odbdDB = ActiveWorkbook.Path & "\納品書2.xlsm"
Set adoCON = New ADODB.Connection
With adoCON
.Provider = "Microsoft.ACE.OLEDB.12.0"
.Properties("Extended Properties") = "Excel 8.0"
.Open odbdDB
End With
adoRS.CursorLocation = adUseClient
strSQL = "SELECT 顧客名 FROM [Sheet2$] "
strSQL = strSQL & "GROUP BY 顧客名;"
adoRS.Open strSQL, adoCON, adOpenDynamic
Do Until adoRS.EOF
Me!cmb顧客名.AddItem adoRS.Fields(0).Value
adoRS.MoveNext
Loop
adoRS.Close
Set adoRS = Nothing
adoCON.Close
Set adoCON = Nothing
End Sub
感動したコード
Excelファイルを1つしか利用していないのに、相対パスを利用しています。
このような記載方法があるのかという事を知ったときは感動のアラシでした。
抽出条件
SQL文は上記のように設定します。
ACCESSであれば、集計クエリを利用するとカンタンなのです。
しかし、Excelで利用するには、集計クエリは標準機能では利用できません。
なので、このようなSQL文で顧客氏名をグループ化できます。
5連動コンボボックス 作業手順 その4 抽出条件を変化させる
これが、5連動目の抽出条件になります。
SQL文をコピーして要所を変更すれば、6連動、7連動と追加することができます。
5連動コンボボックス 作業手順 その5 メンテナンスが楽
新しい顧客が増えても、商品名が増えても追加するだけです。
ここがポイント
今回は、VBAで連動のコンボボックス作成を紹介しました。
しかも、SQLをミックスさせました。それは、メンテナンスを楽にするためです。
一度作成してしまえば、何度でも再利用できます。
まとめ
VBAは、関数でできることを、VBAで実現できるようになるとスキル向上を実感できます。
何度も挑戦してください。
カンタンなVBAに少しだけ、スパイスを加えるだけで、マネタイズできるかもしれません。
今回のサンプルファイルは、No167です。
わからない事を延々と考えるのは、無駄です。
- なんで作動かないの？
- もうやだ！VBAなんか嫌い！
- ネットで調べても情報がない！
必ず作動するコードが、ここにあります。
