[[20100311125825]]　『VBAユーザーフォーム内での計算』（春一番）　ページの最後に飛ぶ

『VBAユーザーフォーム内での計算』（春一番）

 VBAのユーザーフォーム内にある
 1、コンボボックス「リストA」 は同ブック内のシート名データのB3:B列最終入力行までの値を取得する。
 2、1のリストで値を選択した時、シート名データのB列で一致する行から
 　　　テキストボックス名「あああ」 は、C列の一致行の金額を
  　　　　　　　　　　　 「いいい」 は、D列の一致行の金額を
  　　　　　　　　　　　 「ううう」 は、E列の一致行の金額を
  　　　　　　　　　　　 「えええ」 は、F列の一致行の金額を
  　　　　　　　　　　　 「おおお」 は、G列の一致行の金額を　表示させたい。
  　　　　　　　　　　　 「合計」 は、上記5個のテキストボックスの合計金額を表示させたい。

 どんなコードを書けば良いのか教えてください。
 宜しくお願いします。
 エクセル2003
 WINXP

 サンプルコードを載せておきます。
 このような場合、コントロールのオブジェクト名をユニークにすると不便なので
 初期のままか、連番になるようにしておくと簡単に出来るので覚えておいてください。

 サンプルコードでは初期のオブジェクト名のままの場合の例を載せています。

  Private Sub UserForm_Initialize()
  With Worksheets("Sheet1")
    Me.ComboBox1.List = .Range(.Range("B3"), .Range("B3").End(xlDown)).Value
  End With
  End Sub

  Private Sub ComboBox1_Change()
  Dim i As Long, buf As Double, mySum As Double
  With Worksheets("Sheet1")
    For i = 1 To 5
      buf = .Cells(Me.ComboBox1.ListIndex + 3, i + 2).Value
      Me.Controls("TextBox" & i).Value = buf
      mySum = mySum + buf
    Next i
  End With
  Me.TextBox6.Value = mySum
  End Sub

 （ｍｏｍｏ）

 結果的にリストボックスに変更しましたができました、ありがとうございました。
 続いてチェックボックスについての質問なのですが、先の質問のように基本のコードの書き方を教えてください。
 先の質問で教えていただいたコードと検索でとっかえひっかえしたみたのですが、
 やっているうちに訳がわからなくなる始末で…。

 チェックボックスが6つあります。
 チェックしたものだけをテキストボックス6で合計したいのです。

 チェックボックスがＴｒｕｅの時の値は
 先のテキストボックスのようにリストボックスで選択した値と一致するＢ列の行で
 チェックボックス1はJ列
 　　　　　　　　　　2はＬ列
 　　　　　　　　　　3はＮ列
 　　　　　　　　　　4はＰ列
 　　　　　　　　　　5はＲ列
 　　　　　　　　　　6はＴ列　としたいです。

 そして、最終的にはテキストボックス7でテキストボックス5と6の金額を合計したいです。
 宜しくお願いします。
 (春一番)

 え～っと・・・・ まったくイメージがわかないのですが・・・

 少しでも今出来ているコードがあれば見させて頂くとか
 検証できるデータと結果とかを例としてあげてもらえないと
 春一番さんのシートの状態もフォームの状態もわからないので問題点を理解するのが難しいです。

 （ｍｏｍｏ）

 ｍｏｍｏさん、すみません。
 少しだけ、検索したものがついてますが…
 コマンドボタンのものは、形になるまでいかなくてコードがありません。。

 Private Sub UserForm_Initialize()
 With Worksheets("Sheet1")
   Me.ListBox1.List = .Range(.Range("B3"), .Range("B3").End(xlDown)).Value
 End With
 End Sub

 Private Sub ListBox1_Change()
 Dim i As Long, buf As Double, mySum As Double
 With Worksheets("Sheet1")
   For i = 1 To 4
     buf = .Cells(Me.ListBox1.ListIndex + 3, i + 2).Value
     Me.Controls("TextBox" & i).Value = buf
     Me.Controls("TextBox" & i).Value = Format(Me.Controls("TextBox" & i).Value, "#,###")
     Me.Controls("金額").Value = Cells(Me.ListBox1.ListIndex + 3, 8).Value
     Me.Controls("金額").Value = Format(Me.Controls("金額").Value, "#,###")
     mySum = mySum + buf
   Next i
 End With
 Me.TextBox5.Value = mySum
 Me.TextBox5.Value = Format(Me.TextBox5.Value, "#,###")
 End Sub

  フォームにあるコントロール
 ・ListBox1
 ・TextBox1～13
 ・CheckBox1～6

 Sheet1
   B　　C　 D　 E　 F　 G　 H　 I　 J　 K　 L　　M　　N　　O　　P　　Q　　R　　S　　T
 3 aaa　11　21　31　41　51　61　71　81　91　101　111　121　131　141　151　161　171　181
 4 bbb　12　22　32　42　52　62　72　82　92　102　112　122　132　142　152　162　172　182
 5 ccc　13　23　33　43　53　63　73　83　93　103　113　123　133　143　153　163　173　183
 6 ddd　14　24　34　44　54　64　74　84　94　104　114　124　134　144　154　164　174　184
 7 eee　15　25　35　45　55　65　75　85　95　105　115　125　135　145　155　165　175　185

 ListBox1とTextBox1～5は先に教えて頂いたコードで動いています。
 CheckBoxは先のテキストボックスのようにリストボックスで選択した値と一致するＢ列の行で
 チェックボックス1チェックでTextBox6にJ列の値
 　　　　　　　　　　2チェックでTextBox7にＬ列の値
 　　　　　　　　　　3チェックでTextBox8にＮ列の値
 　　　　　　　　　　4チェックでTextBox9にＰ列の値
 　　　　　　　　　　5チェックでTextBox10にＲ列の値
 　　　　　　　　　　6チェックでTextBox11にＴ列の値を拾うとします。

 仮に全部のチェックボックスにチェックが入り、ListBox1でcccを選択した場合、
 TextBox6は83
 　　　　　 7は103
 　　　　　 8は123
 　　　　　 9は143
 　　　　　10は163
 　　　　　11は183　の値を拾い
 TextBox12でチェックボックスで拾った6～11の値の合計798がでる。

 TextBox13は、 TextBox5（112）と12の値(798)を合計した910となる。

 こんな説明でわかりますでしょうか？
 (春一番)

 こんなので合ってますか？

 Private Sub UserForm_Initialize()
 With Worksheets("Sheet1")
   Me.ListBox1.List = .Range(.Range("B3"), .Range("B3").End(xlDown)).Value
 End With
 End Sub

 Private Sub ListBox1_Change()
 Dim i As Long, buf As Double, mySum1 As Double, mySum2 As Double
 With Worksheets("Sheet1")
   For i = 1 To 4
     buf = .Cells(Me.ListBox1.ListIndex + 3, i + 2).Value
     Me.Controls("TextBox" & i).Value = Format(buf, "#,###")
     mySum1 = Format(mySum1 + buf, "#,###")
   Next i
   Me.TextBox5.Value = mySum1
   For i = 1 To 6
     If Me.Controls("CheckBox" & i).Value = True Then
       mySum2 = mySum2 + .Cells(Me.ListBox1.ListIndex + 3, i * 2 + 8).Value
     End If
   Next i
   Me.TextBox6.Value = Format(mySum2, "#,###")
   Me.TextBox7.Value = Format(mySum1 + mySum2, "#,###")
 End With
 End Sub

 （ｍｏｍｏ）

 ｍｏｍｏさん、すみません。
 書き直している間に衝突が起きちゃいました。

 Private Sub UserForm_Initialize()
 With Worksheets("Sheet1")
   Me.ListBox1.List = .Range(.Range("B3"), .Range("B3").End(xlDown)).Value
 End With
 End Sub

 Private Sub ListBox1_Change()
 Dim i As Long, buf As Double, mySum As Double
 With Worksheets("Sheet1")
   For i = 1 To 4
     buf = .Cells(Me.ListBox1.ListIndex + 3, i + 2).Value
     Me.Controls("TextBox" & i).Value = buf
     Me.Controls("TextBox" & i).Value = Format(Me.Controls("TextBox" & i).Value, "#,###")
     Me.Controls("金額").Value = Cells(Me.ListBox1.ListIndex + 3, 8).Value
     Me.Controls("金額").Value = Format(Me.Controls("金額").Value, "#,###")
     mySum = mySum + buf
   Next i
 End With
 Me.TextBox5.Value = mySum
 Me.TextBox5.Value = Format(Me.TextBox5.Value, "#,###")
 End Sub

  フォームにあるコントロール
 ・ListBox1
 ・TextBox1～13　と 金額という名前のTextBox
 ・CheckBox1～6

 Sheet1
   B　　C　 D　 E　 F　 G　 H　 I　 J　 K　 L　　M　　N　　O　　P　　Q　　R　　S　　T
 3 aaa　11　21　31　41　51　61　71　81　91　101　111　121　131　141　151　161　171　181
 4 bbb　12　22　32　42　52　62　72　82　92　102　112　122　132　142　152　162　172　182
 5 ccc　13　23　33　43　53　63　73　83　93　103　113　123　133　143　153　163　173　183
 6 ddd　14　24　34　44　54　64　74　84　94　104　114　124　134　144　154　164　174　184
 7 eee　15　25　35　45　55　65　75　85　95　105　115　125　135　145　155　165　175　185

 ListBox1とTextBox1～5は先に教えて頂いたコードで動いています。
 CheckBoxは先のテキストボックスのようにリストボックスで選択した値と一致するＢ列の行で
 チェックボックス1チェックでTextBox6にJ列の値
 　　　　　　　　　　2チェックでTextBox7にＬ列の値
 　　　　　　　　　　3チェックでTextBox8にＮ列の値
 　　　　　　　　　　4チェックでTextBox9にＰ列の値
 　　　　　　　　　　5チェックでTextBox10にＲ列の値
 　　　　　　　　　　6チェックでTextBox11にＴ列の値を拾うとします。

 仮に全部のチェックボックスにチェックが入り、ListBox1でcccを選択した場合、
 TextBox6は83
 　　　　　 7は103
 　　　　　 8は123
 　　　　　 9は143
 　　　　　10は163
 　　　　　11は183　の値を拾い
 TextBox12でチェックボックスで拾った6～11の値の合計798がでる。

 TextBox13は、 TextBox5（112）と12の値(798)を合計した910となる。

 こんな説明でわかりますでしょうか？
 (春一番)

 こうかな？

 Private Sub ListBox1_Change()
 Dim i As Long, buf As Double, mySum1 As Double, mySum2 As Double
 With Worksheets("Sheet1")
   For i = 1 To 4
     buf = .Cells(Me.ListBox1.ListIndex + 3, i + 2).Value
     Me.Controls("TextBox" & i).Value = Format(buf, "#,###")
     mySum1 = Format(mySum1 + buf, "#,###")
   Next i
   Me.TextBox5.Value = mySum1
   For i = 1 To 6
     If Me.Controls("CheckBox" & i).Value = True Then
       buf = .Cells(Me.ListBox1.ListIndex + 3, i * 2 + 8).Value
       Me.Controls("TextBox" & i + 5).Value = buf
       mySum2 = mySum2 + buf
     End If
   Next i
   Me.TextBox12.Value = Format(mySum2, "#,###")
   Me.TextBox13.Value = Format(mySum1 + mySum2, "#,###")
   Me.金額.Value = Format(.Cells(Me.ListBox1.ListIndex + 3, 8).Value, "#,###")
 End With
 End Sub

 （ｍｏｍｏ）

 すみません、私のチェックボックスプロパティの設定がおかしいのか、
 チェックしてもテキストボックスに値が入りません…？
 どういった原因が考えられますか？
 (春一番)

 ListBoxを選択してからチェックしてませんか？

 今現在はListBox1_Changeイベントしか使っていませんので
 チェックした後にListBoxを選択しないと値は入りませんが
 （ｍｏｍｏ）

 あ！　先に書かれていましたが書いたので…

 使用手順
 1、リストボックスで選択する。(テキストボックス1～4(合計もあわせると5)が反応する)
 2、チェックボックス目的のものをチェックする。
 　（チェックボックスに連携したテキストボックスがチェックした瞬間に反応して、合わせてテキストボックス12、13が反応する）

 としたいのですが

 1、リストボックスで選択する。(テキストボックス1～4(合計もあわせると5)が反応する)
 2、チェックボックス目的のものをチェックする。
 　（チェックボックスに連携したテキストボックスがチェックした瞬間に反応しない、合わせてテキストボックス12、13も反応しない）
 3、リストボックスから違うものを選択してみると2でチェックしていたものに連携したテキストボックスがここで反応。

 となっているようです。
 チェックした瞬間に変化できないものでしょうか？
 (春一番)

 もしチェックボックスのチェック時にも変えたいのであれば以下のように。

 Private Sub UserForm_Initialize()
 With Worksheets("Sheet1")
   Me.ListBox1.List = .Range(.Range("B3"), .Range("B3").End(xlDown)).Value
 End With
 End Sub

 Private Sub ListBox1_Change()
 ValueSet
 End Sub

 Private Sub CheckBox1_Change()
 ValueSet
 End Sub

 Private Sub CheckBox2_Change()
 ValueSet
 End Sub

 Private Sub CheckBox3_Change()
 ValueSet
 End Sub

 Private Sub CheckBox4_Change()
 ValueSet
 End Sub

 Private Sub CheckBox5_Change()
 ValueSet
 End Sub

 Private Sub CheckBox6_Change()
 ValueSet
 End Sub

 Sub ValueSet()
 Dim i As Long, buf As Double, mySum1 As Double, mySum2 As Double
 With Worksheets("Sheet1")
   For i = 1 To 4
     buf = .Cells(Me.ListBox1.ListIndex + 3, i + 2).Value
     Me.Controls("TextBox" & i).Value = Format(buf, "#,###")
     mySum1 = Format(mySum1 + buf, "#,###")
   Next i
   Me.TextBox5.Value = mySum1
   For i = 1 To 6
     If Me.Controls("CheckBox" & i).Value = True Then
       buf = .Cells(Me.ListBox1.ListIndex + 3, i * 2 + 8).Value
       Me.Controls("TextBox" & i + 5).Value = buf
       mySum2 = mySum2 + buf
     End If
   Next i
   Me.TextBox12.Value = Format(mySum2, "#,###")
   Me.TextBox13.Value = Format(mySum1 + mySum2, "#,###")
   Me.金額.Value = Format(.Cells(Me.ListBox1.ListIndex + 3, 8).Value, "#,###")
 End With
 End Sub

 （ｍｏｍｏ）

 ありがとうございます！！
 思ったように動いております！
 あと一つ質問なのですが、私が検索でひっつけた"#,###"のせいだと思いますが
 値が0の時、テキストボックスの表示が空白になってしまいます。
 Format(mySum1 + buf, "#,###") 表示する値または合計する値が0の時は―などと表示したい時の方法はありますか？
 (春一番)

 0でよければ「#,###」を「#,##0」にしてみるとか
 -なら「#,###;-#,###;-」にするとか
 （ｍｏｍｏ）

 あー！書式設定のユーザー設定ようようなものなのですね。！

 じっくりと動作検証したのですが、エラーが起きて困っています。
 ユーザーフォームを立ち上げていきなりチェックボックスにチェックをした時、
 リストで何も選択されていない為、デバッグになってしまいます。
 リストで何も選択されていない場合はチェックボックスを使用できないグレー表示にしておきたいです。
 IF～などで条件分岐するのだと思いますが、
どのように今のコードに使えばよいでしょうか？

  あとチェックボックスをOnにした時に連動したテキストボックスに値が表示されるのですが、
 その同じチェックボックスをOffにした時もテキストボックスに表示された値が残ったままになってしまいます。
 これはどうしようもないことなのでしょうか？
 (春一番)

 えっと、ロジック次第という事で可能は可能です。
 そのように組めばいいだけですので以下のように変更してみてください。
 処理のパーツをサブプロシージャとして作って必要に応じてCallする感じです。

 本当のやりたい事をしっかりするともっと効率的なコードになるかもしれませんが。

 Private Sub UserForm_Initialize()
 With Worksheets("Sheet1")
   Me.ListBox1.List = .Range(.Range("B3"), .Range("B3").End(xlDown)).Value
 End With
 EnCheck False
 SetCheck
 SetList
 SetCalc
 End Sub

 Private Sub ListBox1_Change()
 If Me.ListBox1.ListIndex = -1 Then
   EnCheck False
 Else
   EnCheck True
 End If
 SetList
 SetCheck
 SetCalc
 End Sub

 Private Sub CheckBox1_Change()
 SetCheck
 SetCalc
 End Sub

 Private Sub CheckBox2_Change()
 SetCheck
 SetCalc
 End Sub

 Private Sub CheckBox3_Change()
 SetCheck
 SetCalc
 End Sub

 Private Sub CheckBox4_Change()
 SetCheck
 SetCalc
 End Sub

 Private Sub CheckBox5_Change()
 SetCheck
 SetCalc
 End Sub

 Private Sub CheckBox6_Change()
 SetCheck
 SetCalc
 End Sub

 Private Sub EnCheck(flg As Boolean)
 Dim i As Long
 For i = 1 To 6
   Me.Controls("CheckBox" & i).Enabled = flg
 Next i
 End Sub

 Private Sub SetList()
 Dim i As Long
 With Me.ListBox1
   If .ListIndex = -1 Then
     For i = 1 To 4
       Me.Controls("TextBox" & i).Value = "-"
     Next i
   Else
     For i = 1 To 4
       Me.Controls("TextBox" & i).Value = _
         Format(Worksheets("Sheet1").Cells(.ListIndex + 3, i + 2).Value, "#,###;-#,###;-")
     Next i
   End If
 End With
 End Sub

 Private Sub SetCheck()
 Dim i As Long
 For i = 1 To 6
   With Me.Controls("CheckBox" & i)
     If .Value = True Then
       Me.Controls("TextBox" & i + 5).Value = _
         Format(Worksheets("Sheet1").Cells(Me.ListBox1.ListIndex + 3, i * 2 + 8).Value, "#,###;-#,###;-")
     Else
       Me.Controls("TextBox" & i + 5).Value = "-"
     End If
   End With
 Next i
 End Sub

 Private Sub SetCalc()
 Dim i As Long, mySum1 As Double, mySum2 As Double
 For i = 1 To 4
   mySum1 = mySum1 + CDbl(Replace(Me.Controls("TextBox" & i).Value, "-", 0))
 Next i
 For i = 6 To 11
   With Me.Controls("TextBox" & i)
     If .Value <> "" And .Value <> "-" Then
       mySum2 = mySum2 + CDbl(Replace(.Value, "-", 0))
     End If
   End With
 Next i
 Me.TextBox5.Value = Format(mySum1, "#,###;-#,###;-")
 Me.TextBox12.Value = Format(mySum2, "#,###;-#,###;-")
 Me.TextBox13.Value = Format(mySum1 + mySum2, "#,###;-#,###;-")
 Me.金額.Value = Format(Worksheets("Sheet1").Cells(Me.ListBox1.ListIndex + 3, 8).Value, "#,###;-#,###;-")
 End Sub

 （ｍｏｍｏ）

 わ～！　ありがとうございます。
 今回のコード、色々使いまわしできそうで本当に感謝です。

 あの…それでですね、書き直していただいたコードで
 チェックボックスの計算が合わないのです。
 チェックボックスに連携するそれぞれの TextBox1 が下記のようになる時、

 TextBox1　-33
 TextBox2　-109
 TextBox3　50
 TextBox4　0
 TextBox5　15
 TextBox6　0

 合計であるTextBox12 は -77となるはずなのですが、
 全部が正数で計算されて 207 になってしまっています。
 コードを書き直していただく前までは思うように-77になっていたのですが…。
 どこを直したらよいでしょうか？
 (春一番)

 -があるのですか・・・
 0の時に-と表示されているので-を抜くようにコーディングしています。

 空白の－なのかマイナスの－なのか区別しないといけませんね。

 Private Sub SetCalc()
 Dim i As Long, mySum1 As Double, mySum2 As Double
 For i = 1 To 4
   mySum1 = mySum1 + Val(Me.Controls("TextBox" & i).Value)
 Next i
 For i = 6 To 11
   With Me.Controls("TextBox" & i)
     If .Value <> "" And .Value <> "-" Then
       mySum2 = mySum2 + Val(.Value)
     End If
   End With
 Next i
 Me.TextBox5.Value = Format(mySum1, "#,###;-#,###;-")
 Me.TextBox12.Value = Format(mySum2, "#,###;-#,###;-")
 Me.TextBox13.Value = Format(mySum1 + mySum2, "#,###;-#,###;-")
 Me.金額.Value = Format(Worksheets("Sheet1").Cells(Me.ListBox1.ListIndex + 3, 8).Value, "#,###;-#,###;-")
 End Sub

 もう１つ、TextBox5は集計ですか？　G列？
 もう一度、全てのTextBoxの用途を教えてください。
 なんか行き違いがありそうです。
 （ｍｏｍｏ）

 すみません、先程の説明の№が実際と違ってｍｏｍｏさんを混乱させてしまっています？
 TextBox1　-33
 TextBox2　-109
 TextBox3　50
 TextBox4　0
 TextBox5　15
 TextBox6　0
　　　　↓
 TextBox6　-33
 TextBox7　-109
 TextBox8　50
 TextBox9　0
 TextBox10　15
 TextBox11　0

で

 合計であるTextBox12 は -77となるはずなのですが、
 ですね。

 改めて説明すると
  ListBox1　(B列)
 |→連動　TextBox1 (C列) 
 |　　　　TextBox2 (D列)
 |　　　　TextBox3 (E列) 
 |　　　　TextBox4 (F列)  
 |　　　　→　TextBox5 (TextBox1～4合計)  
 |
 |　　　　　　※どこの合計にも含まれない「金額」というTextBoxもある (H列)
 |
 |→連動　CheckBox　→連動　TextBox6 (J列) 
 |        CheckBox2　　　　 TextBox7 (L列) 
 |        CheckBox3　　　　 TextBox8 (N列) 
 |        CheckBox4　　　　 TextBox9 (P列) 
 |        CheckBox5　　　　 TextBox10 (R列) 
 |        CheckBox6　　　　 TextBox11 (T列) 
 |　　　　　　　　　　　　　→　TextBox12 (TextBox6～11合計)

 TextBox13 (TextBox5+12合計)

 こんな感じで良いでしょうか？
 読み込むセルは問題ないです。

 一番最新のコードだと、実際6桁(例123456)あっても小さいほうから3桁(例123)が、
 TextBox5・12・13とも表示されません…？
 (春一番)

 >先程の説明の№が実際と違ってｍｏｍｏさんを混乱させてしまっています？
 え～　正直かなり混乱してました（笑

 詳しい説明ありがとうございます。
 やっと明確に全体像が見えました＾＾

 以下のようにSetCalcプロシージャを変更してみてください。

 Private Sub SetCalc()
 Dim i As Long, mySum1 As Double, mySum2 As Double
 For i = 1 To 4
   If IsNumeric(Me.Controls("TextBox" & i).Value) Then
     mySum1 = mySum1 + Me.Controls("TextBox" & i).Value
   End If
 Next i
 For i = 6 To 11
   With Me.Controls("TextBox" & i)
     If IsNumeric(.Value) Then
       mySum2 = mySum2 + .Value
     End If
   End With
 Next i
 Me.TextBox5.Value = Format(mySum1, "#,###;-#,###;-")
 Me.TextBox12.Value = Format(mySum2, "#,###;-#,###;-")
 Me.TextBox13.Value = Format(mySum1 + mySum2, "#,###;-#,###;-")
 Me.金額.Value = Format(Worksheets("Sheet1").Cells(Me.ListBox1.ListIndex + 3, 8).Value, "#,###;-#,###;-")
 End Sub

 （ｍｏｍｏ）

 本当に本当に混乱させてしまってすみません…。
 理想のフォームが完成しました！
 ばっちりです！！

 長々と･･･本当に長々とお付き合いいただいて有難うございました。
 感謝いたします。
 (春一番)

コメント返信：

[ 一覧(最新更新順) ]

[[20100311125825]] 『VBAユーザーフォーム内での計算』（春一番） ページの最後に飛ぶ

[[20100311125825]]　『VBAユーザーフォーム内での計算』（春一番）　ページの最後に飛ぶ