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

シェアする

2019年1月28日に ZOOM でのオンラインセミナー開催致します!

30代半ばから税理士になった体験談セミナー

The following two tabs change content below.

松井 元(まつい はじめ)

静岡県三島市の松井会計事務所に勤務する理系税理士。 文理両方のスキルの融合を考えており、このブログは以下を中心に更新している。
●税金・会計に関すること
●IT(Excel、VBAなど)を使った業務効率化
●自分のこと(考え、私生活)

さらに詳細なプロフィールはこちら
松井会計事務所へのお仕事ご依頼・お問い合わせはこちら
税金・会計の無料メルマガはじめます

マクロ(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: ユーザーフォームを表示

そして、マクロ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コードを埋め込んでいます。

zoom を使ったオンラインセミナー開催致します。
◆1/28 30代半ばから税理士になった体験談セミナー

お仕事のご依頼は、以下のリンク先にある個別のフォームよりお願い致します。
◆税務・会計 顧問
◆税務・会計のスポットコンサルティング
◆Excel シートのオーダーメイド
◆Excel、マクロ(Excel 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まで別々のプロシージャになっているので少々長いですが、各プロシージャの考え方はまったく同じです。

コード説明

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

オプションボタン1をクリック

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

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

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

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

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

勘定科目・科目コード入力

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

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

5、まとめ

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

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

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

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

zoom を使ったオンラインセミナー開催致します。
◆1/28 30代半ばから税理士になった体験談セミナー

お仕事のご依頼は、以下のリンク先にある個別のフォームよりお願い致します。
◆税務・会計 顧問
◆税務・会計のスポットコンサルティング
◆Excel シートのオーダーメイド
◆Excel、マクロ(Excel VBA) のマンツーマンレッスン
◆東亜大学通信制大学院(法学専攻) 入試対策コンサル
◆その他のスポットコンサルティング(当ブログを読み何かご相談したい事項が出てきた方に向けたサービス)

税金・会計についての無料メルマガをはじめます。配信をご希望させる方は以下のリンク先にあるフォームよりお願い致します
◆メルマガ登録
税理士試験まとめ、税法論文の書き方

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

税理士試験についてのまとめはこちらです!

税法2科目免除大学院についてのまとめはこちらです!

kindle電子書籍を出版しました。

税理士試験の税法2科目免除のために大学院で税法論文(修士論文)を書いた経験を元に、大学院生としての目線から税法論文の書き方を体系化した書籍です↓

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

本書籍の内容についてはこちらの記事をご確認下さい。

お問い合わせ・フォローボタン

———————————

よろしければフォロー下さい↓

Twitter フォローボタン:

Feedly フォローボタン:
follow us in feedly

Facebookページ:
*いいね!を押して頂くと更新情報が届きます。

レクタングル大 広告
レクタングル大 広告
関連コンテンツ



シェアする

フォローする