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

2019年9月3日
2019年9月15日
販売管理システム【見積編】

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

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

販売管理システムをシリーズで作成しています。

今回から編集機能を作る予定でしたが、その前段階として新規登録機能を作ります。

編集機能を作る前に見積データを登録できる機能を作っちゃいましょう。

1 見積情報を新規登録できるフォームを作成しよう
- 1.1 フォームを追加する
- 1.2 各種コントロールを追加する
2 フォームの初期化設定をしよう
3 まとめ

見積情報を新規登録できるフォームを作成しよう

新規作成用のフォームは細かい設定をしていきます。

オブジェクト名やCaptionの設定に注意しながら本格的なフォームを作りこんでいきましょう。

フォームを追加する

販売管理システムを作るうえで見積情報は重要です。

これから作成するフォームでデータを登録していくのできっちり作っていきましょう。

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

完成図を先にお見せしておきます。フォームのオブジェクト名とCaptionは今のうちに変更しておきましょう。

各種コントロールを追加する

完成図でお見せしたフォームを参考に各種コントロールを配置していきましょう。

量が多くて大変ですが、ていねいに作っていきましょう。

また、タブオーダーでフォーカスの順番を調整しておきましょう。

タブオーダーの設定の仕方はVBE画面から「表示」→「タブオーダー」の順で設定画面を開けます。

上図のように設定し、ユーザーに使いやすく工夫しましょう。

フォームの初期化設定をしよう

フォーム上にコントロールを配置できたら今度は、フォームを表示する前の初期値を設定しましょう。

具体的にはInitializeイベントを使用するんでしたね。

フォーム起動時に何を初期化するべきか考える

まずフォームを起動するとき、正確に言うとフォームが起動する直前に、何を設定しておけばより使いやすいフォームになるか考えてみましょう。

フォームの右上に現在時刻と日付を表示する
見積データ一覧シートを見積No項目を昇順でソートをかける
フォームの見積Noテキストボックス（txtMitumoriNo）内に新規Noを自動表示させる
見積日テキストボックス（txtMhiduke）に現在日付を自動表示させる
得意先コンボボックス（cboTokuisaki）に得意先一覧データを流し込む
商品Noコンボボックス（cboSyouhin）に商品情報を流し込む
リストボックス（lstMeisai）の初期値を設定する
コントロールの設定

今回はざっと上記の設定をします。

フォームの右上に現在時刻と日付を表示する

ぱっと見でフォームに日時が表示されていると見積日を入力しやすいのでないかと判断しました。

以下でフォーム上にリアルタイムで時刻を表示する方法をご紹介します。

まず、下図のようにフォームのInitializeイベントプロシージャを作成し、そこにコードを記載していきます。

Private Sub UserForm_Initialize()
    lblHiduke.Caption = Format(Now, "yyyy/mm/dd hh:mm")
End Sub

Private Sub UserForm_Initialize()

lblHiduke.Caption = Format(Now, "yyyy/mm/dd hh:mm")

End Sub

先ほど作ったラベル（lblHiduke）のキャプションを現在時刻になるように設定しています。

表示形式はFormat関数を使って表現します。

Format(値, 書式)
値を書式の形式で文字列に変換します。

見積データ一覧シートを見積No項目を昇順でソートをかける

このタイミングで見積データ一覧シートのA列見積Noをソートで並べ替えておくことでデータを整理しておきます。

Initializeイベントプロシージャに下記コードを追記してください。

    Worksheets("見積データ一覧").Range("A1:M1").AutoFilter
    ActiveWorkbook.Worksheets("見積データ一覧").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("見積データ一覧").AutoFilter.Sort.SortFields.Add Key:= _
        Range("A1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("見積データ一覧").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    WorkSheets("見積データ一覧").Range("A1:M1").AutoFilter

Worksheets("見積データ一覧").Range("A1:M1").AutoFilter

ActiveWorkbook.Worksheets("見積データ一覧").AutoFilter.Sort.SortFields.Clear

ActiveWorkbook.Worksheets("見積データ一覧").AutoFilter.Sort.SortFields.Add Key:= _

Range("A1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _

xlSortNormal

With ActiveWorkbook.Worksheets("見積データ一覧").AutoFilter.Sort

.Header = xlYes

.MatchCase = False

.Orientation = xlTopToBottom

.SortMethod = xlPinYin

.Apply

End With

WorkSheets("見積データ一覧").Range("A1:M1").AutoFilter

見積データ一覧シートのAからM列にフィルターをかけてA列を昇順に並べ替えています。

自動記録を使い生成されたコードを少し成形しました。

工夫した点は、1行目に設定したフィルターを最終行で解除しているところです。

フィルターやソートについて細かい説明は割愛します。

コードを実行してデータの並び替えが成功したらOKです。

フォームの見積Noテキストボックス（txtMitumoriNo）内に新規Noを自動表示させる

新規登録用のフォームなので見積Noに関しては最新のものが表示されていると便利ですね。

そこで見積Noテキストボックスに自動でNoを表示させる設定をしましょう。

    Dim Lrow 'frmMitumoriCreatの新規見積No
    Lrow = Sheets("見積データ一覧").Range("A" &amp; Rows.Count).End(xlUp).Row
    txtMitumoriNo.Text = Format(Sheets("見積データ一覧").Range("A" &amp; Lrow).Value + 1, "00000")

Dim Lrow 'frmMitumoriCreatの新規見積No

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

txtMitumoriNo.Text = Format(Sheets("見積データ一覧").Range("A" & Lrow).Value + 1, "00000")

コードは上記のようになります。

見積データ一覧シートのA列最終行を変数Lrowに格納し、一番新しい見積Noに＋１した値をFormat関数で５桁に変換したものをテキストボックスに表示しています。

直前に見積データ一覧シートにソートをかけたのがここで生きてきます。

昇順でソートをかけたおかげで、最終行は必ず作成された見積書のなかで最新のものとなっているはずですね。

正しくコードが書かれていれば下図のように表示されます。

見積日テキストボックス（txtMhiduke）に現在日付を自動表示させる

フォームに現在時刻を表示した方法とほとんど同じなのでコードを追記してアクションを確認しておきましょう。

txtMhiduke.Text = Format(Now, "yyyy/mm/dd")

1	txtMhiduke.Text = Format(Now, "yyyy/mm/dd")

得意先コンボボックス（cboTokuisaki）に得意先一覧データを流し込む

コンボボックス（cboTokuisaki）に得意先情報を流し込んでおきましょう。

得意先一覧シートは下図のように作りましたね。

コンボボックスにはA列とB列のデータを転送します。コードは以下の通りです。

    Dim cnt '得意先コンボボックス初期値
    Dim TLrow
    TLrow = Sheets("得意先一覧").Range("A" &amp; Rows.Count).End(xlUp).Row
    With cboTokuisaki
        .ColumnCount = 2
        .ColumnWidths = "30;150"
        .ListWidth = 180
        
        For cnt = 2 To TLrow
            .AddItem Sheets("得意先一覧").Range("A" &amp; cnt).Value
            .List(.ListCount - 1, 1) = Sheets("得意先一覧").Range("B" &amp; cnt).Value
        Next
    End With

Dim cnt '得意先コンボボックス初期値

Dim TLrow

TLrow = Sheets("得意先一覧").Range("A" & Rows.Count).End(xlUp).Row

With cboTokuisaki

.ColumnCount = 2

.ColumnWidths = "30;150"

.ListWidth = 180

For cnt = 2 To TLrow

.AddItem Sheets("得意先一覧").Range("A" & cnt).Value

.List(.ListCount - 1, 1) = Sheets("得意先一覧").Range("B" & cnt).Value

End With

詳しい解説は以下の記事でしていますので、細かいところまで勉強されたい方は参考にしてください。

参考記事

【ExcelVBA】ユーザーフォームを作りこむ【コンボボックスの使い方】

みなさん、こんにちはケンケンです。今回も前回に引き続き、ユーザーフォームの活用についてご紹介します。ユーザーフォームの復習をしたい方は以下をご覧ください。【ExcelVBA】ユーザーフォーム活用①[…]

下図のように表示されたら成功です。

商品Noコンボボックス（cboSyouhin）に商品情報を流し込む

続いて商品Noコンボボックス（cboSyouhin）にデータを流し込みます。

商品一覧シートは下図のように作りました。

コードは以下の通りです。

    TLrow = Sheets("商品一覧").Range("A" &amp; Rows.Count).End(xlUp).Row '商品コンボボックス初期値
    With cboSyouhin
        .ColumnCount = 2
        .ColumnWidths = "30;150;0;0"
        .ListWidth = 180
        
        For cnt = 2 To TLrow
            .AddItem Sheets("商品一覧").Range("A" &amp; cnt).Value
            .List(.ListCount - 1, 1) = Sheets("商品一覧").Range("B" &amp; cnt).Value
            .List(.ListCount - 1, 2) = Sheets("商品一覧").Range("C" &amp; cnt).Value
            .List(.ListCount - 1, 3) = Sheets("商品一覧").Range("D" &amp; cnt).Value
        Next
    End With

TLrow = Sheets("商品一覧").Range("A" & Rows.Count).End(xlUp).Row '商品コンボボックス初期値

With cboSyouhin

.ColumnCount = 2

.ColumnWidths = "30;150;0;0"

.ListWidth = 180

For cnt = 2 To TLrow

.AddItem Sheets("商品一覧").Range("A" & cnt).Value

.List(.ListCount - 1, 1) = Sheets("商品一覧").Range("B" & cnt).Value

.List(.ListCount - 1, 2) = Sheets("商品一覧").Range("C" & cnt).Value

.List(.ListCount - 1, 3) = Sheets("商品一覧").Range("D" & cnt).Value

End With

「ColumnWidths = “30;150;0;0“」

とすることで、C列の単価、D列の単位はコンボボックスには表示されませんが、データとしてあとで使うことができます。

テクニックとして覚えておきましょう。

リストボックス（lstMeisai）の初期値を設定する

リストボックスは最終的に上図のように追加したいデータを順次表示していき、登録ボタンを押下することで見積データを転記することを目標としています。

それを実現するために以下のように初期値を設定しておきます。

    With lstMeisai 'リストボックスの初期値
        .ColumnCount = 10
        .ColumnWidths = "0;0;0;60;36;40;60;60;60;60"
        .TextAlign = fmTextAlignCenter
    End With

With lstMeisai 'リストボックスの初期値

.ColumnCount = 10

.ColumnWidths = "0;0;0;60;36;40;60;60;60;60"

.TextAlign = fmTextAlignCenter

End With

コントロールの設定

ユーザーに触ってほしくないコントロールは使用不能にしたり、できることを制限しましょう。

タイミングを見計らって使用可能にし制限を解除します。

    cboTokuisaki.Style = fmStyleDropDownList'①
    cboSyouhin.Style = fmStyleDropDownList'①
    cmbTuika.Enabled = False'②
    cmbSyusei.Enabled = False'②
    cmbSakujo.Enabled = False'②
    cmbTouroku.Enabled = False'②

cboTokuisaki.Style = fmStyleDropDownList'①

cboSyouhin.Style = fmStyleDropDownList'①

cmbTuika.Enabled = False'②

cmbSyusei.Enabled = False'②

cmbSakujo.Enabled = False'②

cmbTouroku.Enabled = False'②

①fmStyleDropDownListは直接入力を禁止します。つまり、２種類あるコンボボックスはドロップダウンリストからの選択に制限します。

②フォームの下にあるボタンの内、閉じるボタン以外をクリック不可の状態にしておきます。

データがリストボックスに反映されたり、反映されたデータを選択した際にクリック可能にします。

まとめ

今回は、見積データを新規作成する際に必要なフォームの作成とその初期値の設定をしました。

細かい作業が多くて大変かもしれません。

しかし、ユーザーにとって使い勝手が良いフォームを作るためには、どうしてもある程度手間はかかってしまうので辛抱強く作っていきましょう。

今回書いたコードは以下にまとめておくのでご利用ください。

次回は、さらに新規作成用のフォームを作りこみますのでお楽しみに。

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

次回

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

みなさん、こんにちはケンケンです。販売管理システム作成をシリーズでお伝えしています。前回は、見積データの新規登録用フォームを作り初期値を設定しました。 [sitecard subtitle=前回の復習 […]

Private Sub UserForm_Initialize()
    lblHiduke.Caption = Format(Now, "yyyy/mm/dd hh:mm")
    
    Worksheets("見積データ一覧").Range("A1:M1").AutoFilter
    ActiveWorkbook.Worksheets("見積データ一覧").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("見積データ一覧").AutoFilter.Sort.SortFields.Add Key:= _
        Range("A1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("見積データ一覧").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Sheets("見積データ一覧").Range("A1:M1").AutoFilter
    
    Dim Lrow 'frmMitumoriCreatの新規見積No
    Lrow = Sheets("見積データ一覧").Range("A" &amp; Rows.Count).End(xlUp).Row
    txtMitumoriNo.Text = Format(Sheets("見積データ一覧").Range("A" &amp; Lrow).Value + 1, "00000")
    txtMhiduke.Text = Format(Now, "yyyy/mm/dd")
    
    Dim cnt '得意先コンボボックス初期値
    Dim TLrow
    TLrow = Sheets("得意先一覧").Range("A" &amp; Rows.Count).End(xlUp).Row
    With cboTokuisaki
        .ColumnCount = 2
        .ColumnWidths = "30;150"
        .ListWidth = 180
        
        For cnt = 2 To TLrow
            .AddItem Sheets("得意先一覧").Range("A" &amp; cnt).Value
            .List(.ListCount - 1, 1) = Sheets("得意先一覧").Range("B" &amp; cnt).Value
        Next
    End With
    
    TLrow = Sheets("商品一覧").Range("A" &amp; Rows.Count).End(xlUp).Row '商品コンボボックス初期値
    With cboSyouhin
        .ColumnCount = 2
        .ColumnWidths = "30;150;0;0"
        .ListWidth = 180
        
        For cnt = 2 To TLrow
            .AddItem Sheets("商品一覧").Range("A" &amp; cnt).Value
            .List(.ListCount - 1, 1) = Sheets("商品一覧").Range("B" &amp; cnt).Value
            .List(.ListCount - 1, 2) = Sheets("商品一覧").Range("C" &amp; cnt).Value
            .List(.ListCount - 1, 3) = Sheets("商品一覧").Range("D" &amp; cnt).Value
        Next
    End With
    
    With lstMeisai 'リストボックスの初期値
        .ColumnCount = 10
        .ColumnWidths = "0;0;0;60;36;40;60;60;60;60"
        .TextAlign = fmTextAlignCenter
    End With

    'コントロール設定
    cboTokuisaki.Style = fmStyleDropDownList
    cboSyouhin.Style = fmStyleDropDownList
    cmbTuika.Enabled = False
    cmbSyusei.Enabled = False
    cmbSakujo.Enabled = False
    cmbTouroku.Enabled = False

End Sub