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



最終更新日:2014-11-11

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


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


とうとう、20回まできました、


では、前回の続き、3部作の最終です。


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


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


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



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


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


その前に、シート「顧客一覧」にコンボボックスを追加します。

コンボボックスの追加方法は前回やったばかりですが、

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

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

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

  「オブジェクト名」を「cmbシート名」

このコンボボックスには、コードを入れません。

選択を変更しても何もすることがないからです。



ボタン


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

  の「コマンドボタン」を適当な位置に追加して下さい。

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

  「オブジェクト名」を「btn顧客検索」


追加された「ボタン」をダブルクリック、または、「コードの表示」


このモジュールを以下のようにします。

Private Sub btn顧客検索_Click()
  Call 顧客一覧へ(ActiveSheet.Name)
End Sub


続いて、シート「顧客登録」のシートモジュール「一覧へ_Click」以下の部分を修正します。

Private Sub 一覧へ_Click()
  Dim rtn As Integer 'メッセージの応答の受取
  rtn = MsgBox("入力中のデータは破棄されます" & vbLf & vbLf & _
"よろしいですか?", vbYesNo, "確認")
  If rtn = vbNo Then '「いいえ」の場合は終了
    Exit Sub
  End If
  Call 顧客一覧へ(ActiveSheet.Name)
End Sub


さらに、「Mod共通」の「顧客一覧へ」を以下に変更

Sub 顧客一覧へ(ByVal strSht As String)
  Dim i As Integer
  シート取得("顧客一覧").Select
  With ActiveSheet
    For i = 0 To .cmbシート名.ListCount - 1
      If .cmbシート名.List(i) = strSht Then
        .cmbシート名.ListIndex = i
        Exit For
      End If
    Next
  End With
End Sub


まだまだ、シート「顧客一覧」のシートモジュールへ以下を追加。

Private Sub Worksheet_Activate()
  Dim ary As Variant
  ary = シート取得("設定").Range("シート名")
  cmbシート名.Clear
  cmbシート名.List() = ary
End Sub


なんのなんの

Sub ファンクションF1()
  Dim strWork As String

  Select Case True
    Case ActiveSheet Is シート取得("顧客一覧")
      Select Case ActiveSheet.cmbシート名.Text
        Case シート取得("顧客登録").Name, ""
          
If Not IsEmpty(Cells(ActiveCell.Row, 開始セル取得("顧客一覧").Column)) And _
            ActiveCell.Row > 開始セル取得("顧客一覧").Row Then
            マクロ開始処理
            strWork = Cells(ActiveCell.Row, 開始セル取得("顧客一覧").Column)
            Call 顧客登録シート作成(True)
            開始セル取得("顧客登録").Offset(0, 1) = strWork
            Call 顧客一覧より取得(True)
            マクロ終了処理
          End If
        Case シート取得("納品書").Name
          If Not IsEmpty(Cells(ActiveCell.Row, 開始セル取得("顧客一覧").Column)) And _
            ActiveCell.Row > 開始セル取得("顧客一覧").Row Then
            シート取得("納品書").Range("納品書_顧客番号") = Cells(ActiveCell.Row, 開始セル取得("顧客一覧").Column)
            シート取得("納品書").Select
          End If
        Case Else
          
      End Select
    Case ActiveSheet Is シート取得("顧客登録")
      Debug.Print Timer
      Call 顧客一覧へ登録
      Debug.Print Timer
  End Select
End Sub



準備OKでしょうか


あっちこっちで追加・修正になりました。

何をしているかと言うと、以下の流れになります


「納品書」のボタンをクリック

「顧客一覧」のシートを表示

顧客を選択し、F1

「納品書」の顧客番号が設定される

顧客情報が表示される


この一連の機能を実現する為に、いろいろ追加しています。

このような場合、よくあるのは、ユーザーフォームを使用します。

まあ、それでも良いのですが、ユーザーフォームを新たに作り、プログラムを入れるのは、

それなりの作業になります。

せっかく顧客一覧のシートがあるのですから、

これをそのまま使用した方が効率的なのはいうまでもありません。

ただし、それなりの仕掛けは必要です。


Private Sub btn顧客検索_Click()
ここはそのままですね。

自身のシート名を引数に入れての、Subプロシーシ゜ャーのCallです。


Private Sub 一覧へ_Click()
ここも、修正箇所の内容は、上と同じです。


Sub 顧客一覧へ(ByVal strSht As String)
シート「顧客一覧」を選択しておきます。

そして、

With ActiveSheet
  For i = 0 To .cmbシート名.ListCount - 1
    If .cmbシート名.List(i) = strSht Then
      .cmbシート名.ListIndex = i
      Exit For
    End If
  Next

End With
cmbシート名のリストを先頭より検索し、引数のシート名と一致するリストを選択状態にします。

.List(i)でコンボボツクスのリストのi番目(0開始)のテキストを取得します。

.ListIndex = iで、i番目のリストを選択状態にします。

Exit Forは、既に用はすんだので、ループを抜けます。

Exit Forを記述しなくても、正常に動作しますが、

プログラムの基本は、余分な事はさせない。


Private Sub Worksheet_Activate()

「顧客一覧」のシートが選択された時に起動されるイベントです。
シート「設定」の、シート名の範囲を、配列に入れ、

cmbシート名をクリアした後、この配列を入れています。

あれっ、名前定義「シート」名」の設定はどこかで記述しましたかね?

まあ、もうお分かりですよね、可変リストを作成するときの、名前定義と同様です。

もっとも、シートなんて、そんなに追加するものでもないので、固定でも支障ありませんが。


注意).確か前回、コンボボックスに入れる時に、0番目が空だと先頭が空行になると説明しました。

     しかし、ここで作成される、配列は、Lboundが1であり、0番目が存在していないのです。

     ですから、問題が無いのです。

     セル範囲(Range)を、Variantに入れた場合は、1からの配列になります。



Sub ファンクションF1()
このモジュールは、F1が押された時に、起動されるモジュールです。

覚えていますでしょうか、ブックモジュールの「Workbook_Open」で定義しています。

つまり、どのシートで、F1が押されても、このモジュールが起動されます。

そこで、

Select Case True
    Case ActiveSheet Is シート取得("顧客一覧")

この部分で、押されたシートを判定し、個別のCase文で処理しています。

さらに、

今回は、シート名のコンボボックスの選択状態により、処理を分岐させるようにしました。


Select Case ActiveSheet.cmbシート名.Text
        Case シート取得("顧客登録").Name, ""
この部分ですね。

「顧客登録」の場合は、いままで通りです。


Case シート取得("納品書").Name
新たに、「納品書」の場合の処理を追加しました。

「納品書」の「顧客番号」に、現在行の顧客番号を設定し、

シート「納品書」へ移動しています。


注意).IF文で、顧客番号の存在行かをチェックしています。

     このIF文は、シート名判定のSelect文の上にすることもできます。

     ただし、もし今後、顧客番号の無い行でF1を押した場合の処理を記述する時に、

     変更が大きくなってしまいます。

     今回は、そのような処理を入れる事は無いと思うので、上にした方が良いように思えます。

     ただ、このような事を考慮してプログラミングしていくことが、「保守性」を高めるのです。


注意).シート取得("納品書")が2行続いているので、Withを書きたくなります。

     しかし、既にSelectが2段階、そしてIF文となっています。

     ネストは、概ね3段階程度にしないと、「可読性」を落とします。

     また、コンパイラーも完璧ではないので、動作不良を起こす原因にもなります。

     従って、この場合2行程度ならWithを使いません。

     さらに複雑になる場合は、IF文以下を、別のSubモジュールにしていきます。



これで、

「納品書」のボタンをクリック

「顧客一覧」のシートを表示

顧客を選択し、F1

「納品書」の顧客番号が設定される

顧客情報が表示される

の機能が実装されました。

この機能は、さらに今後作成するシートでも使う事になるはずです。

このような、そのソフトにとっての基本機能は、しっかりとさせておく必要があります。


さてさて、大急ぎで3部作を出しました。


別に急ぐ必要がないのですが、プログラム作っちゃったし・・・ガーン


まあ、次回以降で、補足説明をします。


この3部作を読んで理解できるなら、もうバリバリのプログラマーですからね。


次回から、少し復習をしましょう。


ではまた。






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

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

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

画像のトリミング(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日)
セルのコピー&各種ペースト|Google Apps Script入門(11月22日)

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

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技術解説



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

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


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

    ↑ PAGE TOP