前回の記事で金額を入力するためのワークシートの作成終わりました。
今回の記事では、「口座間で金額の移動があった時」に使用する機能を作成していきます。
※Excel家計簿を作成した方法についての記事の3つ目になります。その他の記事は以下にまとめています。
今回の記事で実装する機能について
今回家計簿に追加する機能ですが、例えば「三井住友銀行から三菱UFJ銀行に振り込みを行った」という操作を行う時

上記のようなダイアログで入力を行えるようにします。
また、上記操作を実行後は各シートに入出金の記録を行います。
↓出金を行った銀行のシート

↓入金を行った銀行のシート

また、今回の家計簿では現金、電子マネー、クレジットカードについても口座として扱う仕様にしています。
そのため、
「電子マネーに対してチャージを行う」
→(出金元)現金 (入金元)電子マネー
「クレジットカードの引き落とし日」
→(出金元)引き落とし先の銀行 (入金元)クレジットカード
という設定で操作を行う形になります。
口座振替用のフォームを作成する
前記事で作成した「新規口座用フォーム」と同様に、口座振替用のフォームの作成を行います。
オブジェクト名は「frmHurikae」、キャプションは「振替」としました。

レイアウトは以下のようにしました。

出金元口座と入金先口座は「コンボボックス」で作成し、既存の口座シートの値を選択肢として表示するようにします。
各オブジェクトのオブジェクト名は以下のように設定します。
- 出金元口座:cmbShukin
- 入金先口座:cmbNyukin
- 金額:txtKingaku
- 決定ボタン:cmdKettei
- キャンセルボタン:cmdCancel
フォーム上の各イベントの動作を実装する
レイアウトが完成したら、ボタンの動作などのイベントについてVBAで実装を行っていきます。
「frmHurikae」を右クリックし、コードの表示からプログラムを記載していきます。
キャンセルボタンのコード作成
これは、前記事で書いたものと同じで、何もせずにフォームを閉じるだけのコードとします。
'キャンセルボタンクリック時
Private Sub cmdCancel_Click()
'フォームをアンロードする
Unload frmHurikae
End Sub
金額入力時の妥当性チェック
金額のテキストボックスに値が入力されたときに、「数値以外が入力された場合は、入力をキャンセルする」というものを実装したいと思います。
金額の列に数値以外の文字が入力されている場合、足し算や引き算でバグるので、入力値の妥当性チェックを実装してみることにしました。
作成したコードが以下になります。
'金額入力時
Private Sub txtKingaku_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
'入力された値が数値以外であればキー入力をキャンセルする
If Chr(KeyAscii) < "0" Or Chr(KeyAscii) > "9" Then
KeyAscii = 0
End If
End Sub
オブジェクト名+_KeyPressという関数名とすることで、キーボードから値が入力された時に実行される関数となります。
引数のKeyAsciiに入力された文字のアスキーコードが格納されています。
このアスキーコードが数値の0より小さい、もしくは数値の9より大きい場合は、「数値の0~9以外の入力」ということになります。その場合に入力をキャンセルします。
※「KeyAscii = 0」とすると割り当てられる文字がなくなるのでキャンセルとなる。
フォームロード時、コンボボックスの選択肢を作成する
出金元口座と入金先口座の選択肢について、現在のワークブックに存在する口座名をもとに作成を行います。
作成したコードが以下となります。
'フォームロード時
Private Sub UserForm_Initialize()
'出金口座のコンボボックスの選択肢を作成する
Call addKoza(cmbShukin)
'入金口座のコンボボックスの選択肢を作成する
Call addKoza(cmbNyukin)
End Sub
'コンボボックスの選択肢を作成する
Private Sub addKoza(cmb As ComboBox)
'ワークシートをループさせる
For i = 1 To ThisWorkbook.Worksheets.Count
Dim sh As Worksheet
Set sh = ThisWorkbook.Worksheets(i)
'口座のテンプレートから作成されているシートの場合
If sh.CodeName Like "shKoza*" Then
'選択肢に追加する
cmb.AddItem sh.Name
End If
Next i
End Sub
前回の記事からの引用ですが、今回の家計簿では口座のシートが必ず「shKoza」で始まります。
決定ボタンの処理の下準備として、テンプレート用のシートのオブジェクト名を「shKoza」としておきました。こうすることにより、テンプレートシートからコピーされたシートは「shKoza1」、「shKoza2」といったような命名規則になっていきます。
https://vbaexcel.slavesystems.com/vba/?p=949
「If sh.CodeName Like “shKoza*” Then」でワークシート名が「shKoza」で始まるかを判断し、TRUEであれば選択肢を追加するようにしています。
決定ボタンのコード作成
決定ボタンが押された際ですが、以下の順番で処理を行うように実装します。
- コンボボックスの設定から、値を設定するワークシートを取得する
- ワークシートの最終行を取得する
- 日付、入金or出金、金額をセルに設定する
コードは以下のようになります。
'決定ボタンクリック時
Private Sub cmdKettei_Click()
'出金口座で選択されたシートを操作
Dim shMoto As Worksheet
Set shMoto = ThisWorkbook.Worksheets(cmbShukin.Text)
'使用最終行を取得
lngLastRow = shMoto.Cells(1, 1).SpecialCells(xlLastCell).Row
'日付に本日日付を設定する
shMoto.Cells(lngLastRow + 1, shKozaColumns.HIDUKE).Value = Format(Date, "yyyy/mm/dd")
'種別に「出金」を設定する
shMoto.Cells(lngLastRow + 1, shKozaColumns.SHUBETU).Value = SHUBETU_SHUKIN
'金額を設定する
shMoto.Cells(lngLastRow + 1, shKozaColumns.KINGAKU).Value = txtKingaku.Text
'入金口座で選択されたシートを操作
Dim shSaki As Worksheet
Set shSaki = ThisWorkbook.Worksheets(cmbNyukin.Text)
'使用最終行を取得
lngLastRow = shSaki.Cells(1, 1).SpecialCells(xlLastCell).Row
'日付に本日日付を設定する
shSaki.Cells(lngLastRow + 1, shKozaColumns.HIDUKE).Value = Format(Date, "yyyy/mm/dd")
'種別に「入金」を設定する
shSaki.Cells(lngLastRow + 1, shKozaColumns.SHUBETU).Value = SHUBETU_NYUKIN
'金額を設定する
shSaki.Cells(lngLastRow + 1, shKozaColumns.KINGAKU).Value = txtKingaku.Text
'フォームをアンロードする
Unload frmHurikae
End Sub
特に難しそうなところはないです。
日付セルの設定は、「Format(Date, “yyyy/mm/dd”)」を設定しているので本日日付が入力されます。
私は毎日家計簿を書くのでこれで問題ないですが、ある程度溜めて記載する方は、フォーム上から日付を指定できるようにすると利便性が上がると思います。
作成したフォームを呼び出す用の関数は以下になります。フォーム出現中は、ワークシートを操作されたくないのでモーダルモードで呼び出しとします。
'口座振替フォームを呼び出す
frmHurikae.Show vbModal
これで家計簿に金額などを設定する機能はすべて実装完了となりました。
次回の記事から現在の口座残高や資産推移を集計する処理の実装を行っていきます。
次記事
コメントを残す