みなさん、こんにちはケンケンです。
エクセルを駆使して販売管理システムを作るシリーズを紹介しています。
機能やフォームが複数あるものを作り上げるのは根気がいりますが、じっくり基礎を固めてアウトプットすれば恐れるに足りません。
できるだけ簡潔に説明するよう心掛けていますのでどうぞお付き合い下さい。
前回は、見積データ新規作成用フォームからエクセルシートにデータを転記する機能を実装しました。
思ったより簡単だったと思います。
みなさん、こんにちはケンケンです。 エクセルを駆使して販売管理システムを作成しています。 前回までで見積データをリストボックスに表示するところまで成功しました。 [sitecard subtitle=前[…]
今回からデータの修正や削除機能を実装していきます。
新規作成したもののフォーム上で修正削除できないと中途半端になってしまいますからしっかりと作り込んでいきましょう。
前回の復習
前回は、登録ボタンをクリックしたときに起こるアクションの設定をしました。
ユーザー向けに細かい箇所も設定しましたね。
基礎事項の集合ですから、前回書いたコードを復習してしっかり自分のものにしてください。
前回書いたコード
見積情報登録フォーム（frmMitumoriCreat）
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 |
Private Sub cmbTouroku_Click() If lstMeisai.ListCount = 0 Then MsgBox "明細入力してください。", vbInformation Exit Sub End If Dim res res = MsgBox("登録します。よろしいですか？", vbYesNo + vbInformation, "確認") If res = vbNo Then Exit Sub End If Dim Lrow Lrow = Sheets("見積データ一覧").Range("A" & Rows.Count).End(xlUp).Row + 1 Dim cnt For cnt = 0 To lstMeisai.ListCount - 1 Sheets("見積データ一覧").Range("A" & Lrow).Value = lstMeisai.List(cnt, 0) Sheets("見積データ一覧").Range("B" & Lrow).Value = lstMeisai.List(cnt, 1) Sheets("見積データ一覧").Range("C" & Lrow).Value = lstMeisai.List(cnt, 2) Sheets("見積データ一覧").Range("D" & Lrow).Value = lstMeisai.List(cnt, 3) Sheets("見積データ一覧").Range("F" & Lrow).Value = lstMeisai.List(cnt, 4) Sheets("見積データ一覧").Range("G" & Lrow).Value = lstMeisai.List(cnt, 5) Sheets("見積データ一覧").Range("H" & Lrow).Value = lstMeisai.List(cnt, 6) Sheets("見積データ一覧").Range("I" & Lrow).Value = lstMeisai.List(cnt, 7) Sheets("見積データ一覧").Range("J" & Lrow).Value = lstMeisai.List(cnt, 8) Lrow = Lrow + 1 Next lstMeisai.Clear Lrow = Sheets("見積データ一覧").Range("A" & Rows.Count).End(xlUp).Row txtMitumoriNo.Text = Format(Sheets("見積データ一覧").Range("A" & Lrow).Value + 1, "00000") txtTokuisaki = "" txtGoukei = "" cboTokuisaki.Enabled = True cboTokuisaki.SetFocus End Sub Private Sub lstMeisai_Change() cmbSyusei.Enabled = True cmbSakujo.Enabled = True End Sub Private Sub cmbMclose_Click() Unload Me End Sub |
メインメニュー（frmMainmenu）
|
1 2 3 |
Private Sub cmbMsinki_Click() frmMitumoriCreat.Show End Sub |
削除機能の実装
先に削除機能の実装から始めます。
理由は、修正機能よりやや作業工数が少ないからです。
しかし、工数が少なくても重要な部分ですのでしっかりインプットしてください。
削除機能実装のためにやること
- 削除ボタンをクリックするとメッセージボックスでユーザーに確認を問う
- 削除ボタンをクリックするとリストボックスの選択された行を削除する
- 合計金額の再集計をする
- リストボックスの選択を解除する
- 修正ボタン、削除ボタンを選択不能にする
削除ボタンをクリックするとメッセージボックスでユーザーに確認を問う
おなじみになりましたが、データを本当に削除して良いかをユーザーに確認するコードを書きます。
VBEを起動してフォームを表示して選択行削除ボタンをダブルクリックしClickイベントプロシージャにコードを書きましょう。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
Private Sub cmbSakujo_Click() If lstMeisai.ListIndex = -1 Then'① Exit Sub End If Dim res Dim msg Dim title msg = "選択した行を削除します。" & vbNewLine & "よろしいですか？" title = "確認" res = MsgBox(msg, vbYesNo + vbExclamation + vbDefaultButton2, title)'② If res = vbNo Then Exit Sub End If End Sub |
コードの解説
①listIndexが-1の時はプロシージャを離脱します。万が一データが選択されていない時を考慮しています。
②メッセージボックスを起動させユーザーに削除の確認をしてもらいます。
いいえ（vbNo）を回答した時はプロシージャを離脱します。
ちなみにvbNewLineは改行を意味しています。
削除ボタンをクリックするとリストボックスの選択された行を削除する
リストボックスから行を削除するときはRemoveItemメソッドを使います。
追加するときはAddItemメソッドを使っていましたね。その反対です。
cmbSakujo_Clickプロシージャにコードを追加します。
|
1 |
lstMeisai.RemoveItem lstMeisai.ListIndex |
このメソッドは引数に行番号を指定する必要があるので、ListIndexを使います。
これでリストボックスからデータを削除することができます。
合計金額の再集計をする
今のままだと、リストボックスからは削除できても、合計金額はそのまま残ってしまっています。
ですので合計金額を再計算する必要がありますね。
これには、以前データ追加ボタンで作ったコードを使い回して対応します。
|
1 2 3 4 5 6 7 8 9 |
Dim Lrow Dim goukei Dim cnt Lrow = lstMeisai.ListCount - 1 goukei = 0 For cnt = 0 To Lrow goukei = goukei + lstMeisai.List(cnt, 9) Next txtGoukei.Text = Format(goukei, "#,###") |
これで、削除後の再計算もできました。
リストボックスの選択を解除しボタン選択を無効にする
リストボックスのクリックした行を解除しておきましょう。
おきまりのListIndex=-1を使うとどの行も選択していないという認識になります。
|
1 |
lstMeisai.ListIndex = -1 |
そして、このタイミングで修正と削除ボタン選択を無効にしておきましょう。
|
1 2 |
cmbSyusei.Enabled = False cmbSakujo.Enabled = False |
ここまでできたらフォームを起動し挙動を確認します。
修正機能の実装
修正機能は削除と違って修正用のフォームを作って実装します。
難易度はそれほど高くないですが、フォーム同士でデータをやりとりしますので間違えないように気を使うところではあります。
修正機能実装のためにやること
- 修正用のフォームを作る
- 修正用フォームを見積情報登録フォームと連携する
- 見積No・見積日・得意先名を転記する
- 修正用フォームの初期値（Initialize）を設定する
- コンボボックスが変化した時の処理
- 数量テキストボックスが更新された時の処理
- 修正ボタンをクリックすると新規作成フォームのリストボックスを更新する
修正用のフォームを作る
それでは、まず修正用のフォームを作りましょう。
下図のように作ってください。コントロールはほとんど見積情報登録フォームのコピーで良いです。
VBEを起動して挿入からユーザーフォームを選択し、フォームを作成します。
そのフォームのオブジェクト名はfrmSyuseiとします。
そのフォームに以下のようにコントロールを配置していきます。
①②両テキストボックスは表示だけさせて修正は不可にしたいので、EnabledプロパティをFalseに設定しています。
③見積情報登録フォームではカラーをグレーにしていましたが、白に変更し編集不可に設定しました。
④～⑩見積情報登録フォームからコピペしています。
⑪⑫新たにコマンドボタンを設置しています。
修正実行ボタンはデフォルトで編集不可にしておきます。
修正用フォームを見積情報登録フォームと連携する
見積情報登録フォームの選択行修正ボタンと修正用フォームを連携させましょう。
Clickイベントプロシージャを作成しコードを書きます。
|
1 2 3 |
Private Sub cmbSyusei_Click() frmSyusei.Show End Sub |
これで、両フォームの連携がとれました。
見積No・見積日・得意先名を転記する
見積No・見積日・得意先名を修正用フォームに転記し、ユーザーが今どの処理の続きをしているか忘れないように視覚的に誘導していきます。
フォーム間をまたいでデータを転記させる必要があります。そのときは、フォーム名から指定していきます。
具体的にはコードをご覧ください。先ほどのcmbSyusei_Clickプロシージャに追記します。
|
1 2 3 4 5 6 |
Private Sub cmbSyusei_Click() frmSyusei.txtMitumoriNo.Text = frmMitumoriCreat.txtMitumoriNo.Text frmSyusei.txtMhiduke.Text = frmMitumoriCreat.txtMhiduke.Text frmSyusei.txtTokuisaki.Text = frmMitumoriCreat.txtTokuisaki.Text frmSyusei.Show End Sub |
お分かりかと思いますが、フォーム名から明記することでテキストボックスのデータをやり取りできます。
特定のシートのセルを指定する際にsheets(“sample”).Range(“A1”)と先にシート名を指定するのと同義です。
Public変数を使ってみる
Public変数はどのモジュールからでも呼び出せる便利な変数です。
標準モジュールに記述することで機能します。便利ではあるのですが、あまり使いすぎると頭がごちゃごちゃになってしまうので
ここぞという場面で使いましょう。
今回は、リストボックスで選択した行数を変数に格納しておいて他のフォームから呼び出せるように設定します。
標準モジュール内にPublic変数selectRowを用意します。変数名の前にPublicと表記するだけです。
|
1 |
Public selectRow |
選択行修正ボタンをクリックすると同時にリストボックス選択行を把握したいので cmbSyusei_Clickプロシージャに追記します。
|
1 2 3 4 5 6 7 |
Private Sub cmbSyusei_Click() frmSyusei.txtMitumoriNo.Text = frmMitumoriCreat.txtMitumoriNo.Text frmSyusei.txtMhiduke.Text = frmMitumoriCreat.txtMhiduke.Text frmSyusei.txtTokuisaki.Text = frmMitumoriCreat.txtTokuisaki.Text <strong>selectRow = lstMeisai.ListIndex</strong> frmSyusei.Show End Sub |
lstMeisai.ListIndexでリストボックスの選択行を取得できますので、その値を変数selectRowに格納しています。
修正用フォームの初期値（Initialize）を設定する
フォームを作ったら初期値の設定をすることを忘れないようにしましょう。
今回は、コンボボックスの初期値を設定します。
下図のようにプロシージャを作って初期値を記述していきましょう。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
Private Sub UserForm_Initialize() Dim Lrow Dim cnt Lrow = Sheets("商品一覧").Range("A" & Rows.Count).End(xlUp).Row '商品コンボボックス初期値 With cboSyouhin .ColumnCount = 2 .ColumnWidths = "30;150;0;0" .ListWidth = 180 For cnt = 2 To Lrow .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 Next End With End Sub |
以前ご紹介した、コンボボックスの初期値コードをそのままコピペしています。
このフォームのInitializeは以上です。
コンボボックスが変化した時の処理
続いてコンボボックスが変化した際の処理です。
こちらも以前書いたコードをコピペします。
cboSyouhin_Changeイベントプロシージャを作成しコードを書きます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
Dim tanni Private Sub cboSyouhin_Change() With cboSyouhin If .ListIndex = -1 Then Exit Sub End If txtSyouhin.Text = cboSyouhin.List(cboSyouhin.ListIndex, 1) txtSyouhin.BackColor = &H80000005 txtTanka.Text = Format(cboSyouhin.List(cboSyouhin.ListIndex, 2), "#,###") txtTanka.BackColor = &H80000005 tanni = cboSyouhin.List(cboSyouhin.ListIndex, 3) End With End Sub |
他のプロシージャでも使えるように変数tanniをプロシージャの上で宣言していましたね。
コード全体はコピーなので特に新しく覚えることはありません。
しかし、今どのフォームのどのプロシージャに記述しているかは意識しておきましょう。
数量テキストボックスが更新された時の処理
数量テキストボックスに変更があったときは大きく分けて２つの処理をしていました。
ひとつは、０から９以外の数値が記入できないようにする。
もうひとつは、数量が確定した時点で消費税額や合計金額まで計算する、でしたね。
これもほぼコピペです。
|
1 2 3 4 5 |
Private Sub txtSuryou_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger) If Chr(KeyAscii) < "0" Or Chr(KeyAscii) > "9" Then KeyAscii = 0 End If End Sub |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
Private Sub txtSuryou_AfterUpdate() If txtSyouhin.Text = "" Then Exit Sub End If txtKingaku.Text = Format(txtSuryou.Text * txtTanka, "#,###") If txtKingaku.Text = "" Then txtTax.Text = "" txtZeikomi.Text = "" Exit Sub End If txtTax.Text = Format(txtKingaku.Text * 0.08, "#,###") txtZeikomi.Text = Format(txtKingaku.Text * 1.08, "#,###") txtKingaku.BackColor = &H80000005 txtTax.BackColor = &H80000005 txtZeikomi.BackColor = &H80000005 <strong>cmbSyusei.Enabled = True'①</strong> End Sub |
ひとつだけ補足の部分があります。
①で修正実行ボタンをクリック可能にしています。
これで、修正の準備が完了しました。
修正実行ボタンをクリックしてリストボックスの情報を更新する
いよいよ修正実行ボタンをクリックしたときの処理をみていきます。
手順として、
- 修正用フォームから登録用フォームへ転記する
- 登録用フォームの合計金額を再計算する
- 修正用フォームを閉じる
この順番でコードを書きます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
Private Sub cmbSyusei_Click() With frmMitumoriCreat.lstMeisai '① .List(frmMitumoriCreat.selectRow, 1) = txtMhiduke.Text .List(frmMitumoriCreat.selectRow, 2) = txtTokuisaki.Text .List(frmMitumoriCreat.selectRow, 3) = txtSyouhin.Text .List(frmMitumoriCreat.selectRow, 4) = txtSuryou.Text .List(frmMitumoriCreat.selectRow, 5) = tanni .List(frmMitumoriCreat.selectRow, 6) = txtTanka.Text .List(frmMitumoriCreat.selectRow, 7) = txtKingaku.Text .List(frmMitumoriCreat.selectRow, 8) = txtTax.Text .List(frmMitumoriCreat.selectRow, 9) = txtZeikomi.Text End With Dim Lrow Dim goukei Dim cnt With frmMitumoriCreat '② Lrow = .lstMeisai.ListCount - 1 goukei = 0 For cnt = 0 To Lrow goukei = goukei + .lstMeisai.List(cnt, 9) Next .txtGoukei.Text = Format(goukei, "#,###") End With Unload Me '③ End Sub |
コードの解説
①WithからEndWithで修正用フォームにあるテキストデータを登録用フォームへ転記しています。
ここで先ほど作ったPublic変数selectRowを使います。
②合計集計のコードですね。
すでにおなじみとなっているかもしれませんが、内容しっかり理解して自分で一から書けるように練習しましょう。
③合計金額の再集計が終わったら修正用フォームを閉じます。
修正というユーザーがしたい処理は終わったはずなので気を使ってフォームを自動で閉じてあげましょう。
最後に取り消しボタンをクリックするとフォームが閉じるように設定しておきましょう。
|
1 2 3 |
Private Sub cmbTorikesi_Click() Unload Me End Sub |
アクションの確認
修正ボタンをクリックしたときのアクションの確認をしておきましょう。
下図のようになっていれば成功です。
まとめ
いかがでしたか。
今回でいよいよ見積書作成の新規作成部分は完成しました。
次回以降で登録済みのデータを修正・削除する仕組みを作っていきたいと思います。
大変かもしれまんせんが、一歩一歩積み重ねていきましょう。
それでは次回もお楽しみに！
みなさん、こんにちはケンケンです。 販売管理システムの作成を通してエクセルを学んでいくことを目指したシリーズです。 前回は、とうとう新規登録画面の完成までこぎつけました。 [sitecard subti[…]
今回書いたコード
標準モジュール
|
1 |
Public selectRow |
データの修正フォーム（frmSyusei）
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 |
Dim tanni Private Sub cboSyouhin_Change() With cboSyouhin If .ListIndex = -1 Then Exit Sub End If txtSyouhin.Text = cboSyouhin.List(cboSyouhin.ListIndex, 1) txtSyouhin.BackColor = &H80000005 txtTanka.Text = Format(cboSyouhin.List(cboSyouhin.ListIndex, 2), "#,###") txtTanka.BackColor = &H80000005 tanni = cboSyouhin.List(cboSyouhin.ListIndex, 3) End With End Sub Private Sub cmbSyusei_Click() With frmMitumoriCreat.lstMeisai .List(frmMitumoriCreat.selectRow, 1) = txtMhiduke.Text .List(frmMitumoriCreat.selectRow, 2) = txtTokuisaki.Text .List(frmMitumoriCreat.selectRow, 3) = txtSyouhin.Text .List(frmMitumoriCreat.selectRow, 4) = txtSuryou.Text .List(frmMitumoriCreat.selectRow, 5) = tanni .List(frmMitumoriCreat.selectRow, 6) = txtTanka.Text .List(frmMitumoriCreat.selectRow, 7) = txtKingaku.Text .List(frmMitumoriCreat.selectRow, 8) = txtTax.Text .List(frmMitumoriCreat.selectRow, 9) = txtZeikomi.Text End With Dim Lrow Dim goukei Dim cnt With frmMitumoriCreat Lrow = .lstMeisai.ListCount - 1 goukei = 0 For cnt = 0 To Lrow goukei = goukei + .lstMeisai.List(cnt, 9) Next .txtGoukei.Text = Format(goukei, "#,###") End With Unload Me End Sub Private Sub cmbTorikesi_Click() Unload Me End Sub Private Sub txtSuryou_AfterUpdate() If txtSyouhin.Text = "" Then Exit Sub End If txtKingaku.Text = Format(txtSuryou.Text * txtTanka, "#,###") If txtKingaku.Text = "" Then txtTax.Text = "" txtZeikomi.Text = "" Exit Sub End If txtTax.Text = Format(txtKingaku.Text * 0.08, "#,###") txtZeikomi.Text = Format(txtKingaku.Text * 1.08, "#,###") txtKingaku.BackColor = &H80000005 txtTax.BackColor = &H80000005 txtZeikomi.BackColor = &H80000005 cmbSyusei.Enabled = True End Sub Private Sub txtSuryou_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger) If Chr(KeyAscii) < "0" Or Chr(KeyAscii) > "9" Then KeyAscii = 0 End If End Sub Private Sub UserForm_Initialize() Dim Lrow Dim cnt Lrow = Sheets("商品一覧").Range("A" & Rows.Count).End(xlUp).Row '商品コンボボックス初期値 With cboSyouhin .ColumnCount = 2 .ColumnWidths = "30;150;0;0" .ListWidth = 180 For cnt = 2 To Lrow .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 Next End With End Sub |
見積情報登録フォーム（frmMitumoriCreat）
|
1 2 3 4 5 6 7 |
Private Sub cmbSyusei_Click() frmSyusei.txtMitumoriNo.Text = frmMitumoriCreat.txtMitumoriNo.Text frmSyusei.txtMhiduke.Text = frmMitumoriCreat.txtMhiduke.Text frmSyusei.txtTokuisaki.Text = frmMitumoriCreat.txtTokuisaki.Text selectRow = lstMeisai.ListIndex frmSyusei.Show End Sub |
販売管理システム作成記事一覧～見積編～
エクセルを駆使して販売管理システムを作ってみる【元データの取り扱い方】
【販売管理システム作成】見積書を自動作成する方法①【シートをコピーしてデータ転記】
【販売管理システム作成】見積書を自動作成する方法②【繰り返し処理の回数に注目】
【販売管理システム作成】見積書を期間指定して作成する方法①【コードのカスタマイズ】
【販売管理システム作成】見積書を期間指定して作成する方法②【リストボックス作成】
【販売管理システム作成】見積書を期間指定して作成する方法③【リストボックス作成その２】
【販売管理システム作成】見積データ登録用のフォームを作ろう①【フォームの設定】
【販売管理システム作成】見積データ登録用のフォームを作ろう②【コンボボックスの活用】
【販売管理システム作成】見積データ登録用のフォームを作ろう③【リストボックスの活用】
【販売管理システム作成】見積データ登録用のフォームを作ろう④【エクセルシートに登録】
【販売管理システム作成】見積データ登録用のフォームを作ろう⑤【修正削除機能の実装】
