スポンサーリンク
Kindle本出版しました！
　理系出身の元エンジニアが『税理士』になった方法

　大学院での税法論文の書き方

Excel VBA のユーザーフォームのオプションボタンで Excel 現金出納帳の勘定科目を選択して入力、Excel現金出納帳（マクロ付）を無料提供！

2018.07.19
このエントリーをはてなブックマークに追加

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

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

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

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

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

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

スポンサーリンク

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

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

現金出納帳

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

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

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

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

My タックスノロジー
 
手書きをやめるための超簡単なExcel（エクセル）現金出納帳の作り方
https://my-tax-nology.com/how-to-make-a-super-easy-excel-cash-book-to-stop-handwriting
会社や個人事業は、日々現金の取引があります。この現金の取引は従来は、入出金伝票に手書きで記帳した後、補助簿の現金出納帳に手書きで転記する記録方法が多くとられていました。今は直接会計ソフトに入力する人が多いと思います。また、Excelで作成した現金出納帳に記録したデータを、CSVファイルに加工して会計ソフトに取り込むというやり方もあります。今日は、まず伝票や現金出納帳への手書きの業務をやめたいという方に向けて、Excel で簡単に現金出納帳を作る方法を紹介します。計算に関数は使わず、足し算と引き算のみで作れ...
My タックスノロジー
 
Excel（エクセル）現金出納帳 印刷・PDF化したときの見栄えを良くする
https://my-tax-nology.com/improve-the-appearance-of-excel-cash-book-when-converted-to-pdf
アイキャッチ画像は、コメダ珈琲の期間限定メニュー　ミニチョコノワールです（^^）さて、Excel で現金出納帳を簡単に作る方法について、こちらの記事↓で紹介しました。こんな感じ↓の現金出納帳ができあがっておりました。金額についてはD列に収入金額、E列に支払金額を入力し、F列に計算された差引残高が表示されます。上の記事の最後でも書いたように、この現金出納帳は見栄え上よくないところがあります。それはF列です。D列（収入金額）、E列（支払金額）へ入力がされていない場合でもF列に差引残高が表示されてしまうので、これを...

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

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

現金出納帳の科目選択

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

勘定科目入力

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

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

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

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

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

科目選択シート

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

マクロA：　ユーザーフォームを表示

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

マクロ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列を選択した場合にマクロを実行したいわけですね。

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_Activate」プロシージャはユーザーフォームがアクティブになった際に動作するイベントです。

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

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

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

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

Controls(“OptionButton” & 1).Caption

Controls(“OptionButton” & 2).Caption

・

・

・

Controls(“OptionButton” & 10).Caption

オプションボタン1-10

これらを 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まで別々のプロシージャになっているので少々長いですが、各プロシージャの考え方はまったく同じです。