第19回.納品書を作成、顧客情報を取得(2)
エクセルで顧客管理を作成します、
前回の続きです、
再度、エクセルのサンプル です、
実際のエクセルを見ながら確認して下さい。
まず、シート「納品書」です。
今回は、シート上部の、コンボボックスの機能説明になります。
コントロールの追加方法の詳細は、「第13回.コントロールのボタンを配置 」を参照して下さい。
コンボボックス
１．2003なら「コントロールツールボックス」、2007以降なら「ActiveXコントロール」
の「コンボボックス」を適当な位置に追加して下さい。
２．プロパティを変更する
「オブジェクト名」を「cmb顧客一覧」
「ListRows」を20、これは、ドロップダウン時の行数なのでお好きな数値で
追加された「コンボボックス」をダブルクリック、または、「コードの表示」
このモジュールを以下のようにします。
Private Sub cmb顧客一覧_Change()
Dim strSplit() As String
If
cmb顧客一覧.ListIndex < 0 Then
Exit Sub
End If
strSplit() =
Split(cmb顧客一覧.List(cmb顧客一覧.ListIndex), " , ")
Range("納品書_顧客番号") =
strSplit(0)
End Sub
さらに、以下のSubモジュールを追加します。
Private Sub Worksheet_Activate()
Dim ary1 As Variant
Dim ary2() As
String
Dim r1 As Long, c1 As Long '顧客一覧の見出しの行,列位置
Dim i1 As Long, i2
As Long
r1 = 開始セル取得("顧客一覧").Row '顧客一覧の開始行位置を取得
c1 = 開始セル取得("顧客一覧").Column
'顧客一覧の開始列位置を取得
With シート取得("顧客一覧")
ary1 = .Range(.Cells(r1,
c1), .Cells(最終行取得(シート取得("顧客一覧")), c1 + 2))
End With
ReDim
ary2(UBound(ary1, 1) - LBound(ary1, 1))
For i1 = LBound(ary1, 1) To UBound(ary1, 1)
For i2 = LBound(ary1, 2)
To UBound(ary1, 2)
If i2 = LBound(ary1, 2) Then
ary2(i1 -
LBound(ary1, 1)) = ary1(i1, i2)
Else
ary2(i1 - LBound(ary1,
1)) = ary2(i1 - LBound(ary1, 1)) & " , " & ary1(i1, i2)
End
If
Next
Next
cmb顧客一覧.Clear
cmb顧客一覧.List() =
ary2
End Sub
上の２つのSubモジュールを作成します。
先に、
Private Sub Worksheet_Activate()
の説明をします。
これは、シートが選択された直後に動くイベントです。
「顧客一覧」より、「顧客番号」から３列分の情報を、" , "で、結合し、コンボボックスに表示します。
With シート取得("顧客一覧")
ary1 = .Range(.Cells(r1, c1),
.Cells(最終行取得(シート取得("顧客一覧")), c1 + 2))
End
With
「顧客番号」から３列分を、配列として、ary1に取り出します。
ReDim ary2(UBound(ary1, 1)-LBound(ary1, 1))
ary2には、３列を結合した文字列を格納します。
その為、まず、ary1の要素数に合わせて、配列の定義します。
このように、配列の要素を動的に決定する事を、動的配列と言います。
-LBound(ary1, 1)は、セル範囲から配列にした時、配列の先頭が、1から始まっているからです。
ary2には、0から入れる必要がありますので、このようにしています。
コンボボックスに設定する場合、0が空いていると、コンボボックスの１行目があいてしまうからです。
For～Next
ここでは、３列分のセル値を、" , "で、結合しています。
cmb顧客一覧.Clear
コンボボックスのリストをクリアします。
cmb顧客一覧.List() = ary2
先性された配列ary2を、コンボボックスのリストとして設定します。
以上で、シートを切り替えて、「納品書」を選択すると、コンボボックスに顧客の一覧が作成されます。
Private Sub cmb顧客一覧_Change()
If cmb顧客一覧.ListIndex < 0 Then
.ListIndexは、コンボボックスの選択された行インデックスが入ります。
先頭行は0になります。
コンボボックスが未選択の場合は、ListIndexは、-1です。
従って、未選択の場合は、処理を終了します。
strSplit() = Split(cmb顧客一覧.List(cmb顧客一覧.ListIndex), " , ")
コンボボックスから、顧客番号を取り出します。
先の、コンボボックス作成時に、" , "で結合しましたので、これを区切り文字として取り出します。
Split(文字列,区切り文字)
文字列を区切り文字で分割し、配列で返します。
配列は、要素0から作成されます。
Range("納品書_顧客番号") =
strSplit(0)
「顧客番号」のセルに、先に取り出した、顧客番号をいれます。
これにより、コンボボックスで選択した顧客情報が表示されます。
コンボボックス選択→顧客番号に設定
これにより、前回、顧客番号の変更で自動で顧客情報を表示する部分が起動されます。
Subモジュールの流れは、
cmb顧客一覧_Change→Worksheet_Change
となります。
今回は、ここまでです。
続きは、次回に。
ちょっと駆け足すぎますね。
次回で、ボタンの説明が終わったら、要所の復習をする事にしましょう。
そうそう、プログラム内のコメントは止めました。
面倒だから・・・ウソ！
WEBで見ると、無い方がプログラムが読みやすいので。
また、コピペする人は、分かりづらい所に、自分なりのコメントを付けた方が、理解が進むと思いますので。
ではでは。
同じテーマ「エクセル顧客管理」の記事
第16回.処理速度の向上はどこまでやれば良い（GW特別号No3）
第20回.納品書を作成、顧客情報を取得(3)
新着記事NEW ・・・新着記事一覧を見る
TRIMRANGE関数（セル範囲をトリム：端の空白セルを除外）｜エクセル入門(2024-08-30)
正規表現関数（REGEXTEST,REGEXREPLACE,REGEXEXTRACT）｜エクセル入門(2024-07-02)
エクセルが起動しない、Excelが立ち上がらない｜エクセル雑感(2024-04-11)
ブール型（Boolean）のis変数・フラグについて｜VBA技術解説(2024-04-05)
テキストの内容によって図形を削除する｜VBA技術解説(2024-04-02)
ExcelマクロVBA入門目次｜エクセルの神髄(2024-03-20)
VBA10大躓きポイント（初心者が躓きやすいポイント）｜VBA技術解説(2024-03-05)
テンキーのスクリーンキーボード作成｜ユーザーフォーム入門(2024-02-26)
無効な前方参照か、コンパイルされていない種類への参照です。｜エクセル雑感(2024-02-17)
初級脱出10問パック｜VBA練習問題(2024-01-24)
アクセスランキング ・・・ ランキング一覧を見る
1.最終行の取得（End,Rows.Count）｜VBA入門
2.繰り返し処理（For Next)｜VBA入門
3.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
4.変数宣言のDimとデータ型｜VBA入門
5.RangeとCellsの使い方｜VBA入門
6.ブックを閉じる・保存（Close,Save,SaveAs）｜VBA入門
7.セルのクリア（Clear,ClearContents）｜VBA入門
8.メッセージボックス(MsgBox関数)｜VBA入門
9.条件分岐（Select Case）｜VBA入門
10.ブック・シートの選択（Select,Activate）｜VBA入門
このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。