【販売管理システム作成】見積書を期間指定して作成する方法②【リストボックス作成】

販売管理システム【見積編】

みなさん、こんにちはケンケンです。

販売管理システムをエクセルのみで作成するというチャレンジシリーズになります。

前回は見積書作成コードのカスタマイズをすることでいろいろ修正を加えていきましたが、

今回は期間指定して見積書を作成する方法をご紹介していきます。

1 入力フォームを作る
2 フィルタオプションを使って抽出しよう
3 まとめ

入力フォームを作る

期間を指定して見積書を作るときに必要なフォームを作成します。

以前は一括作成したのでフォームを必要としませんでしたが、今回は期間指定するためのフォームを作り、ユーザーが使いやすくなるよう工夫していきます。

コントロールを配置する

まずは、期間指定用のフォームを作りコントロールを配置しましょう。

VBEを起動して、「挿入」から「ユーザーフォーム」を選択しフォームを新規作成します。

以下がフォームの完成図です。確認しておきましょう。

フォームを作ることで、すでに登録済みの見積データをフォーム上のリストボックスに表示させることで、作成するべき見積書を視覚的に指定することができるようになります。

タブオーダーを使ってフォーカスの順番を設定しよう

「Tab」キーを使ってコントロール間を移動する順番をタブオーダーで設定することが可能です。

これもユーザーが使いやすくするための工夫なので細かいテクニックですが、覚えておきましょう。

VBE画面から「表示」→「タブオーダー」を選択します。

上図のように設定することをおすすめします。

イメージとしては左上から右下へ向かうと良いでしょうか。

フォームの初期設定

今回のフォームはリストボックスが配置されていますので、そのリストボックスに初期値を設定します。

フォームが起動された瞬間に走るプログラムのことですね。

フォームの初期値は、コード画面の「UserForm」の「Initialize」を選択して設定します。

プロシージャが自動生成されるのでそこにコードを記述していきます。

Private Sub UserForm_Initialize()
    With lstMitumori'①
        .ColumnCount = 3
        .ColumnWidths = "50;100;100"
        .TextAlign = fmTextAlignLeft
    End With
    
    With lstMeisai'②
        .ColumnCount = 7
        .ColumnWidths = "80;36;60;60;60;60;60"
        .TextAlign = fmTextAlignCenter
    End With
    
    btnCreat.Enabled = False'③
    btnUpdate.Enabled = False'③
End Sub

Private Sub UserForm_Initialize()

With lstMitumori'①

.ColumnCount = 3

.ColumnWidths = "50;100;100"

.TextAlign = fmTextAlignLeft

End With

With lstMeisai'②

.ColumnCount = 7

.ColumnWidths = "80;36;60;60;60;60;60"

.TextAlign = fmTextAlignCenter

End With

btnCreat.Enabled = False'③

btnUpdate.Enabled = False'③

End Sub

コードの解説

フォームに２つのリストボックスがあるので、それぞれについて設定します。

①上のリストボックス（lstMitumori）の設定を５行でしています。

リストは３列で左揃えにしています。

②下のリストボックス（lstMeisai）の設定を５行でしています。

リストは７列で中央ぞろえにしています。

③見積書作成ボタンと見積書修正ボタンを選択不可能の設定にしています。

ちなみに、編集不可の設定はLockedプロパティを使うので臨機応変に使い分けましょう。

フィルタオプションを使って抽出しよう

リストボックスに表示させるデータをフィルタオプション機能を使って抽出します。

せっかくエクセルを使っているので、既存の機能を使わせてもらいましょう。

フィルタオプションについてはこちらの記事で紹介しているので参考にどうぞ。

見積データ一覧シートに下準備をする

フィルタオプションを使うに当たってシートにいくつか仕掛けを置いておく必要があります。

リスト範囲と検索条件範囲と抽出範囲の３つを見積データ一覧シートに以下のように設定します。

AdvancedFilterメソッドを使う

フィルタオプションをVBAで制御する場合にAdvancedFilterメソッドを使います。

構文は以下の通りです。

リスト範囲.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=検索条件範囲,

CopyToRange:=抽出範囲, Unique:=重複レコードの有無

ちょっと長くて複雑に見えますが、要は検索したい範囲に対し設定した検索条件を使って抽出範囲に結果を書き出しているだけです。

具体的にコードを書いてみましょう。抽出用だけでなく検索全体を含んだコードを以下で一気にご紹介します。

検索ボタンをダブルクリックして生成されたbtnKensaku_Clickプロシージャ内に記述しましょう。

Private Sub btnKensaku_Click()
    If txtDay1.Text = "" Or txtDay2.Text = "" Then '①
        MsgBox "期間指定してください。", vbInformation
        Exit Sub
    End If
    
    Sheets("見積データ一覧").Activate '②
    Range("O2:P2").Value = "" '③
    Range("O2").Value = ">=" & txtDay1.Text '④
    Range("P2").Value = "<=" & txtDay2.Text '④
    Sheets("見積データ一覧").Range("A1:M7").AdvancedFilter Action:=xlFilterCopy, _
        criteriarange:=Range("O1:P2"), copytorange:=Range("O4:AA4"), Unique:=True '⑤
        
     With lstMitumori '⑥
        .Clear '⑦
        Dim Lrow '⑧
        Lrow = Sheets("見積データ一覧").Range("O" & Rows.Count).End(xlUp).Row '⑧
        Dim cnt
        For cnt = 5 To Lrow '⑨
            If Sheets("見積データ一覧").Range("O" & cnt).Value <> Sheets("見積データ一覧").Range("O" & cnt + 1).Value Then '⑩
                .AddItem Format(Range("O" & cnt).Value, "00000") '⑪
                .List(.ListCount - 1, 1) = Range("P" & cnt).Value '⑫
                .List(.ListCount - 1, 2) = Range("Q" & cnt).Value '⑫
            End If
        Next
     End With
     lstMeisai.Clear '⑬
End Sub

Private Sub btnKensaku_Click()

If txtDay1.Text = "" Or txtDay2.Text = "" Then '①

MsgBox "期間指定してください。", vbInformation

Exit Sub

End If

Sheets("見積データ一覧").Activate '②

Range("O2:P2").Value = "" '③

Range("O2").Value = ">=" & txtDay1.Text '④

Range("P2").Value = "<=" & txtDay2.Text '④

Sheets("見積データ一覧").Range("A1:M7").AdvancedFilter Action:=xlFilterCopy, _

criteriarange:=Range("O1:P2"), copytorange:=Range("O4:AA4"), Unique:=True '⑤

With lstMitumori '⑥

.Clear '⑦

Dim Lrow '⑧

Lrow = Sheets("見積データ一覧").Range("O" & Rows.Count).End(xlUp).Row '⑧

Dim cnt

For cnt = 5 To Lrow '⑨

If Sheets("見積データ一覧").Range("O" & cnt).Value <> Sheets("見積データ一覧").Range("O" & cnt + 1).Value Then '⑩

.AddItem Format(Range("O" & cnt).Value, "00000") '⑪

.List(.ListCount - 1, 1) = Range("P" & cnt).Value '⑫

.List(.ListCount - 1, 2) = Range("Q" & cnt).Value '⑫

End If

End With

lstMeisai.Clear '⑬

End Sub

コード解説

①テキストボックスのtxtDay1もしくはtxtDay2が空欄だったらメッセージボックスで注意し、プログラムを中止します（下図参照）

②フィルタオプションを使う時は対象のシートをアクティブにしなくてはならないので、見積データ一覧シートをアクティブにします。

③検索範囲はO列からQ列までですが、期間指定はO列とP列ですね。

そこで、検索する度に一度対象セルを空白にする処理をします。

④期間を指定するための設定です。

⑤VBAでフィルタオプションを使うためのコードです。

先ほどの構文に照らし合わせて内容を確認しておきましょう。

⑥～⑫上のリストボックスにデータを表示するための設定となります。

⑦ボックス内をクリアにして表示されるデータが重複しないように設定しています。

⑧～⑫リストボックスに転記すべきデータを見積データ一覧シートから取得します。⑤で抽出されたデータをリストボックスに転記する作業になります。

⑧まず抽出したデータの最終行を取得します。

⑨ForNextを使って抽出したデータをリストボックスに転記します。開始行は５行目ですね。

⑩リストボックスに転記したいデータは見積Noごとで良いですね。今のままだと見積Noが重複して転記されてしまうのでIf文を使って重複を排除しています。

⑪AddItemメソッドでリストボックスに新規行を追加しています。見積Noを使っているのでFormat関数で５ケタの数値に修正しています。

⑫リストボックスの２列目と３列目に、それぞれ見積日と相手先を設定しています。

⑬このタイミングで下のリストボックスに表示されているデータを削除します。

元データは消えないので問題ありません。

フォームを連動させよう

今回作ったフォームを既存のフォームと連動させておきましょう。

見積書作成フォームの「期間指定」ボタンをダブルクリックして、cmbMkikan_Clickプロシージャに以下のようにコードを書きましょう。

Private Sub cmbMkikan_Click()
    frmMitumoriSearch.Show
End Sub

Private Sub cmbMkikan_Click()

frmMitumoriSearch.Show

End Sub

これで先ほど作ったフォームと連動させることができました。

挙動を確認しよう

それでは今回作ったフォームの挙動を確認してみましょう。以下の順で確認してください。

入力フォームシート上に設置したボタンをクリック
メインメニューの見積書作成ボタンをクリック
見積書作成フォームの期間指定ボタンをクリック
見積情報検索フォームで期間指定して検索ボタンクリック

上図のように抽出できていれば成功です。

まとめ

いかがでしたか。

今回は、フィルタオプションを使って抽出したデータをリストボックスに転記する方法をご紹介しました。

リストボックスの使い方は以前にもご紹介しているので、そちらの記事も合わせて参考にしてください。

なお、今回は秀和システムより出版の「ExcelVBAユーザーフォーム＆コントロール」を大いに参考にさせていただきました。

がっつりユーザーフォームを作りたいという方におすすめの本です。

次回は、フォーム下のリストボックスに見積の詳細データを転記する方法をご紹介しますのでお楽しみに。

それでは、また次回お会いしましょう。

販売管理システム作成記事一覧～見積編～

エクセルを駆使して販売管理システムを作ってみる【元データの取り扱い方】

【販売管理システム作成】見積書を自動作成する方法①

【販売管理システム作成】見積書を自動作成する方法②

【販売管理システム作成】見積書を期間指定して作成する方法①【コードのカスタマイズ】

【販売管理システム作成】見積書を期間指定して作成する方法②【リストボックス作成】

【販売管理システム作成】見積書を期間指定して作成する方法③【リストボックス作成その２】

【販売管理システム作成】見積データ登録用のフォームを作ろう①【フォームの設定】

【販売管理システム作成】見積データ登録用のフォームを作ろう②【コンボボックスの活用】

【販売管理システム作成】見積データ登録用のフォームを作ろう③【リストボックスの活用】

【販売管理システム作成】見積データ登録用のフォームを作ろう④【エクセルシートに登録】

【販売管理システム作成】見積データ登録用のフォームを作ろう⑤【修正削除機能の実装】