【エクセル】入力フォームの作り方 ～ 集計・入力作業の効率をアップさせる方法 ～

こんな要望にお応えします。

この記事では、ユーザーフォームのテキストボックスを使って金額を入力するための入力フォームの作り方を詳しく解説しています。

下記のような様式に金額を自動記入する入力フォームの作り方をご紹介しています。

この入力フォームを利用することによって、集計・入力作業の効率をアップさせることができます。

テキストボックスを上手に使いたい！集計・入力作業の効率をアップさせたい！と考えている方は是非参考にしてみてください。

金額を自動記入する入力フォームとは

金額を自動記入する入力フォームとはどういうものか説明します。

1.数量を入力するだけで合計金額を集計するテキストボックス

下図のように、白色のテキストボックスが手入力、黄色のテキストボックスが自動入力になります。

数量を入力した結果が下図になります。

白色のテキストボックスに値を入力した時点で、黄色のテキストボックスに集計結果が表示されます。

ひらめく人

すぐに結果を確認することができるのでとても便利

2.ボタンを押すだけで集計結果をセルに記入

下図のように、ボタンを押すだけでテキストボックスの全ての値をシートに記入することができます。

数量を記入するだけで集計結果をすぐに確認することができ、その集計結果をシートに反映させることができます。金額を入力するエクセル作業が大量にある方におすすめの入力フォームになっています。

この記事では、金額を自動記入する入力フォームの作り方について詳しく解説しています。

入力フォームの作り方

入力フォームを作成する場合、以下の手順で作業を進めると効率的です。

最初にVBAの作成から手がける方がいらっしゃいますが、得策ではありません。

VBAの作成途中に様式の変更が発生してしまうと、値を入力するセルの場所がズレる可能性があります。

そうなると作成中のVBAを作り直さないといけなくなるので、無駄な手間と時間がかかってしまいます。上記の手順を意識して入力フォームを作成してみてください。

1.シートの様式を決める

入力フォームを作成する際、一番最初にやるべきことはシートの様式を作り込むことです。どんな書類を作成するのか、シートのどのセルを編集するのかを確定しなければVBAを作成することができません。途中で様式の変更が発生しないよう、しっかりと作り込みましょう。

2.値を入力するセルをピックアップする

VBAの作成で必要になるのが、「値を記入するセルの場所」です。テキストボックスの値をどこのセルに記入するのかをピックアップしておくことで効率よく作業を進めることができます。

Cells( 1 , 1 ) = TextBox1.Value というようにVBAを作っていきます。

上記VBAは、テキストボックス1の値をA1セルに入力する！という意味です。このようなVBAを積み重ねていくことで「金額を自動記入する入力フォーム」を作ることができます。

上を目指す人

テキストボックスの数が多くなると、作成するVBAの数も多くなって大変ですが頑張りましょう。

3.入力フォームの様式を決める

入力フォームの様式を作成するには、ユーザーフォームでオブジェクトの編集をしていきます。

金額を自動記入する入力フォームは以下のようにオブジェクトを作成していきます。

ユーザーフォームは値を入力する際に表示されるものなのでできるだけ見やすく、扱いやすいものにしましょう。

テキストボックスやコマンドボタンの大きさを変えたり、色を変えたりすることもできます。

プロパティを操作することでいろんな編集を行うことができます。

上を目指す人

プロパティをいろいろ操作して扱いやすい入力フォームに仕上げてみてください。

4.VBAを実行する仕組みを決める

入力フォームの作成では、「VBAの実行方法」をしっかりと考える必要があります。

「金額を自動記入する入力フォーム」では以下の実行方法が使われてています。

実行に関するVBAだけでも3種類のVBAが使われています。入力フォームをどのように運用していくのか！どのような設定にすれば使いやすいのか！をしっかり作り込んでみてください。

4-1.ボタンで入力フォームを表示させるVBA

「入力フォームが常に表示されていると邪魔くさい！」という方が多いので、ボタンを押すと入力フォームが表示されるVBAを設定します。

標準モジュールに「入力フォームを表示するVBA」を作成します。

Sub 表示()
With UserForm1
    .Show vbModeless
    .Height = 450
    .Width = 600
End With
End Sub

シートにボタンを作成し、「表示VBA」を設定します。

4-2.コマンドボタンでテキストボックスの値をセルに記入するVBA

ユーザーフォームにある「入力実行」ボタンを押すと、テキストボックスの値を指定するセルの記入する！というVBAを設定します。

コマンドボタンにVBAを記入します。

Private Sub CommandButton1_Click()
'セルに入力されている値をリセット
Range(Cells(27, 12), Cells(41, 35)).ClearContents
'標準モジュールに設定した「セルに金額を記入するVBA」を呼び出す
Call 金額転記1
Call 金額転記2
'今日の日付を入力するToday関数を設定
Cells(3, 11).Formula = "=Today()"
'入力フォームを閉じる
Unload Me
End Sub

コマンドボタンを押してVBAを実行すると、標準モジュールに作成する「金額転記1」「金額転記2」というVBAを呼び出し、テキストボックスの値をセルに記入することができます。

4-3.テキストボックスに値を入力すると別のテキストボックスに値を表示するVBA

「枚数を入力するテキストボックスに値を入力すると、合計金額を表示するテキストボックスに値が表示される」というVBAを設定します。

「1万円の枚数を手入力すると、集計金額が表示される」という部分のVBAを紹介します。

テキストボックスにVBAを記入します。

Private Sub TextBox1_Change()
On Error Resume Next
Go1 = UserForm1.TextBox1.Value * 10000
Go2 = UserForm1.TextBox2.Value * 5000
Go3 = UserForm1.TextBox3.Value * 2000
Go4 = UserForm1.TextBox4.Value * 1000
Go5 = UserForm1.TextBox5.Value * 500
Go6 = UserForm1.TextBox6.Value * 100
Go7 = UserForm1.TextBox7.Value * 50
Go8 = UserForm1.TextBox8.Value * 10
Go9 = UserForm1.TextBox9.Value * 5
Go10 = UserForm1.TextBox10.Value * 1
Go11 = UserForm1.TextBox11.Value * 500
Go55 = Val(UserForm1.TextBox1.Value) + Val(UserForm1.TextBox2.Value) + Val(UserForm1.TextBox3.Value) + Val(UserForm1.TextBox4.Value) + Val(UserForm1.TextBox5.Value) + Val(UserForm1.TextBox6.Value) + Val(UserForm1.TextBox7.Value) + Val(UserForm1.TextBox8.Value) + Val(UserForm1.TextBox9.Value) + Val(UserForm1.TextBox10.Value) + Val(UserForm1.TextBox11.Value) + Val(UserForm1.TextBox12.Value) + Val(UserForm1.TextBox13.Value)
Go53 = Go1 + Go2 + Go3 + Go4 + Go5 + Go6 + Go7 + Go8 + Go9 + Go10 + Go11 + UserForm1.TextBox25.Value + UserForm1.TextBox26.Value
If UserForm1.TextBox1.Value <> "" Then
UserForm1.TextBox14 = Format(Go1, "#,##0")
UserForm1.TextBox55 = Format(Go55, "#,##0")
UserForm1.TextBox53 = Format(Go53, "#,##0")
End If
On Error GoTo 0
End Sub

上記のサンプルVBAは「両替前の1万円の枚数を手入力すると合計が集計される」部分です。ほかの5千円や2千円部分の金額計算は上記サンプルVBAと同じような表記になるので省略します。

テキストボックス毎にチェンジプロパティを設定することで、「テキストボックスを操作するだけでコードを実行する」ことができます。上記のVBAのように、テキストボックス一つ一つに集計金額を表示するVBAを設定していくと入力フォームを作成することができます。

上を目指す人

記入しなければならないVBAが大量で大変ですが、頑張ってみてください。

よくコマンドボタンを使ってVBAを実行する入力フォームを見かけますが、ボタンをクリックする操作が増えてしまいます。これから入力フォームを作成する場合、操作が手順がなるべく少ない簡単な入力フォームになるようにしましょう。

5.標準モジュールにシートに値を記入するVBAを作成する

標準モジュールには、テキストボックスの値をシートに入力するVBAを作成します。標準モジュールで作成したVBAはコマンドボタンのクリックで実行されるように設定します。

Sub 金額転記1()
'テキストボックスの集計をセルに入力(両替前)
Dim Go1 As Long
Dim Go2 As Long
Dim Go3 As Long
Dim Go4 As Long
Dim Go5 As Long
Dim Go6 As Long
Dim Go7 As Long
Dim Go8 As Long
Dim Go9 As Long
Dim Go10 As Long
Dim Go11 As Long
Dim Go25 As Long
Dim Go26 As Long
Dim Go53 As Long
Dim i As String

Go1 = UserForm1.TextBox1.Value * 10000
i = Len(UserForm1.TextBox1.Value * 10000)
For j = 1 To 9
If UserForm1.TextBox1.Value = "0" Then
GoTo Label1
ElseIf IsNumeric(Go1) And (i - j >= 0) Then
Cells(27, 24 - j) = Mid(Go1, (i + 1) - j, 1)
End If
Next
Label1:

Go2 = UserForm1.TextBox2.Value * 5000
i = Len(UserForm1.TextBox2.Value * 5000)
For j = 1 To 9
If UserForm1.TextBox2.Value = "0" Then
GoTo Label2
ElseIf IsNumeric(Go2) And (i - j >= 0) Then
Cells(28, 24 - j) = Mid(Go2, (i + 1) - j, 1)
End If
Next
Label2:

Go3 = UserForm1.TextBox3.Value * 2000
i = Len(UserForm1.TextBox3.Value * 2000)
For j = 1 To 9
If UserForm1.TextBox3.Value = "0" Then
GoTo Label3
ElseIf IsNumeric(Go3) And (i - j >= 0) Then
Cells(29, 24 - j) = Mid(Go3, (i + 1) - j, 1)
End If
Next
Label3:

Go4 = UserForm1.TextBox4.Value * 1000
i = Len(UserForm1.TextBox4.Value * 1000)
For j = 1 To 9
If UserForm1.TextBox4.Value = "0" Then
GoTo Label4
ElseIf IsNumeric(Go4) And (i - j >= 0) Then
Cells(30, 24 - j) = Mid(Go4, (i + 1) - j, 1)
End If
Next
Label4:

Go5 = UserForm1.TextBox5.Value * 500
i = Len(UserForm1.TextBox5.Value * 500)
For j = 1 To 9
If UserForm1.TextBox5.Value = "0" Then
GoTo Label5
ElseIf IsNumeric(Go5) And (i - j >= 0) Then
Cells(31, 24 - j) = Mid(Go5, (i + 1) - j, 1)
End If
Next
Label5:

Go6 = UserForm1.TextBox6.Value * 100
i = Len(UserForm1.TextBox6.Value * 100)
For j = 1 To 9
If UserForm1.TextBox6.Value = "0" Then
GoTo Label6
ElseIf IsNumeric(Go6) And (i - j >= 0) Then
Cells(32, 24 - j) = Mid(Go6, (i + 1) - j, 1)
End If
Next
Label6:

Go7 = UserForm1.TextBox7.Value * 50
i = Len(UserForm1.TextBox7.Value * 50)
For j = 1 To 9
If UserForm1.TextBox7.Value = "0" Then
GoTo Label7
ElseIf IsNumeric(Go7) And (i - j >= 0) Then
Cells(33, 24 - j) = Mid(Go7, (i + 1) - j, 1)
End If
Next
Label7:

Go8 = UserForm1.TextBox8.Value * 10
i = Len(UserForm1.TextBox8.Value * 10)
For j = 1 To 9
If UserForm1.TextBox8.Value = "0" Then
GoTo Label8
ElseIf IsNumeric(Go8) And (i - j >= 0) Then
Cells(34, 24 - j) = Mid(Go8, (i + 1) - j, 1)
End If
Next
Label8:

Go9 = UserForm1.TextBox9.Value * 5
i = Len(UserForm1.TextBox9.Value * 5)
For j = 1 To 9
If UserForm1.TextBox9.Value = "0" Then
GoTo Label9
ElseIf IsNumeric(Go9) And (i - j >= 0) Then
Cells(35, 24 - j) = Mid(Go9, (i + 1) - j, 1)
End If
Next
Label9:

Go10 = UserForm1.TextBox10.Value * 1
i = Len(UserForm1.TextBox10.Value * 1)
For j = 1 To 9
If UserForm1.TextBox10.Value = "0" Then
GoTo Label10
ElseIf IsNumeric(Go10) And (i - j >= 0) Then
Cells(36, 24 - j) = Mid(Go10, (i + 1) - j, 1)
End If
Next
Label10:


Go11 = UserForm1.TextBox11.Value * 500
i = Len(UserForm1.TextBox11.Value * 500)
For j = 1 To 9
If UserForm1.TextBox11.Value = "0" Then
GoTo Label11
ElseIf IsNumeric(Go11) And (i - j >= 0) Then
Cells(37, 24 - j) = Mid(Go11, (i + 1) - j, 1)
End If
Next
Label11:

Go25 = UserForm1.TextBox25.Value * 1
i = Len(UserForm1.TextBox25.Value * 1)
For j = 1 To 9
If UserForm1.TextBox25.Value = "0" Then
GoTo Label25
ElseIf IsNumeric(Go25) And (i - j >= 0) Then
Cells(38, 24 - j) = Mid(Go25, (i + 1) - j, 1)
End If
Next
Label25:

Go26 = UserForm1.TextBox26.Value * 1
i = Len(UserForm1.TextBox26.Value * 1)
For j = 1 To 9
If UserForm1.TextBox26.Value = "0" Then
GoTo Label26
ElseIf IsNumeric(Go26) And (i - j >= 0) Then
Cells(39, 24 - j) = Mid(Go26, (i + 1) - j, 1)
End If
Next
Label26:

Go53 = UserForm1.TextBox53.Value * 1
i = Len(UserForm1.TextBox53.Value * 1)
For j = 1 To 9
If UserForm1.TextBox53.Value = "0" Then
GoTo Label53
ElseIf IsNumeric(Go53) And (i - j >= 0) Then
Cells(41, 24 - j) = Mid(Go53, (i + 1) - j, 1)
End If
Next
Label53:

For j = 1 To 13
If UserForm1.Controls("TextBox" & j).Value = "0" Then
GoTo Label60
Else
Cells(26 + j, 12) = UserForm1.Controls("TextBox" & j).Value
Cells(41, 12) = UserForm1.TextBox55.Value
End If
Label60:
Next

End Sub

上記のVBAは「両替前の全てのテキストボックスの値を指定するセルに入力する」というものです。

「両替後の金額を記入するVBA」は上記で紹介しているVBAとほとんど同じなので省略します。

上を目指す人

テキストボックス一つ一つに値をセルに入力するVBAを設定しなければならないので結構大変な作業ですが頑張ってみてください。

金額を自動記入する入力フォームは仕事でよく使われるマクロ

金額を自動記入する入力フォームは仕事でよく使われるマクロです。

金額を入力する操作があるエクセルファイルに導入することができます。

下記のサイトでは、上記で紹介している「金額入力フォーム」が設定されたエクセルファイルをダウンロードして使うことができます。

このマクロを使えば、紙幣や硬貨の枚数を入力するだけで合計金額の確認、セルへの入力が可能です。

金額を入力するエクセル業務に最適のマクロになっているので是非参考にしてみてください。

<<金額入力フォームが設定されたエクセルのダウンロードはこちら

この記事では「ユーザーフォームを使って入力フォームを作成する方法」をご紹介してきましたが、ユーザーフォームを使わずに入力フォームの機能を設定することができます。

それは、「シートから別シートに情報を転記するVBAを使う」ということです。

入力フォームの代わりに、設定シートを作りあらかじめ入力項目一覧を表記しておきます。入力項目一覧表に記入した情報を様式シートに転記すれば入力フォームと同じ効果を得ることができます。

下記の記事では、「シートから別シートに情報を転記するVBA」と「転記VBAを使った契約書作成VBA」について紹介されています。

mamemametochan.com

 

5 Pockets

【エクセルマクロ】別シートに情報を自動反映させるマクロの作り方・導入方法を紹介

https://mamemametochan.com/excel-8

 シートから別シートに必要な情報を転記する方法ってないの？ こんな疑問にお答えします。 結論から言うと、シート名を取得するVBAを使って、別シートに情報を自動反映させるマクロを作成する！です。 別シートに情報を転記するマクロの概要がこちら 一覧表に入力している大量の情報を、ボタンをクリックするだけで別シートの好きな場所(セル)に転記することが可能。 この別シートに情報を転記するマクロはシート名を取得する方法さえ理解すれば簡単に作成することができます。この記事では画像や動画を...

mamemametochan.com

 

1 Pocket

【エクセルVBA】「文字を丸で囲む」マクロの作り方～設定されているコードを解説～

https://mamemametochan.com/excel-73

 文字を丸で囲む処理を自動化したい！ こんな要望をエクセルVBAを使って解決します。 文字を丸で囲むVBAを導入すれば以下のような編集作業の効率をアップさせることができます。 アンケートなどでよく使われる「有・無」どちらかに〇を点ける 「点検済み」を〇で囲む テキスト長によって〇の大きさを調整する 文字を丸で囲むVBAを使えば、「文字に〇が被って見づらくなった！！」「〇ってどうやって入力するんだっけ？」といった問題の発生を防ぐことができます。 エクセル作業で文字を丸で囲む作業が定期...

ユーザーフォームの設定は苦手だけど入力フォームを作成したい！と考えている方におすすめの内容です。