エクセル顧客管理
納品書を作成、顧客情報を取得(2)

Excelマクロを駆使したカスタマイズ可能なエクセル顧客管理、エクセルVBAの学習教材
最終更新日:2014-11-11

第19回.納品書を作成、顧客情報を取得(2)


エクセルで顧客管理を作成します、


前回の続きです、


再度、エクセルのサンプル です、


実際のエクセルを見ながら確認して下さい。


まず、シート「納品書」です。


Excel VBA 解説

今回は、シート上部の、コンボボックスの機能説明になります。


コントロールの追加方法の詳細は、「第13回.コントロールのボタンを配置 」を参照して下さい。


エクセルで顧客管理を作ります、前回は、話が難しかったかもしれません、今回は簡単です、コントロールのボタンを配置して、マクロを割り当てます。「顧客一覧」から、新規登録する為に、「顧客登録」のシートへ移動する為に使用します。直接シート選択すれば良さそうですが、最終的には、シート名は非表示にすることになりますので。


コンボボックス


1.2003なら「コントロールツールボックス」、2007以降なら「ActiveXコントロール」

  の「コンボボックス」を適当な位置に追加して下さい。

2.プロパティを変更する

  「オブジェクト名」を「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


上の2つのSubモジュールを作成します。



先に、

Private Sub Worksheet_Activate()

の説明をします。

これは、シートが選択された直後に動くイベントです。


「顧客一覧」より、「顧客番号」から3列分の情報を、" , "で、結合し、コンボボックスに表示します。


With シート取得("顧客一覧")
ary1 = .Range(.Cells(r1, c1), .Cells(最終行取得(シート取得("顧客一覧")), c1 + 2))
End With
「顧客番号」から3列分を、配列として、ary1に取り出します。


ReDim ary2(UBound(ary1, 1)-LBound(ary1, 1))

ary2には、3列を結合した文字列を格納します。

その為、まず、ary1の要素数に合わせて、配列の定義します。

このように、配列の要素を動的に決定する事を、動的配列と言います。

-LBound(ary1, 1)は、セル範囲から配列にした時、配列の先頭が、1から始まっているからです。

ary2には、0から入れる必要がありますので、このようにしています。

コンボボックスに設定する場合、0が空いていると、コンボボックスの1行目があいてしまうからです。


For~Next

ここでは、3列分のセル値を、" , "で、結合しています。


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)
第17回.商品マスタを作成、2段階の可変リスト
第18回.納品書を作成、顧客情報を取得(1)
第19回.納品書を作成、顧客情報を取得(2)
第20回.納品書を作成、顧客情報を取得(3)
第21回.イベント処理について
第22回.コントールについて
第23回.納品書を作成、商品情報を取得(1)
第24回.納品書を作成、商品情報を取得(2)
第25回.納品書を作成、商品情報を取得(3)
第26回.WorksheetFunctionについて


新着記事NEW ・・・新着記事一覧を見る

イータ縮小ラムダ(eta reduced lambda)|エクセル入門(2023-11-20)
PIVOTBY関数(縦軸と横軸でグループ化して集計)|エクセル入門(2023-11-19)
GROUPBY関数(縦軸でグループ化して集計)|エクセル入門(2023-11-18)
PY関数(Pythonコードをセル内で実行)|エクセル入門(2023-11-17)
画像「セルに配置」のVBAについて(365の新機能)|VBA技術解説(2023-11-13)
スピルのゴーストの範囲を選択するVBA|ツイッター出題回答 (2023-10-31)
エクセル試験3:月間の所定労働時間|エクセル練習問題(2023-10-04)
エクセル試験2:所得税の計算|エクセル練習問題(2023-10-04)
エクセル試験1:曜日別の平均客単価|エクセル練習問題(2023-10-04)
列全体を指定する時のRangeとColumnsの違い|ツイッター出題回答 (2023-09-24)


アクセスランキング ・・・ ランキング一覧を見る

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.繰り返し処理(For Next)|VBA入門
4.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
5.変数宣言のDimとデータ型|VBA入門
6.条件分岐(IF)|VBA入門
7.ブックを閉じる・保存(Close,Save,SaveAs)|VBA入門
8.並べ替え(Sort)|VBA入門
9.マクロとは?VBAとは?VBAでできること|VBA入門
10.Range以外の指定方法(Cells,Rows,Columns)|VBA入門




このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。


記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。



このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
本文下部へ