マクロ(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列を選択した場合にマクロを実行したいわけですね。
全コード
全コードを以下に示します。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim Top ’ユーザーフォームの上端の座標’ Dim Left ’ユーザーフォームの左端の座標’ ’科目選択 12列目(L列)の13行目以降を選択している場合’ If Target.Column = 12 And Target.Row >= 13 Then 'セルA6は選択ボタンの表示' UserForm2.Show vbModeless UserForm2.Top = 200 ’ユーザーフォームの上端の座標’ UserForm2.Left = 500 ’ユーザーフォームの左端の座標’ AppActivate Application.Caption 'セルを選択した際にユーザーフォームではなく、セルの方をアクティブにする' Else UserForm2.Hide ’ユーザーフォームを非表示にする’ End If End Sub |
コード説明
プロシージャ名 Worksheet_SelectionChange(ByVal Target As Range)
の()の中にある「Target」は新たに選択したセル(移動先)のことを表します。
「Target.Column」で選択したセルの列番号
「Target.Row」で選択したセルの行番号
を表します。
IF 文を用いて
選択したセルの列番号が12(勘定科目を入力する L列)で、かつ選択したセルの行番号が13以上の場合、以下のコードにより上端200 左端500の位置にユーザーフォームを表示させています。
1 2 3 |
UserForm2.Show vbModeless UserForm2.Top = 200 ’ユーザーフォームの上端の座標’ UserForm2.Left = 500 ’ユーザーフォームの左端の座標’ |
また、このとき選択したセルに直接入力することもできるようにするために、以下のコードによりユーザーフォームではなくセルの方をアクティブにするようにしています。
1 |
AppActivate Application.Caption 'セルを選択した際にユーザーフォームではなく、セルの方をアクティブにする' |
勘定科目の列以外を選択した場合には、ユーザーフォームを非表示にしています。
1 |
UserForm2.Hide ’ユーザーフォームを非表示にする’ |
「科目選択」シートの内容をユーザーフォーム上のオプションボタンに表示する VBA コード
VBE(エディタ)上でユーザーフォームをダブルクリックすれば、コードを記述できるようになります。
オブジェクトとして「UserForm」、イベントとして「Activate」を選択しましょう。
これで「UserForm_Activate」プロシージャにコードを書くことができます。
このプロシージャに書き込んだコードはユーザーフォームがアクティブになった瞬間に実行されます。
全コード
全コードを以下に示します。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
Private Sub UserForm_Activate() '勘定科目 1列目' For i = 1 To 10 Controls("OptionButton" & i).Caption = Worksheets("科目選択").Cells(27 + i, 13) & " " & Worksheets("科目選択").Cells(27 + i, 14) Controls("OptionButton" & i).Font.Size = 9 Next i '勘定科目 2列目' For i = 11 To 20 Controls("OptionButton" & i).Caption = Worksheets("科目選択").Cells(27 + i - 10, 16) & " " & Worksheets("科目選択").Cells(27 + i - 10, 17) Controls("OptionButton" & i).Font.Size = 9 Next i '勘定科目 3列目' For i = 21 To 30 Controls("OptionButton" & i).Caption = Worksheets("科目選択").Cells(27 + i - 20, 19) & " " & Worksheets("科目選択").Cells(27 + i - 20, 20) Controls("OptionButton" & i).Font.Size = 9 Next i '勘定科目 4列目' For i = 31 To 40 Controls("OptionButton" & i).Caption = Worksheets("科目選択").Cells(27 + i - 30, 22) & " " & Worksheets("科目選択").Cells(27 + i - 30, 23) Controls("OptionButton" & i).Font.Size = 9 Next i End Sub |
コード説明
「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にしています。
1 2 3 4 5 |
'勘定科目シート 1列目(1~10)' For i = 1 To 10 Controls("OptionButton" & i).Caption = Worksheets("科目選択").Cells(27 + i, 13) & " " & Worksheets("科目選択").Cells(27 + i, 14) Controls("OptionButton" & i).Font.Size = 9 Next i |
他の列も同様の考え方で、オプションボタンの文字列を表示させています。
オプションボタンの選択で勘定科目と科目コードを表示させる VBA コード
次にオプションボタンを選択した際に、シート上に勘定科目と科目コードを表示させるコードです。
オプションボタン1であれば「OptionButton1_Click」プロシージャ、オプションボタン40であれば「OptionButton40_Click」プロシージャにコードを書きます。
1~40まで同様のコードを40個書かなければなりません。
「OptionButton1_Click」プロシージャのみを示しておきます。
他のオプションボタン2~40については「科目選択」シートの対応するセルが異なるのみで考え方は全く一緒です。
全コード
全コードを以下に示します。
オプションボタン 1~40まで別々のプロシージャになっているので少々長いですが、各プロシージャの考え方はまったく同じです。
1 2 3 4 5 6 7 8 9 10 11 12 13 |
Private Sub OptionButton1_Click() Dim Font As Font Set Font = ActiveCell.Font ActiveCell.Value = Worksheets("科目選択").Cells(28, "M") Cells(ActiveCell.Row, ActiveCell.Column + 1) = Worksheets("科目選択").Cells(28, "N") Font.Size = 11 Unload Me End Sub |
1 2 3 4 5 6 7 8 9 10 11 12 13 |
Private Sub OptionButton2_Click() Dim Font As Font Set Font = ActiveCell.Font ActiveCell.Value = Worksheets("科目選択").Cells(29, "M") Cells(ActiveCell.Row, ActiveCell.Column + 1) = Worksheets("科目選択").Cells(29, "N") Font.Size = 11 Unload Me End Sub |
1 2 3 4 5 6 7 8 9 10 11 12 13 |
Private Sub OptionButton3_Click() Dim Font As Font Set Font = ActiveCell.Font ActiveCell.Value = Worksheets("科目選択").Cells(30, "M") Cells(ActiveCell.Row, ActiveCell.Column + 1) = Worksheets("科目選択").Cells(30, "N") Font.Size = 11 Unload Me End Sub |
1 2 3 4 5 6 7 8 9 10 11 12 13 |
Private Sub OptionButton4_Click() Dim Font As Font Set Font = ActiveCell.Font ActiveCell.Value = Worksheets("科目選択").Cells(31, "M") Cells(ActiveCell.Row, ActiveCell.Column + 1) = Worksheets("科目選択").Cells(31, "N") Font.Size = 11 Unload Me End Sub |
1 2 3 4 5 6 7 8 9 10 11 12 13 |
Private Sub OptionButton5_Click() Dim Font As Font Set Font = ActiveCell.Font ActiveCell.Value = Worksheets("科目選択").Cells(32, "M") Cells(ActiveCell.Row, ActiveCell.Column + 1) = Worksheets("科目選択").Cells(32, "N") Font.Size = 11 Unload Me End Sub |
1 2 3 4 5 6 7 8 9 10 11 12 13 |
Private Sub OptionButton6_Click() Dim Font As Font Set Font = ActiveCell.Font ActiveCell.Value = Worksheets("科目選択").Cells(33, "M") Cells(ActiveCell.Row, ActiveCell.Column + 1) = Worksheets("科目選択").Cells(33, "N") Font.Size = 11 Unload Me End Sub |
1 2 3 4 5 6 7 8 9 10 11 12 13 |
Private Sub OptionButton7_Click() Dim Font As Font Set Font = ActiveCell.Font ActiveCell.Value = Worksheets("科目選択").Cells(34, "M") Cells(ActiveCell.Row, ActiveCell.Column + 1) = Worksheets("科目選択").Cells(34, "N") Font.Size = 11 Unload Me End Sub |
1 2 3 4 5 6 7 8 9 10 11 12 13 |
Private Sub OptionButton8_Click() Dim Font As Font Set Font = ActiveCell.Font ActiveCell.Value = Worksheets("科目選択").Cells(35, "M") Cells(ActiveCell.Row, ActiveCell.Column + 1) = Worksheets("科目選択").Cells(35, "N") Font.Size = 11 Unload Me End Sub |
1 2 3 4 5 6 7 8 9 10 11 12 13 |
Private Sub OptionButton9_Click() Dim Font As Font Set Font = ActiveCell.Font ActiveCell.Value = Worksheets("科目選択").Cells(36, "M") Cells(ActiveCell.Row, ActiveCell.Column + 1) = Worksheets("科目選択").Cells(36, "N") Font.Size = 11 Unload Me End Sub |
1 2 3 4 5 6 7 8 9 10 11 12 13 |
Private Sub OptionButton10_Click() Dim Font As Font Set Font = ActiveCell.Font ActiveCell.Value = Worksheets("科目選択").Cells(37, "M") Cells(ActiveCell.Row, ActiveCell.Column + 1) = Worksheets("科目選択").Cells(37, "N") Font.Size = 11 Unload Me End Sub |
1 2 3 4 5 6 7 8 9 10 11 12 13 |
Private Sub OptionButton11_Click() Dim Font As Font Set Font = ActiveCell.Font ActiveCell.Value = Worksheets("科目選択").Cells(28, "P") Cells(ActiveCell.Row, ActiveCell.Column + 1) = Worksheets("科目選択").Cells(28, "Q") Font.Size = 11 Unload Me End Sub |
1 2 3 4 5 6 7 8 9 10 11 12 13 |
Private Sub OptionButton12_Click() Dim Font As Font Set Font = ActiveCell.Font ActiveCell.Value = Worksheets("科目選択").Cells(29, "P") Cells(ActiveCell.Row, ActiveCell.Column + 1) = Worksheets("科目選択").Cells(29, "Q") Font.Size = 11 Unload Me End Sub |
1 2 3 4 5 6 7 8 9 10 11 12 13 |
Private Sub OptionButton13_Click() Dim Font As Font Set Font = ActiveCell.Font ActiveCell.Value = Worksheets("科目選択").Cells(30, "P") Cells(ActiveCell.Row, ActiveCell.Column + 1) = Worksheets("科目選択").Cells(30, "Q") Font.Size = 11 Unload Me End Sub |
1 2 3 4 5 6 7 8 9 10 11 12 13 |
Private Sub OptionButton14_Click() Dim Font As Font Set Font = ActiveCell.Font ActiveCell.Value = Worksheets("科目選択").Cells(31, "P") Cells(ActiveCell.Row, ActiveCell.Column + 1) = Worksheets("科目選択").Cells(31, "Q") Font.Size = 11 Unload Me End Sub |
1 2 3 4 5 6 7 8 9 10 11 12 13 |
Private Sub OptionButton15_Click() Dim Font As Font Set Font = ActiveCell.Font ActiveCell.Value = Worksheets("科目選択").Cells(32, "P") Cells(ActiveCell.Row, ActiveCell.Column + 1) = Worksheets("科目選択").Cells(32, "Q") Font.Size = 11 Unload Me End Sub |
1 2 3 4 5 6 7 8 9 10 11 12 13 |
Private Sub OptionButton16_Click() Dim Font As Font Set Font = ActiveCell.Font ActiveCell.Value = Worksheets("科目選択").Cells(33, "P") Cells(ActiveCell.Row, ActiveCell.Column + 1) = Worksheets("科目選択").Cells(33, "Q") Font.Size = 11 Unload Me End Sub |
1 2 3 4 5 6 7 8 9 10 11 12 13 |
Private Sub OptionButton17_Click() Dim Font As Font Set Font = ActiveCell.Font ActiveCell.Value = Worksheets("科目選択").Cells(34, "P") Cells(ActiveCell.Row, ActiveCell.Column + 1) = Worksheets("科目選択").Cells(34, "Q") Font.Size = 11 Unload Me End Sub |
1 2 3 4 5 6 7 8 9 10 11 12 13 |
Private Sub OptionButton18_Click() Dim Font As Font Set Font = ActiveCell.Font ActiveCell.Value = Worksheets("科目選択").Cells(35, "P") Cells(ActiveCell.Row, ActiveCell.Column + 1) = Worksheets("科目選択").Cells(35, "Q") Font.Size = 11 Unload Me End Sub |
1 2 3 4 5 6 7 8 9 10 11 12 13 |
Private Sub OptionButton19_Click() Dim Font As Font Set Font = ActiveCell.Font ActiveCell.Value = Worksheets("科目選択").Cells(36, "P") Cells(ActiveCell.Row, ActiveCell.Column + 1) = Worksheets("科目選択").Cells(36, "Q") Font.Size = 11 Unload Me End Sub |
1 2 3 4 5 6 7 8 9 10 11 12 13 |
Private Sub OptionButton20_Click() Dim Font As Font Set Font = ActiveCell.Font ActiveCell.Value = Worksheets("科目選択").Cells(37, "P") Cells(ActiveCell.Row, ActiveCell.Column + 1) = Worksheets("科目選択").Cells(37, "Q") Font.Size = 11 Unload Me End Sub |
1 2 3 4 5 6 7 8 9 10 11 12 13 |
Private Sub OptionButton21_Click() Dim Font As Font Set Font = ActiveCell.Font ActiveCell.Value = Worksheets("科目選択").Cells(28, "S") Cells(ActiveCell.Row, ActiveCell.Column + 1) = Worksheets("科目選択").Cells(28, "T") Font.Size = 11 Unload Me End Sub |
1 2 3 4 5 6 7 8 9 10 11 12 13 |
Private Sub OptionButton22_Click() Dim Font As Font Set Font = ActiveCell.Font ActiveCell.Value = Worksheets("科目選択").Cells(29, "S") Cells(ActiveCell.Row, ActiveCell.Column + 1) = Worksheets("科目選択").Cells(29, "T") Font.Size = 11 Unload Me End Sub |
1 2 3 4 5 6 7 8 9 10 11 12 13 |
Private Sub OptionButton23_Click() Dim Font As Font Set Font = ActiveCell.Font ActiveCell.Value = Worksheets("科目選択").Cells(30, "S") Cells(ActiveCell.Row, ActiveCell.Column + 1) = Worksheets("科目選択").Cells(30, "T") Font.Size = 11 Unload Me End Sub |
1 2 3 4 5 6 7 8 9 10 11 12 13 |
Private Sub OptionButton24_Click() Dim Font As Font Set Font = ActiveCell.Font ActiveCell.Value = Worksheets("科目選択").Cells(31, "S") Cells(ActiveCell.Row, ActiveCell.Column + 1) = Worksheets("科目選択").Cells(31, "T") Font.Size = 11 Unload Me End Sub |
1 2 3 4 5 6 7 8 9 10 11 12 13 |
Private Sub OptionButton25_Click() Dim Font As Font Set Font = ActiveCell.Font ActiveCell.Value = Worksheets("科目選択").Cells(32, "S") Cells(ActiveCell.Row, ActiveCell.Column + 1) = Worksheets("科目選択").Cells(32, "T") Font.Size = 11 Unload Me End Sub |
1 2 3 4 5 6 7 8 9 10 11 12 13 |
Private Sub OptionButton26_Click() Dim Font As Font Set Font = ActiveCell.Font ActiveCell.Value = Worksheets("科目選択").Cells(33, "S") Cells(ActiveCell.Row, ActiveCell.Column + 1) = Worksheets("科目選択").Cells(33, "T") Font.Size = 11 Unload Me End Sub |
1 2 3 4 5 6 7 8 9 10 11 12 13 |
Private Sub OptionButton27_Click() Dim Font As Font Set Font = ActiveCell.Font ActiveCell.Value = Worksheets("科目選択").Cells(34, "S") Cells(ActiveCell.Row, ActiveCell.Column + 1) = Worksheets("科目選択").Cells(34, "T") Font.Size = 11 Unload Me End Sub |
1 2 3 4 5 6 7 8 9 10 11 12 13 |
Private Sub OptionButton28_Click() Dim Font As Font Set Font = ActiveCell.Font ActiveCell.Value = Worksheets("科目選択").Cells(35, "S") Cells(ActiveCell.Row, ActiveCell.Column + 1) = Worksheets("科目選択").Cells(35, "T") Font.Size = 11 Unload Me End Sub |
1 2 3 4 5 6 7 8 9 10 11 12 13 |
Private Sub OptionButton29_Click() Dim Font As Font Set Font = ActiveCell.Font ActiveCell.Value = Worksheets("科目選択").Cells(36, "S") Cells(ActiveCell.Row, ActiveCell.Column + 1) = Worksheets("科目選択").Cells(36, "T") Font.Size = 11 Unload Me End Sub |
1 2 3 4 5 6 7 8 9 10 11 12 13 |
Private Sub OptionButton30_Click() Dim Font As Font Set Font = ActiveCell.Font ActiveCell.Value = Worksheets("科目選択").Cells(37, "S") Cells(ActiveCell.Row, ActiveCell.Column + 1) = Worksheets("科目選択").Cells(37, "T") Font.Size = 11 Unload Me End Sub |
1 2 3 4 5 6 7 8 9 10 11 12 13 |
Private Sub OptionButton31_Click() Dim Font As Font Set Font = ActiveCell.Font ActiveCell.Value = Worksheets("科目選択").Cells(28, "V") Cells(ActiveCell.Row, ActiveCell.Column + 1) = Worksheets("科目選択").Cells(28, "W") Font.Size = 11 Unload Me End Sub |
1 2 3 4 5 6 7 8 9 10 11 12 13 |
Private Sub OptionButton32_Click() Dim Font As Font Set Font = ActiveCell.Font ActiveCell.Value = Worksheets("科目選択").Cells(29, "V") Cells(ActiveCell.Row, ActiveCell.Column + 1) = Worksheets("科目選択").Cells(29, "W") Font.Size = 11 Unload Me End Sub |
1 2 3 4 5 6 7 8 9 10 11 12 13 |
Private Sub OptionButton33_Click() Dim Font As Font Set Font = ActiveCell.Font ActiveCell.Value = Worksheets("科目選択").Cells(30, "V") Cells(ActiveCell.Row, ActiveCell.Column + 1) = Worksheets("科目選択").Cells(30, "W") Font.Size = 11 Unload Me End Sub |
1 2 3 4 5 6 7 8 9 10 11 12 13 |
Private Sub OptionButton34_Click() Dim Font As Font Set Font = ActiveCell.Font ActiveCell.Value = Worksheets("科目選択").Cells(31, "V") Cells(ActiveCell.Row, ActiveCell.Column + 1) = Worksheets("科目選択").Cells(31, "W") Font.Size = 11 Unload Me End Sub |
1 2 3 4 5 6 7 8 |