仕事でつまづくと「普段の学びが大事だな」と思ったりはするものの、忙しくて余裕がないというのが現実ですよね。
(*’ω’*)σ それなら時間を捻出できる方法を考えよう！…から時短見積フォームが誕生しました。
▼ 本記事は超速見積フォーム作成の後編です
- 1通60秒で完成する見積フォーム前編
＞＞ https://mushroom-blog.net/excel-vba-mitsumori1/
全体の構想とレイアウト・必須となる価格反映のマクロ等について解説 - 1通60秒で完成する見積フォーム後編
補助的な機能に関するマクロの解説
ユーザーフォームを用いた汎用性の高いカスタマイズ方法
現代の人類は疲れ切っています。時短ツールに助けてもらいましょう。
単純作業は時短して、自分のために大切に使える時間を増やしたいですね。
送料ボタン用マクロ｜商品以外の費用計上
送料と単価が入力されて、クリックするごとに数量が1づつ増えて自動計算されます。
- 既に送料が記載されていたらカウントアップ、記載がなければ送料を入力
- 価格転記エリアを参照して送料の単価を入力
- 送料単価×数量の計算
以上の3段構造でマクロを書きます
通常送料は「1」が多いかと思いますが、2梱包に分かれる場合等を想定してカウントアップできる仕様にしてあります。
サンプルプロシージャ
Sub 送料()
Dim FoundCell As Range ‘送料のセルなのでRangeオブジェクト
Dim 空欄セル As Range ‘空欄のセルなのでRangeオブジェクト
Set FoundCell = Range(“M12:P27”).Find(“送料”) ‘仕様の列Mから送料を検索
Set 空欄セル = Range(“S11:S27”).Find(“”) ‘数量の列Sから空欄のセルを検索
If Not FoundCell Is Nothing Then ‘送料があればS列をカウントアップ
Range(“S” & FoundCell.Row) = Range(“S” & FoundCell.Row) + 1
ElseIf FoundCell Is Nothing Then ‘送料が無ければ
空欄セル.Offset(0, -6) = “送料” ‘空欄のセルを基準にOffSetで各項目を入力
空欄セル.Value = “1”
空欄セル.Offset(0, 1).Value = “式”
End If
Dim i As Integer
For i = 13 To 27 ’13行目から27行目に繰り返し処理
If Cells(i, “M”) = “送料” Then ‘仕様のセルが送料ならば価格表参照でW列に単価を入力
Cells(i, “W”) = Application.VLookup(Cells(i, “M”), Range(“AL17:AO100”), 2, False)
End If
Next
Dim j As Integer
For j = 13 To 27 ’13行目から27行目に繰り返し処理
If Cells(j, “M”) = “送料” Then ’仕様のセルが送料ならば
Cells(j, “Z”) = Cells(j, “S”) * Cells(j, “W”) ‘金額のセルZは、数量のセルS×単価のセルW
End If
Next
End Sub
商品用マクロと似ていますが、送料は1行だけ入力されるプロシージャになっています。
書き変えれば「手数料」とか「延長料」にも使いまわせます。
S列は上から順に必ず何らかの数字が入るので、S列を基準にした空欄の判定で次に入力する位置が決まるようになっています。
月数ボタン用マクロ｜レンタル月数のカウントアップ
※販売見積の場合は必要のない機能です。
- ”レンタル料”の文字列があれば月数が入力される
- 1クリックごとに1月ずつカウントアップする
- 月数×金額の計算を行う
以上の順に処理されるマクロを書きます。
サンプルプロシージャ
Sub 月数カウンタ()
Dim j As Integer ‘変数の宣言
For j = 13 To 27 ’13行目から27行目に繰り返し処理
If Cells(j, “M”) = “レンタル料” Then ‘M列仕様のセルがレンタル料ならば
Cells(j, “Q”) = Cells(j, “Q”) + 1 ‘Q列月数のセルをカウントアップ
End If
Next
Dim i As Integer ‘変数の宣言
For i = 13 To 27 ’13行目から27行目に繰り返し処理
If Cells(i, “Q”) = “” Then ‘Q列月数のセルが空欄ならば
Cells(i, “Z”) = Cells(i, “S”) * Cells(i, “W”) ‘金額のセルZは、数量のセルS×単価のセルW
ElseIf Cells(i, “Q”) <> “” Then ‘Q列月数のセルが空欄ではなければ
Cells(i, “Z”) = Cells(i, “S”) * Cells(i, “W”) * Cells(i, “Q”)
End If ‘金額の列セルZは、数量のセルS×単価のセルW×月数のセルQ
Next
End Sub
今回は列を指定した書き方ですが「もし左隣のセルがレンタル料ならば…」という書きかたでも大丈夫です。
しっかりとレンタル料だけが加算されていきます。
リバースボタン用マクロ｜あると便利なチョイ消し
クリックミスした時に1行だけ削除できます。
あくまで片手でクリックするだけという手軽さを追求した機能です。
サンプルプロシージャ
Sub 最終行削除()
Dim 消したいセル As Range ‘Rangeオブジェクトの宣言
Set 消したいセル = Range(“S12”).End(xlDown) ‘S12セルを起点にデータの下端が消したいセル
消したいセル.Offset(0, -15).Value = “” ‘表の消したいセルと同じ行を空欄にしていく
消したいセル.Offset(0, -6).Value = “”
消したいセル.Offset(0, -2).Value = “”
消したいセル.Offset(0, 1).Value = “”
消したいセル.Offset(0, 3).Value = “”
消したいセル.Offset(0, 6).Value = “”
消したいセル.Offset(0, 9).Value = “”
消したいセル.Value = “”
End Sub
これについてはもう少し良い書き方があると思って試してみたのですが、上手くいきませんでした。とりあえずこの状態で使っています。改善できたらリライトしますね。
クリアボタン用マクロ｜図形も同時に削除
よくあるデータクリアのマクロですが、前半は図形（QRコード）を削除するプロシージャになっています。図形の挿入をしないのであれば、後半のセルをクリアする部分だけ使用してください。
サンプルプロシージャ
Sub データクリア()
Dim Obj As Object
For Each Obj In ActiveSheet.Shapes ‘アクティブシートの図形に
If Not Intersect(Obj.TopLeftCell, Range(“V7:Y11”)) Is Nothing Then
Obj.Delete ‘左上の角がセルV7からY11に合った図形があれば消す
End If
Next
Range(“D13:AI29,D3:L4,P3:R4,D30:L30,D5:N5,D7:N7,B14:B30,AE11,P5,O10”).Select
Selection.ClearContents ‘空欄にしたいセルを選んでクリア
MsgBox “この案件、決まりますように゜(‘ω’)ノ・。゜”
End Sub ‘ちょっと優しいメッセージが表示されます
SAVEボタン用マクロ｜名前をつけて保存までワンクリックで可能
「名前を付けて保存」という地味に面倒な作業から解放されます。
保存先は デスクトップにすることも可能ですが少しややこしいコードが必要なので、ここはサクッとCドライブにフォルダを作ってショートカットをデスクトップに貼るのが正解です。
サンプルプロシージャ
Sub SAVE()
ActiveWorkbook.SaveCopyAs Filename:=”c:\おみつもり\” & Range(“C1”) & “ _
” & Range(“D3”) & “” & Range(“D5”) & “” & Range(“D7”) & “” & Range(“P3”).Text & “.xlsm”
‘見積番号、顧客名、件名、納品場所、宛名が名前になって保存される
‘保存先はCドライブの「おみつもり」という名前のフォルダに設定してある
MsgBox “OK(‘ω’)ノ保存したで！” & vbCrLf & “おつかれさん！”, vbInformation
‘無事保存できたと分かりやすいようにメッセージボックス表示
Range(“C1”) = Range(“C1”) + 1 ‘見積番号を1カウントアップ
ActiveWorkbook.SAVE ‘見積番号がカウントアップされた状態で上書き保存
End Sub
ワンクリックでこんな感じに保存されます↑
見積番号、顧客名、件名、納品場所、宛名がアンダーバーでつながって名前になります。
備考ボタン用マクロ｜定型文もセレクト型も用意
お決まりの備考がある場合は2～3パターン定型備考があると便利です。
見積った商品のラインナップに応じてコメントを選択したい場合は、ユーザーフォームを使ったセレクト備考がオススメです。
定型備考
サンプルプロシージャ
Sub 定型備考A()
Range(“D28”).Value = “※製品都合上、キャンセルや返品は致しかねます”
Range(“D29”).Value = “※オプション費用は含まれておりません”
Range(“D30”).Value = “※その他記載無き事項は別途ご請求となります”
End Sub
あとはこのマクロを対応する備考ボタンに設定して完了です。
ボタン位置はどこでも良いのですが、商品押して月数押して・・の流れでその下に並べています。押し忘れない位置に配置してくださいね。
セレクト備考
こちらはユーザーフォームを利用します。
ダイアログボックス的なものを自分で造作できる楽しい機能です。
エディターのフォーム→挿入→ユーザーフォームと進んで、新規フォームを表示します。
初めは味気ない板1枚です。
ツールボックスを使ってコマンドボタンとコンボボックスを配置します。
ツールボックスが出てこない場合は表示タブの中にあります。
D28,D29,D30セルに備考を表示したいので、コンボボックスは3個配置。
決定時のボタンとしてコマンドボタンを1個配置しました。
フォームを右クリック→プロパティで、色やキャプションの編集ができます。
プロシージャを書く場所が変わります。
今までは標準モジュールでしたが、ユーザーフォーム内に書きます。
イベントプロシージャと言って、ボタンに設定しなくても特定の状況下で起動します。
コンボボックスに格納するコメントを書きます。
表示させたい内容に応じてボックス1から3まで同じでもいいし、変えても良いです。
サンプルプロシージャ
Private Sub UserForm_Initialize()
With ComboBox1
.AddItem “※設置・組立費用は含まれておりません”
.AddItem “※ご注文前に付属の説明書をご確認下さい”
.AddItem “※商品到着時に破損や傷が無いかご確認ください”
.AddItem “※納期はご注文後1週間程度です”
.AddItem “※その他記載無き事項は別途ご請求となります”
.AddItem “※カスタマイザーは付属しておりません”
.AddItem “※カラー変更は別途費用となります”
End With
With ComboBox2
.AddItem “※設置・組立費用は含まれておりません”
.AddItem “※ご注文前に付属の説明書をご確認下さい”
.AddItem “※商品到着時に破損や傷が無いかご確認ください”
.AddItem “※納期はご注文後1週間程度です”
.AddItem “※その他記載無き事項は別途ご請求となります”
.AddItem “※カスタマイザーは付属しておりません”
.AddItem “※カラー変更は別途費用となります”
End With
With ComboBox3
.AddItem “※設置・組立費用は含まれておりません”
.AddItem “※ご注文前に付属の説明書をご確認下さい”
.AddItem “※商品到着時に破損や傷が無いかご確認ください”
.AddItem “※納期はご注文後1週間程度です”
.AddItem “※その他記載無き事項は別途ご請求となります”
.AddItem “※カスタマイザーは付属しておりません”
.AddItem “※カラー変更は別途費用となります”
End With
End Sub
コマンドボタン用のプロシージャを書きます。
サンプルプロシージャ
Private Sub CommandButton1_Click()
Range(“D28”).Value = ComboBox1.Value ‘セルD28の値はコンボボックス1の値
Range(“D29”).Value = ComboBox2.Value ‘セルD29の値はコンボボックス2の値
Range(“D30”).Value = ComboBox3.Value ‘セルD30の値はコンボボックス3の値
Unload UserForm1 ‘ユーザーフォーム1を引っ込める
End Sub
ユーザーフォームを呼び出す
標準モジュールに以下のコードを書いて、セレクト備考のボタンにマクロを設定します。
呼び出しのマクロは標準モジュールに書くという点に注意してください。
サンプルプロシージャ
Sub myform1()
UserForm1.Show ‘ユーザーフォーム1を呼び出す
End Sub
UserFormの後の数字は、プロパティで決まっています。
呼び出したいフォームの数字を見て呼び出しのマクロを設定して下さい。
では呼び出してみます。セレクト備考ボタンをクリック。
きちんとコンボボックスに備考の文言が格納されています。
表示したいコメントを選んだらコマンドボタンで決定です。
コメントが反映されてユーザーフォームが引っ込みました。
QRコード挿入ボタン用マクロ｜画像挿入もワンクリック
QRコードをスクリーンショットでシートに挿入します。
（スクショは、挿入タブ→スクリーンショット→画像の領域→ドラッグで範囲選択です）
前編でも紹介しましたがURLからQRコードが作れるサイトは ▶こちらから
取り込んだ画像には仮の名前が付いているので、分かりやすい名前に変更します。
ホームページなのでHPです。
必要なQRコードが揃ったら、適度な大きさに揃えて印刷範囲外に並べておきます。
大きさは印刷した時のバランスを考えてお好みで大丈夫です。
画像挿入のマクロを設定する際に画像の左上の角を○○セルと合わせて挿入というプロシージャを使うので、それを意識してサイズ決定すると収まりが良くなります。
心配な方は実際に画面を携帯で読み込んでテストしてみてください。
ちなみにFAXして多少画像が荒くなってもQRは読み込みできます。テスト済みです。
サンプルプロシージャ
Sub HP()
ActiveSheet.Shapes(“HP”).Copy ‘余白に並べてあるHPQRの画像をコピー
ActiveSheet.Paste Destination:=Range(“V7”) ‘セルV7に左上角を合わせて貼り付け
Range(“O10”).Value = “製品情報はこちらから→” ‘セルO10に「製品情報はこちらから」
End Sub
余白にあるQR画像を移動ではなくコピペします。
これならデータクリアしても何度でも挿入できます。
図形の挿入位置とデータクリアの図形位置は揃えておきましょう。
顧客リスト連携｜宛名とFAX番号もクリックだけで挿入
ユーザーフォームを用意
・リストを表示するユーザーフォームを作ります。大きなリストボックスを1つ配置します。
テーブルを用意
・顧客リストをテーブルにして任意のテーブル名を設定しておきます。
テーブルをクリック→テーブルデザインタブ→左端に名前を入力する場所が出ます。
・リストから選択した客先が一度仮入力されるセルを決めておきます。私はAX3にしました。
ユーザーフォームのプロシージャは標準モジュールではなく
ユーザーフォーム内に書きます。セレクト備考のフォームを作成した時と同じです。
リストにテーブルを格納
サンプルプロシージャ
Private Sub UserForm_Initialize()
Dim Tb As ListObject ‘Tb(テーブル)はリストオブジェクトです
Set Tb = ActiveSheet.ListObjects(“お得意様リスト”)
‘Tbはアクティブシートの「お得意様リスト」というリストとします
seq = Tb.DataBodyRange ‘seqはTbのデータです（見出しは除外）
ListBox1.List = seq ‘リストボックス1のリストはseqです
End Sub
リストでダブルクリックした顧客を見積に反映
サンプルプロシージャ
Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Range(“AX3”).Value = ListBox1.List(ListBox1.ListIndex, 0)
‘セルAX3はリストボックス1でダブルクリックした値が入る
Range(“D3:L4”) = Application.VLookup(Range(“AX3”), Range(“AX7:BC300”), 3, False)
‘見積の宛名D3:L4セルはAX3セルを参照してリストの3列目の値
Range(“P5”) = Application.VLookup(Range(“AX3”), Range(“AX7:BC300”), 5, False)
‘FAX番号P5セルはAX3セルを参照して5列目の値
Unload UserForm5 ‘ユーザフォーム5を引っ込める
End Sub
ユーザーフォームにプロシージャを書くときに気を付けるのが「オブジェクト名」です。
必ずプロパティに表示されるオブジェクト名を使用してください。
たくさんユーザーフォームを作っているとツールの数だけオブジェクト名が発生します。
少しややこしいですが、正確に記載しないとうまく起動しないので注意が必要です。
ユーザーフォーム呼び出しボタン
サンプルプロシージャ
Sub myform5()
UserForm5.Show
End Sub
フォームを呼び出すボタンは標準モジュールに書いてボタンに設定します。
吹き出し型のボタンにしてみました。
印刷範囲内に設置しても、図形の書式→サイズの右下小矢印→プロパティの
「オブジェクトの印刷をする」のチェックを外せば印刷されなくなります。
完成しました！｜まとめ
始めは「そんなのできるの？」と笑っていた同僚も、完成品を愛用してくれています。
私の勤務先は全国に拠点があるのですが、他の支店でも使っている人がチラホラ笑。
最初に掲げた目標を振り返ってみます。
片手で簡単に・・・できます！クリックするだけで片手間に完成。
ミスが発生・・・しにくい！マクロで必ず価格表通りの金額が入ります。
秒速で対応・・・できます！客先から「嘘みたいに早い」と好評です。
QRコード・・・入ります！微力ながらHPへの集客にも役立ってくれてます。
カスタマイズ性・・・あります！ボタン配置で自由自在。拡張性も抜群。
後輩も・・・使いやすい！不慣れでもクリックだけなので簡単。
ちょっと・・・楽しい！みんな「これおもしろー」って言ってくれます。
もう1年以上使っていて不具合は発生していないので大丈夫かとは思いますが
完全に同じフォームでない限りプロシージャの書き換えが必要なので
参考程度にとらえていただけたらと思います。
実装前には必ずテスト期間を設けて、十分な動作確認を行ってください。