営業リーダーがスタッフに対し、翌日の指示をすることがよくあります。
その指示を出す時、1つの営業先に対し、複数の人に指示するとき、一人一人にメールを手書きで書くのは大変で、一覧の画面でどのスタッフにどの営業先への指示をするかを一度に設定できると効率いいですよね。
これは、どんな作業でもリーダーから翌日の作業する指示を出すというシチュエーションで共通して言えることです。
そんな時に使えるツールを作りながら、ExcelVBAのフォームの作り方の初級編を紹介します。
VBAのフォーム
VBAのフォームとは、次のようなものです。
これは最も簡単なフォームで、文字を入力してOKボタンが押せるだけのものですね。
意外と作成するのは簡単なので、ポイントを絞ってお話していきましょう。
今回作成するのは、次のような仕組みです。
作業一覧があって、社員一覧があって、指示する日付、作業を選択し、それにあたる社員をクリックして選んでいき、OKボタンをクリックすれば一覧表に反映します。
複数人数が担当する場合には複数人数分、行が増えるわけですね。
準備
でははじめの準備ですが、次のようにExcelに入力します。
入力が大変なら作業数と社員数は3つくらいでよいです。
また、作業一覧と社員の一覧はテーブルにしてもよいと思います。
（しなくても今回の練習はできます）
VBE起動
Altキーを押したまま、F11キーを押し、VBEを起動します。
ユーザーフォーム作成
挿入メニューの中のユーザーフォームをクリックすると、ユーザーフォームが作成されます。
ユーザーフォームに名前を付ける
ユーザーフォームには名前を付けましょう。
名前をつけていないと管理しにくくて仕方なくなります。
名前は、フォームを右クリックしプロパティをクリックすることで表示されるウィンドウの「(オブジェクト名)」欄に入力します。
私は、私自身が認識しやすいので、いつも名前は日本語で入力していますが、VBAをプログラムしていて名前をCtrl+スペースキーなどで呼び出しやすく、やりやすいのは実はアルファベットです。ご自身のやりやすい方で名前を付けてください。
名前設定のついでにタイトルに表示する文字も変えてしまいます。Captionで設定します。
今回は「作業指示」とします。
これでフォームの枠は完成です。
部品を入れる
ではフォームに部品を入れていきましょう。
今回は、ドロップダウンで日付を選択、同じようにドロップダウンで作業内容をまとめたものから選択、社員の一覧からどの社員がその作業をするか複数選択できるようにしたものから選択の三つのボックスがあります。それぞれ作成していきましょう。
まず日付のボックスですが、コンボボックス挿入します。
コンボボックスはツールボックスの中にあります。
それをクリックし作業指示のフォーム上をドラッグしフォーム上にコンボボックスを配置します。
コンボボックスのような部品にもすぐに名前を付けましょう。
私は日付と名前を付けました。
同様に、作業のコンボボックスも作成します。
次はラベルを作成します。
ラベルはそのボックスが何かを表す文字です。これもツールボックスにあります。
これをフォーム上でドラックし、配置し、中の文字を変えます。
「日付」「作業」のラベルを作成します。
ラベルのオブジェクト名は私は慣れないうちは付けていたのですが、実際にはあまり意味がないので最近は付けていません。
右の方に社員の一覧表を表示したいので、フォームを広げます。
普通に四角形の図形を大きくするようにフォームの右下のポインタを広げれば大きくなります。
広げたところに社員の一覧を作ります。
まず社員のラベルを作ります。
次にリストボックスを作ります。リストボックスはドロップダウンする内容がすべて表示されているようなボックスで、単一選択の他に複数選択する時にも使えます。
リストボックスもツールボックスの中にあります。
これをフォーム上で、ドラッグして配置します。オブジェクト名は「社員」にします。
最後にOKボタンを作成します。
ボタンは、キャンセルでもOKでも、ツールボックスのボタンを使用します。
フォーム上に配置します。文字を「登録」にしましょう。オブジェクト名は「登録ボタン」にするとよいでしょう。
ここまでで部品の登録は終わりです。
フォームを表示する
フォームを表示するには、VBAでフォームを表示する命令をしなければなりません。
VBEの挿入で標準モジュールを挿入します。
ではこの右側のウィンドウに、フォームを表示するプログラム（マクロ）を作っていきましょう。
マクロ名は「作業指示開始」とします。
ですので、
Sub 作業指示開始() End Sub
と入力します。
そして、フォームの表示の動作をSubとEnd Subの間に書きます。
フォームの表示は、
フォーム名.Show
です。今回は
作業指示.Show
です。
では、このマクロを呼び出す四角形をExcelシート上に作成し、マクロ「作業指示開始」を登録します。
その四角形をクリックするだけでフォームが表示されます。
部品の中身を作成する
今のところ、部品があるだけで、その内容はそれぞれの部品には入っていないので、選択できません。
それそれ項目を入れていかなければなりませんが、その項目一つ一つをアイテムと呼びます。
アイテムは設定するというより、追加する、という感じです。
部品名.AddItem(追加するもの)
と指定します。
追加はいつでも行えるのですが、まずはフォームを開いたときにあると良いでしょう。
作業指示のフォームをダブルクリックします。
右側にフォームが表示されるので右クリックしてコードの表示をすると、そのフォームに対して行えるプログラムを書く画面が表示されます。
はじめ、UserForm_Click()になっています。
これはフォームをクリックしたら始まるマクロという意味です。
今回は、フォームが表示されたときに動くマクロにしたいので、ClickをActivateに書き換えます。Activateが表示された時、ということになります。
ではこのSubからEnd Sub間にVBAを書いていきます。
まず、日付ですが、本日より7日後までの日付から選べるようにします。
なので、今日から今日より7日後までのループをしてアイテムを追加していきます。
Dim d As Date For d = Date To Date + 7 作業指示.日付.AddItem (Format(d, "yyyy/mm/dd")) Next
日付をループするのにdという変数を使います。日付型の宣言をします。
実際にボックスに入れるのは、見やすい日付形式のものにするので、Format関数で年月日の形式にしています。そのままExcelにも入力できる形ですね。
実際にExcelシートからフォームを表示してみましょう。
本日の日付から7日後までがリストされました。
では一旦×ボタンで閉じます。
次は作業です。
作業は、シートのAからC列に入っていますね。
これを見やすい形でリストにします。
作業番号は半角4文字なので、全部で6文字分でいいと思います。
顧客は全角5文字なのですが、余裕を見て10文字になります。
内容も2文字なのですが、実際は10文字くらい入っていた方がいいと思います。
ということで、それぞれの文字の長さを揃えます。
揃えるには左からの文字数を規定できるLeft関数を使います。
今回は単なる文字数ではなく半角の文字数で指定するのでLeft関数になります。
具体的には
Left(追加アイテムの文字 & " ", 6)
とします。
その文字列に規定したい文字数と同じ長さの空白を足して、それの左から既定の文字数を取り出せば、すべて同じ文字の長さにできます。
この詰め物をすることをパディングと言います。タブ揃えみたいなものですね。
3行目からA列の入力されているセルの数の分、ループすればいいので、次のようなVBAになります。
Dim i As Long
For i = 3 To WorksheetFunction.CountA(Range("A:A"))
作業指示.作業.AddItem (<作業の内容>)
Next
ループは回数なので単純に数字ですのでiという変数でLong型にしています。
さて作業の内容は3項目あり、それぞれ文字数を規定しなければならないので、素直に書くと次のようになります。
Left(Cells(i, 1) & " ", 6) & Left(Cells(i, 2) & " ", 10 ) & Left(Cells(i, 3) & " ", 10)
これでは見づらいので、次のように_で行を分けて表記します。
1行が長くなるときはこれで分割できるのですね。
これを先ほどの日付のVBAの下に記述します。
最後の社員ですが、これもシートのE列に入っているものを使うので、3行目からE列にデータの入っているセルの個数の回数、ループします。
ループ回数はiがすでに数字として宣言してあるので流用します。
For i =3 To WorksheetFunction.CountA(Range("E:E"))
作業指示.社員.AddItem (Range("E" & i).Value)
Next
作業ではセルを参照するのにCellsを使いましたが、今回はRangeで指定しています。
どちらでもよいと私は考えますが、今回、私は、作業は3項目あるので、それもA列から指定できるので1,2,3の数値で指定したほうがわかりやすいのではないか、とくにループをするのであれば座標で指定できるCellsがいいと思いCellsで指定しました。
一方、社員は必ずE列を見るということを明示したかったので、本来ループなのでCellsを使いたいところですが、明示したいことの方が勝っているような気がするのでRangeにしました。
他人が見てわかりやすいというのが一番だと思うのですが、統一したほうがいいという考えもありますし、このあたりの選択は難しいので、その時の判断によると思います。
ではこのVBAを作業の下に入れます。
※↑社員のところのループの始まりは2になっていますが3が正解です。
これでアイテムを入れるところは完了です。
では実際に動かしてみましょう。
作業が揃うようにしたはずなのに、揃っていません。
また社員が複数選べません。
この2つを修正します。
まず、作業が揃っていないのは、フォントが等幅フォントになっていないからです。
すべてのボックスを選択して、プロパティのFontをMSゴシックに変更します。
もうひとつ、社員は、これもプロパティで、MultiSelectを1-fmMultiSelectMultiを選択します。
この、MultiSelectなんて設定があるかどうかもわからない、どうやって探したらいいのかわからない、探せたとしてどの設定を選べばいいかわからないという方は、こういう方法はいかがでしょうか。
落ち着いてプロパティを上から順に探します。そしてそれらしいものを見つけたら、その設定値も何が選択できるか見てみます。見当違いのものであれば設定値も見当違いのものが出るでしょう。
私自身、それで見分けていきます。基本は英語なので、時に和英辞書を片手に探すこともあります。あとは略語になっている場合もあるので頭文字などでも想像します。
ではこれで動作して、うまくいったことを確認しましょう。
（作業のところは結局うまく揃わないので、左からの文字数を調整する必要があるかもしれないです）
一旦×ボタンで閉じましょう。
ユーザーフォームの値をセルに登録する
ではいよいよ、登録ボタンをクリックしたときの動作を作成します。
フォームの登録ボタンをダブルクリックします。
すると、Private Sub 登録ボタン_Click()が現れます。
このPrivate SubからEnd Subの間に登録ボタンをクリックしたときの動作を書きます。
登録するのは、シートのG列からI列に、日付、作業、社員を登録します。社員が複数いたら社員の分書き込みます。
ということは、選択されている社員の数だけループします。
日付はフォームで選択された日付をそのまま入力すればいいので、日付のValueで読み込めばよいです。
作業は、コードで記録します。作業コードはフォームで選択された作業の左から6文字なのですが、空白が入っているので、Left関数で6文字切り出して、余計な空白を削除するTrim関数で処理します。
社員は、ちょっと大変です。でも原理がわかれば簡単です。
社員は今回複数を選ぶので、選んだものは社員のValueに「佐藤」「島田」「渡辺」のように複数入ることはできません。
登録されたアイテムの上から順番にSelected(0)からにTrue、Falseで入っていきます。Trueが選ばれているものです。
つまり社員の上から2番目、4番目、7番目が選ばれていると、社員.Selected(1)、社員.Selected(3)、社員.Selected(6)がTrueで他の社員.SelectedはFalseになります。
もう一つ抑えておきたいのは、リストボックスに登録されているアイテムは上から順にList(0)からに値が入っているということです。
つまり、アイテムの個数分、Selectedを調べていってTrueの時に、そのListの値がセルに書き込むべき社員名ということになります。
まず、アイテムの個数分のループをします。アイテムの個数はListCountでわかります。
順番は0から始まるのでループの始まりも１からではなく0から、その分、ListCountから1を引きましょう。
Dim i As Long For i = 0 To 作業指示.社員.ListCount - 1 Next
このForとNextの間に判定とセルに書き込むものを書いていきます。その判定式は次の通りです。
If 作業指示.社員.Selected(i) Then End If
作業指示.社員.Selected(i) = True と書いてもいいのですが、省略しています。
これはIfの書式はこういうことができるからなのです。
普通のIfの構文は次の通りですよね。
If 真の場合 Then End If
真の場合=Trueだったら、なのです。
もし、その答えがTrue、Falseのどっちかならば、= Trueを省略できるのです。成立している=Trueそのものなのですから。
それでIfとEnd Ifの間の処理を考えていきます。
今、G列に入力されているセル数の次の行のG列に日付、H列に作業の頭6文字の空白を除いたもの、I列に社員のListの今の順番iのものを入力するということになりますので、まずは、今入力する行を変数rに求めましょう。
rは行数なので、Long型にします。
r = WorksheetFunction.CountA(Range("G:G")) + 1
そしてそれぞれを入力するところを作っていきます。
Range("G" & r).Value = 作業指示.日付.Value
Range("H" & r).Value = Trim(Left(作業指示.作業.Value, 6))
Range("I" & r).Value = 作業指示.社員.List(i)
変数名iとrをLongで宣言して終わりです。
では実際に動作させて登録されるかやってみましょう。
おわりに
今回は私なりにVBAのフォームで便利なものを作る方法を比較的初心者の方にも楽しく学んでいただけるように丁寧に解説しました。
この操作は1回だけやっても身につきません。
何度も繰り返し練習しましょう。
私は大体45分で作成するのですが、2回目は20分、３回目は15分と短くなっていきます。
手順を丸暗記でもいいので、何も見ずにできるようにしておくとスキルアップにつながると思います。
本当に何も見ないでできるようになったら、それぞれの意味をもう一度読み直すと良いでしょう。
コメント