エクセル VBA ユーザフォーム 総合計を簡単に出す方法

お世話になします。
添付図の様なユーザフォームがあります。
数量・単価の何処に入力しても総合計が反映させたいので、
TextBox1/2/4/5/7/8_Change時、それぞれ毎回、横列合計を出して、総合計を出しています。
これがTextBox90程続くので、簡素化したいのですが、
方法を教えて下さい。宜しくお願い致します。

Dim myHT1 As Long, myHT2 As Long, myHT3 As Long
Rem:ので、TextBox1/2/4/5/7/8にそれぞれに書いています。

Private Sub TextBox1_Change()
If IsNumeric(TextBox1.Value) And IsNumeric(TextBox2.Value) Then
TextBox3.Value = TextBox1 * TextBox2
myTH1 = TextBox1.Value * TextBox2.Value
End If
If IsNumeric(TextBox4.Value) And IsNumeric(TextBox5.Value) Then
TextBox6.Value = TextBox4 * TextBox5
myTH2 = TextBox4.Value * TextBox5.Value
End If
If IsNumeric(TextBox7.Value) And IsNumeric(TextBox8.Value) Then
TextBox9.Value = TextBox7 * TextBox8
myTH3 = TextBox7.Value * TextBox8.Value
End If
TextBox91.Value = myTH1 + myTH2 + myTH3
End Sub

No.4 ベストアンサー 回答者： WindFaller 回答日時： 2016/07/15 20:54

こんばんは。

少し、気になっていたので、全面的に変えてみました。
あまり良い改編とまでは行きませんが、TextBox91　この部分がすっきりしませんでしたので、書き換えました。(なお、順序どおりに並んでいない場合は、うまく行かないような気がします。）

'//
'UserForm モジュール
Private Sub CommandButton1_Click()
Dim i As Long
Dim k As Long
k = totalTxtBxNum
If k = 0 Then Exit Sub　'失敗した場合は、ボタンが利きません。
For i = 1 To k
　 myClass(i).myTxtBx.Text = ""
Next i
End Sub

Private Sub UserForm_Initialize()
　Dim colContrls As Collection
　Dim cn As Control
　Dim i As Long, j As Long
　Set colContrls = New Collection
　For Each cn In Me.Controls
　　On Error Resume Next
　　If TypeName(cn) = "TextBox" Then
　　　i = i + 1
　　　cn.IMEMode = fmIMEModeDisable
　　　colContrls.Add cn
　　　If i Mod 3 <> 0 Then
　　　　cn.TabIndex = j 'TabIndex を整える
　　　　j = j + 1
　　　End If
　　End If
　On Error GoTo 0
Next cn
totalTxtBxNum = colContrls.Count
ReDim myClass(colContrls.Count)
For i = 1 To colContrls.Count
　ReDim Preserve myClass(i)
　Set myClass(i) = New Class1
　With myClass(i)
　　Set .myTxtBx = colContrls(i)
　　.intIndex = i
　End With
Next
End Sub


'標準モジュール
Public myClass() As Class1
Public totalTxtBxNum As Long


'Class1
Option Explicit
Public WithEvents myTxtBx As MSForms.TextBox
Public intIndex As Integer
Private txtBxNum As Long
Private Sub Class_Initialize()
　txtBxNum = totalTxtBxNum
End Sub

Private Sub myTxtBx_Change()
　Dim a As Variant
　Dim b As Variant
　Dim i As Long
　Dim lSum As Long, mSum As Long
　If intIndex Mod 3 <> 2 Then Exit Sub
　　 i = intIndex
　　 a = myClass(i - 1).myTxtBx.Text
　　 b = myClass(i).myTxtBx.Text
　　 lSum = Val(a) * Val(b)
　　 myClass(i + 1).myTxtBx.Text = lSum
　　 Call TotalSum
End Sub
Sub TotalSum()
Dim j As Long
Dim mSum As Long
　For j = 3 To totalTxtBxNum - 1 Step 3
　　mSum = mSum + Val(myClass(j).myTxtBx.Text)
　Next j
　　　myClass(txtBxNum).myTxtBx.Text = mSum
End Sub

なお、このファイルは、
http://bit.ly/29XYhqe
で入手できます。パスワードは、このURLのhtml　の前の7桁の数字です。ちなみに、リボンカスタマイズの方法が見れるかと思います。ダウンロード期間は期限付きです。

この回答へのお礼

ご回答ありがとうございます。

記載して頂いたコードを、アレンジ/参考して、作成致します。

そもそもメインルーティンでやろうとしているのが、だめでした。
記載頂いたコートを参考に作成致します。

ダウンロードは出来ませんでしたが、ここまで教えて頂いたので
後は、自分で頑張ります。
ありがとうございます。

お礼日時：2016/07/19 11:02

No.5 回答者： ママチャリ 回答日時： 2016/07/17 15:17

No.1　fujillinさんの指摘にもある通り、ワークシートの計算機能を使うのが一番効率的と思います。

そこで、ワークシートで計算した結果をテキストボックスに転記するというのはいかがでしょうか？
プログラムコードのコーディング量は、かなり減ると思います。ただ、TextBoxのプロパティ設定がちょっと面倒ですが…。また、変則的な考え方になりますので、お気に召さなければ読み捨てて下さい。

具体的には、まず、計算用シートを用意します。A列（数量）、B列（単価）、C列(合計）×30行。31行目のC列は総合計とし、それぞれ計算式を設定します。
次に、TextBox（数量と単価）のControlSourceプロパティで、計算用シート上の各セルにリンクを張ります。
ここで、C列の合計にはリンクを張ってはいけません。合計については、計算用シートのWorksheet_Calculateイベントで、TextBox（合計と総合計）へ転記するようにコーディングします。
具体的には、こんな感じです。

Private Sub Worksheet_Calculate()
Dim i As Long
For i = 1 To 30
UserForm1.Controls("Textbox" & i * 3).Text = Sheets("Sheet1").Cells(i, "C").Value
Next
UserForm1.Controls("Textbox" & (i - 1) * 3 + 1).Text = Sheets("Sheet1").Cells(i, "C").Value
End Sub

この回答へのお礼

ありがとうございます。

ワークシートで計算させて、結果をTextBoxに転記する発想が無かったです。TextBoxに拘ってしまったのは、
1.履歴が見れる（コンボボックスでデータを呼び出す）
2.過去に入力したデータから新規入力で入力をする。
（コンボボックスで呼び出したデータを利用する）
3.履歴を保存したい。
（入力済のワークシートを、コピー貼付ければ良いのですが、面倒かなと思いました。）

今思うと、ワークシートにコンボボックスを用意して、別のシートにデータを保存させ、そこから出し入れした方が、だんぜん楽です。

次は、作る前に頭を使います。

貴重なご意見ありがとうございます。

お礼日時：2016/07/19 10:49

No.3 回答者： WindFaller 回答日時： 2016/07/14 14:54

補足：

90個以上のテキストボックスなど、データ数が多くなってきた場合は、必ず、どこかで、緊急避難的なデータを保存する方法を取ったほうがよいです。
Userform は、時々、手順を誤るとハングします。

何かのイベントに対して、外部的なバックアップを取る方法がないかと思っています。もちろん、Excelのバックアップ・システムを使ってもよいかとは思いますが、なんとなく、取り出し方に難があるような気がしていますので、実際にはやったことがありません。

この回答へのお礼

ありがとうございます。

下記の方法を試してみます。

データのバックアップは、テキストボックスに入力したデータを
txtで吐き出す事に致します。

お礼日時：2016/07/14 15:58

No.2 回答者： WindFaller 回答日時： 2016/07/13 20:14

>簡素化したいのですが、

本来、こういう時は、UserForm の中に、SpreadSheet オブジェクトを入れたほうがよいかもしれませんが、以下のように、Class に書けば、いくら数が増えても、数字を書き換えるだけで、このコードのまま同じように出来ます。

今回は、Val関数を用いました。

'//
'UserFrom module

Private Sub UserForm_Initialize()
Dim colContrls As Collection
Dim i As Long
Set colContrls = New Collection
For i = 1 To 9
　 colContrls.Add Me.Controls("TextBox" & i)
Next
　colContrls.Add Me.Controls("TextBox91") '数が飛んているので、外に出した

ReDim myClass(colContrls.Count)
　　　For i = 0 To colContrls.Count - 1
　　　　　　ReDim Preserve myClass(i)
　　　　　　Set myClass(i) = New Class1
　　　　　　With myClass(i)
　　　　　　 Set .myTxtBx = colContrls(i + 1)
　　　　　　　　　.intIndex = i + 1
　　　　　　End With
　　　Next
End Sub

'標準モジュール
Public myClass() As Class1


'Class1 モジュール

Public WithEvents myTxtBx As MSForms.TextBox
Public intIndex As Integer
Private Sub myTxtBx_Change()
　Dim a As Variant
　Dim b As Variant
　Dim i As Long, j As Long
　Dim lSum As Long, mSum As Long
　If intIndex Mod 3 <> 2 Then Exit Sub
　With UserForm1
　For i = 1 To 9 Step 3
　　　a = .Controls("TextBox" & i).Text
　　　b = .Controls("TextBox" & i + 1).Text
　　　lSum = Val(a) * Val(b)
　　　　.Controls("TextBox" & i + 2).Text = lSum
　Next i
　For j = 3 To 9 Step 3
　　mSum = mSum + Val(.Controls("TextBox" & j).Text)
　Next j
　　　.TextBox91.Value = mSum
　End With
End Sub
'//

No.1 回答者： fujillin 回答日時： 2016/07/13 17:53

こんにちは

回答ではありませんが・・・

90個もあるのなら、通常のシートを入力用シートとして使うのが良さそうに思います。
セルの入力規則で入力値の限定も可能ですし、セル間の計算も当然可能。
シートの保護を利用すれば、入力用のセル以外は変更できなくもできます。

まったく回答になっていなくて、ごめんなさい。

この回答へのお礼

ご回答ありがとうございます。
確かに、これだけ見るとシートで関数を入れた方が、だんぜん楽です。
作っている物を全部公開しなくてすみません。

お礼日時：2016/07/14 10:04