Excel VBA のユーザーフォームのオプションボタンで Excel 現金出納帳の勘定科目を選択して入力

マクロ（Excel VBA）を組み込んだ Excel シートは自分1人が使うのであれば、自分だけが理解できる仕組みにすれば良いですが

複数人が使うとなるとそういうわけにはいきません。

Excel シートへの入力は、私個人はできる限りマウスを使わずにキーボードのみで入力を済ませたいと思っています。

一方で、Excel を触り始めて間もない人からは、視覚的に分かりやすい入力方法の方が好まれます。

今日は、マクロ（Excel  VBA）のユーザーフォームにオプションボタンを設置して選択する仕組みを紹介します。

Excel で作った現金出納帳での科目選択を例にあげますね。

1、Excel で作った現金出納帳

次のような Excel 現金出納帳に日々の現金取引を記帳しているものとします。

月ごとに、以下の各項目を入力します。

・日にち
・勘定科目（相手科目）
・科目コード
・摘要
・収入金額
・支出金額

※仮に自分が個人的に使うとすれば、勘定科目名は省き科目コードのみをキーボードで入力する仕様にします。

以下の記事で作成手順を示した Excel現金出納帳がベースとなっています。

今回、「勘定科目」と「科目コード」をオプションボタンの選択で入力できる仕様を紹介します。

具体的な使い方は、「勘定科目」の列（L列）を選択するとユーザーフォームが表示されるので、これから勘定科目を選択（オプションボタンを押す）します。

すると L列に選択した勘定科目、M列に科目コードが入力されます。

2、マクロ（VBA）を使ったExcel 現金出納帳の仕組み

ユーザーフォームのオプションボタンの表示させる

さて、どのようにしてユーザーフォームのオプションボタンに勘定科目を表示させているか説明します。

「科目選択」という名前のシートを作り、その中に勘定科目と科目コードの組合せの表を作っておきます。

1～40まで全部で40個の組合せを入力できるようにしてあります。

そして、現金出納帳のL列を選択した際にマクロAが実行し以下を行います。

そして、マクロAが実行するとそれがトリガーとなり、マクロBが実行します。

ですので、ユーザーフォーム上のオプションボタンに表示させたい勘定科目＆科目コードを変更するためには、「科目選択」シートを編集しなければなりません。

各々1～40まで並びのとおりに対応しています。

オプションボタンの選択で勘定科目と科目コードを表示させる仕組み

ユーザーフォームが表示されたらオプションボタンを1つ選択します。

オプションボタンを選択したら、マクロが実行して該当する勘定科目と科目コードが表示されます。

3、ユーザーフォームの設定

さて、現金出納帳の勘定科目の列（L列）を選択した際に表示されるユーザーフォームの設定は、VBA Project の「ユーザーフォーム」を使います。

VBA Project のどこかで右クリックを押しましょう。

そして、「挿入」で「ユーザーフォーム」を選択します。

するとユーザーフォームが挿入され、またツールボックスが表示されます。

ツールボックスは、ユーザーフォームの上にオプションボタンやラベルなど（コントロールという）を配置するためのものです。

なお、一度ツールボックスを消して（×ボタンで）しまった場合に再表示させるためには、ユーザーフォームを選択した状態で「表示」の「ツールボックス」を選択すればできます。

今回は、ユーザーフォームの縦横の幅を広げてオプションボタンを全部で40個（縦10個 × 横4個）配置しています。

先にも書いたように、現金出納帳の画面で「勘定科目」の列（L列）を選択すれば、このユーザーフォームが表示され、このオプションボタン1つ1つに「科目選択」シートで設定した勘定科目名とコードが表示されます。

4、Excel VBA コード

さて、VBA コードについて説明します。

勘定科目の列を選択するとユーザーフォームを表示する VBA コード

イベントプロシージャを使います。

イベントプロシージャは、例えば「ユーザーフォームのオプションボタンを押した時」など何かの操作をトリガーにしてコードを実行します。

オブジェクトとイベントを選択します。

イベントプロシージャの設定の仕方は、VBA Project で設定したいシート名ををダブルクリックすればできるようになります。

「現金出納帳」シートのイベントプロシージャとして「Worksheet_SelectionChange」プロシージャを使っています。

今回は「現金出納帳」シートのイベントプロシージャ「Worksheet_SelectionChange」プロシージャに VBAコードを埋め込んでいます。

オブジェクトは「Worksheet」とイベントは「SelectionChange」を選択すればそのプロシージャにコードを書き込めます。

このプロシージャの中に書くVBAコードは、ワークシート上のセルの選択を変更したときに実行します。

————————————

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

 

End Sub

————————————

上記 Private Sub ～ End Sub の間にコードを書くわけです。

（）内の「Target」は選択しているセルを表しています。

この Target について特に何もしなければ、どのセルに選択を変更してもマクロが実行します。

今回の場合は、セルの選択を変更して L列を選択した場合にマクロを実行したいわけですね。

全コード

全コードを以下に示します。

コード説明

プロシージャ名　Worksheet_SelectionChange(ByVal Target As Range)

の（）の中にある「Target」は新たに選択したセル（移動先）のことを表します。

「Target.Column」で選択したセルの列番号

「Target.Row」で選択したセルの行番号

を表します。

IF 文を用いて

選択したセルの列番号が12（勘定科目を入力する L列）で、かつ選択したセルの行番号が13以上の場合、以下のコードにより上端200 左端500の位置にユーザーフォームを表示させています。

また、このとき選択したセルに直接入力することもできるようにするために、以下のコードによりユーザーフォームではなくセルの方をアクティブにするようにしています。

勘定科目の列以外を選択した場合には、ユーザーフォームを非表示にしています。

「科目選択」シートの内容をユーザーフォーム上のオプションボタンに表示する VBA コード

VBE（エディタ）上でユーザーフォームをダブルクリックすれば、コードを記述できるようになります。

オブジェクトとして「UserForm」、イベントとして「Activate」を選択しましょう。

これで「UserForm_Activate」プロシージャにコードを書くことができます。

このプロシージャに書き込んだコードはユーザーフォームがアクティブになった瞬間に実行されます。

全コード

全コードを以下に示します。

コード説明

「UserForm_Activate」プロシージャはユーザーフォームがアクティブになった際に動作するイベントです。

ちなみに「UserForm_Initialize」はユーザーフォームの初期値を設定するイベントですが、これを使ってもうまくできませんでした。。理由はよく分かりません。

Caption プロパティはオプションボタンに表示される文字列を指定するものです。

オプションボタンのそれぞれに表示される文字列を、「科目選択」シートから取り出しています。

オプションボタンの一番左側の列のオプションボタンの文字列は以下のようにして指定します。

Controls(“OptionButton” & 1).Caption

Controls(“OptionButton” & 2).Caption

・

・

・

Controls(“OptionButton” & 10).Caption

これらを Controls(“OptionButton” & i).Caption として、変数 i を1~10 まで変化させることで

一番左側の列のオプションボタンに「科目選択」シートの一番左側の列の「勘定科目」Worksheets(“科目選択”).Cells(27 + i, 13)と「科目コード」Worksheets(“科目選択”).Cells(27 + i, 14)を入れ込んでいます。

また、同時にユーザーフォームに表示されるフォントのサイズ Controls(“OptionButton” & i).Font.Size を9にしています。

他の列も同様の考え方で、オプションボタンの文字列を表示させています。

オプションボタンの選択で勘定科目と科目コードを表示させる VBA コード

次にオプションボタンを選択した際に、シート上に勘定科目と科目コードを表示させるコードです。

オプションボタン1であれば「OptionButton1_Click」プロシージャ、オプションボタン40であれば「OptionButton40_Click」プロシージャにコードを書きます。

1～40まで同様のコードを40個書かなければなりません。

「OptionButton1_Click」プロシージャのみを示しておきます。

他のオプションボタン2～40については「科目選択」シートの対応するセルが異なるのみで考え方は全く一緒です。

全コード

全コードを以下に示します。

オプションボタン 1～40まで別々のプロシージャになっているので少々長いですが、各プロシージャの考え方はまったく同じです。

コード説明

「OptionButton1_Click」プロシージャはオプションボタン1をクリックした際に動作するイベントです。

以下で変数の定義をしています。

ActiveCell.Font はアクティブなセルのフォントを意味します。

勘定科目の列（L列）を選択してユーザーフォームが出てきた後にオプションボタンを選択すると、このマクロが実行するので、今回の場合はアクティブなセル（ActiveCell）は必ず L列のセルになります。

以下で、選択してアクティブになっているセルに「勘定科目」シートのセル「M28」の中身を入れ込んでいます。

また以下でアクティブになっているセルの1つ右側のセルに「勘定科目」シートのセル「N28」の中身を入れ込んでいます。

以下でフォントのサイズを 11 にしています。

以下でユーザーフォームを閉じています。  

5、まとめ

Excel 現金出納帳の科目入力について、Excel VBA のユーザーフォームにオプションボタンを設置して選択する仕組みについて説明しました。

ユーザーフォームは感覚的に分かりやすいので、複数の人が使うシステムを作るうえで有用です。

もちろん人によってユーザーフォームが表示された方が良いと思う人とそうではない人がいますが、Excel を使い始めたばかりの人ほど感覚的に分かりやすい仕組みが必要になると思います。

是非参考にして頂ければと思います。