エクセル顧客管理 | 第19回.納品書を作成、顧客情報を取得(2) | Excelマクロを駆使したカスタマイズ可能なエクセル顧客管理、エクセルVBAの学習教材



最終更新日:2014-11-11

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


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


前回の続きです、


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


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


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



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


コントロールの追加方法の詳細は、「第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で見ると、無い方がプログラムが読みやすいので。


また、コピペする人は、分かりづらい所に、自分なりのコメントを付けた方が、理解が進むと思いますので。


ではでは。






同じテーマ「エクセル顧客管理」の記事

第20回.納品書を作成、顧客情報を取得(3)
第21回.イベント処理について
第22回.コントールについて
第23回.納品書を作成、商品情報を取得(1)
第24回.納品書を作成、商品情報を取得(2)
第25回.納品書を作成、商品情報を取得(3)
第26回.WorksheetFunctionについて

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

スプレッドシートが非常に遅い、高速化するには|Google Apps Script入門(1月17日)
画像のトリミング(PictureFormat,Crop)|ExcelマクロVBAサンプル集(12月27日)
シート保護|Google Apps Script入門(12月24日)
表示の固定|Google Apps Script入門(12月24日)
グラフ|Google Apps Script入門(12月21日)
入力規則|Google Apps Script入門(12月13日)
並べ替え|Google Apps Script入門(12月12日)
メモの挿入・削除と改行文字|Google Apps Script入門(12月6日)
リンクの挿入・編集・削除|Google Apps Script入門(12月6日)
セルに数式を入れる|Google Apps Script入門(12月1日)

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

1.RangeとCellsの使い方|ExcelマクロVBA入門
2.最終行の取得(End,Rows.Count)|ExcelマクロVBA入門
3.徹底解説(VLOOKUP,MATCH,INDEX,OFFSET)|エクセル関数超技
4.Range以外の指定方法(Cells,Rows,Columns)|ExcelマクロVBA入門
5.セルの参照範囲を可変にする(OFFSET,COUNTA,MATCH)|エクセル関数超技
6.セルのコピー&値の貼り付け(PasteSpecial)|ExcelマクロVBA入門
7.ひらがな⇔カタカナの変換|エクセル基本操作
8.CSVの読み込み方法|ExcelマクロVBAサンプル集
9.変数とデータ型(Dim)|ExcelマクロVBA入門
10.VBAのFindメソッドの使い方には注意が必要です|ExcelマクロVBA技術解説



  • >
  • >
  • >
  • 納品書を作成、顧客情報を取得(2)

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


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

    ↑ PAGE TOP