Excel 2010 プルダウンメニューを塗りつぶしたセルの数をカウントしたい。

お世話になっています。よろしくお願いします。

この間ここで、チェックボックスをチェックすると、塗りつぶしできる方法を教えていただきました。

今度は、表題にもあるように、塗りつぶしたセルの数を数えておきたいのです。

過去ログに、塗りつぶしのセルのカウント方法として、以下のようなものを見つけました。

https://oshiete.goo.ne.jp/qa/2000523.html

これでもカウントできるのですが、変化があるたびに、enterが必要です。

リアルタイムに反映させる方法はないのでしょうか？

ご存知の方おられましたらご教授願います。

No.1 ベストアンサー 回答者： WindFaller 回答日時： 2015/02/26 14:58

こんにちは。

すみません、本日は、ちょった諸用で書き込みが遅くなってしまいました。

http://oshiete.goo.ne.jp/qa/8930446.html
この内容ですが、valox 様は、誤解されていたようです。

まず、各々のチェックボックスには、マクロ不要なのです。

CheckBoxesMacro とSettingMacro　を標準モジュールに貼り付け、
次に、一回だけ、SettingMacroを実行するだけで、
CheckBoxesMacroの登録マクロが、CheckBoxesMacroに書き換わるのです。

そうすると、各々のCheckBoxesは、それぞれに働くようになるというものなのです。
このやり方を、VB6では、『コントロール配列』と言います。
もう一度、お試しになることをお勧めます。

'//
Sub SettingMacro()
　Dim cb As Object
　'一回きりの設定用のマクロです。
　If ActiveSheet.CheckBoxes.Count = 0 Then MsgBox "シートが違います。", vbExclamation: Exit Sub
　For Each cb In ActiveSheet.CheckBoxes
　　cb.OnAction = "'" & ThisWorkbook.Name & "'!" & "CheckBoxesMacro" '※
　Next cb
End Sub

'標準モジュールのみ
Sub CheckBoxesMacro()
　With Worksheets("Sheet1")　'シート限定
　　With .Shapes(Application.Caller).DrawingObject
　　　If .Value = xlOn Then
　　　　.TopLeftCell.Offset(0, 6).Interior.ColorIndex = 46
　　　Else
　　　　.TopLeftCell.Offset(0, 6).Interior.ColorIndex = xlColorIndexNone
　　　End If
　　End With
　End With
End Sub
'//

さて、色塗りセルのユーザー定義関数の問題ですが、

>これでもカウントできるのですが、変化があるたびに、enterが必要です。
>リアルタイムに反映させる方法はないのでしょうか？
とおっしゃるようですと、その色塗りセルを数えるユーザー定義関数は、ちゃんと動くということですね。

これを働かせるためには、Calculateイベントというものを発生させないといけません。
ただ、リンク先の色つきセルを数えるユーザー定義関数は、こちらの環境では、再帰が起きて、エラーが出てしまうので、今の時点では私のところでは使えません。(こちらのバージョンは、Excel 2010です）まだ、このバージョンの細かい部分はみきれていないのです。

そこで、上記のCheckBoesMacroの中に、
Application.CalculateFull
の一行を加えることはできませんか？

　　　　.TopLeftCell.Offset(0, 6).Interior.ColorIndex = xlColorIndexNone
　　　End If
　　End With
　End With
　Application.CalculateFull　'ここら辺り（本来はどこでもよいです）
End Sub　'最後の行

もし、ここで、色塗りセルを数えるユーザー定義関数が稼働するようなら、この後は不要です。
「Application.CalculateFull」これが、Enterの代わりをしてくれます。

同じく、標準モジュールの中で、このようなユーザー定義関数を貼り付け、

Function CheckBoxesCount()
Dim i As Long
Dim cnt As Long
With ActiveSheet
For i = 1 To .CheckBoxes.Count
　If .CheckBoxes(i).Value = xlOn Then
　　cnt = cnt + 1
　End If
Next i
End With
CheckBoxesCount = cnt
End Function

ワークシート上で、
=CheckBoxesCount()
とすれば、そのシートにある全部のCheckBoxのオンを数えます。
ユーザー定義関数については、どちらでも、良いと思います。
ただ、ColorCellCountというユーザー定義関数は、見た目以上に複雑に出来ているようです。

この回答へのお礼

回答くださりありがとうございます。結論から言うと、うまくいきました。ありがとうございます。”「Application.CalculateFull」これが、Enterの代わりをしてくれます。”これが大事だと思いました。ベストアンサーは、少し考えてからつけたいと思います。ありがとうございました。

お礼日時：2015/02/26 23:55

No.2 回答者： tom04 回答日時： 2015/02/26 20:13

こんばんは！

横からお邪魔します。

ユーザー定義関数を使用してやってみました。
↓の画像のチェックボックスは「フォームコントロール」のチェックボックスにしています。
そして各チェックボックスはA1～A5セルに対応していて、チェックがONの場合は「赤」に塗りつぶすようにしてみました。
まず、各チェックボックスのマクロは↓のコードにしてみてください。

Sub チェック1_Click()
With Range("A1")
If ActiveSheet.CheckBoxes("チェック 1") = xlOn Then
.Interior.ColorIndex = 3
Else
.Interior.ColorIndex = xlNone
End If
End With
ActiveSheet.Calculate
End Sub
Sub チェック2_Click()
With Range("A2")
If ActiveSheet.CheckBoxes("チェック 2") = xlOn Then
.Interior.ColorIndex = 3
Else
.Interior.ColorIndex = xlNone
End If
End With
ActiveSheet.Calculate
End Sub
Sub チェック3_Click()
With Range("A3")
If ActiveSheet.CheckBoxes("チェック 3") = xlOn Then
.Interior.ColorIndex = 3
Else
.Interior.ColorIndex = xlNone
End If
End With
ActiveSheet.Calculate
End Sub
Sub チェック4_Click()
With Range("A4")
If ActiveSheet.CheckBoxes("チェック 4") = xlOn Then
.Interior.ColorIndex = 3
Else
.Interior.ColorIndex = xlNone
End If
End With
ActiveSheet.Calculate
End Sub
Sub チェック5_Click()
With Range("A5")
If ActiveSheet.CheckBoxes("チェック 5") = xlOn Then
.Interior.ColorIndex = 3
Else
.Interior.ColorIndex = xlNone
End If
End With
ActiveSheet.Calculate
End Sub

次に
メニュー　→　挿入　→　標準モジュール
として新しいVBE画面に↓のコードを追加します（ユーザー定義関数）

Function kosuu(範囲 As Range)
Dim c As Range, cnt As Long
For Each c In 範囲
If c.Interior.ColorIndex <> xlNone Then
cnt = cnt + 1
End If
Next c
kosuu = cnt
End Function

そして画像ではE1セルに
=kosuu(A1:A5)
としています。

これでチャックが入ったり外れたりするたびに
色付きセルの個数が表示されると思います。m(_ _)m

この回答へのお礼

回答くださりありがとうございます。いろいろとやり方があるようで勉強になります。ありがとうございました。

お礼日時：2015/02/26 23:51

No.3 回答者： cj_mover 回答日時： 2015/02/27 08:44

こんにちは、お邪魔します。

私なりの解答というのも考えてはみたのですが、
今の流れでそれを提示しても混乱を招くと思いますので、今回は敢えて
他の回答者さんの手による解決にお任せする前提で、
気が付いたことを書いてみようと思います。
より良き解決へと帰結することを願うばかりで他意はありませんのでご了承を。

application.CalculateFull
（必要のない再計算まで求めるのはどうかと思いました）
よりは、
sheet.Calculate
（セル数式より先に条件付き書式などを再計算させる不思議なメソッドで、
　使い途は限定されますが今回の例ではこれでも十分）
それよりも計算する範囲を限定して、
range.Calculate
（一般的にsheet.Calculateよりは的確な結果が出せるメソッドです）
を使えるならその方が宜しいかと。

ユーザー定義関数では、
"Activeなにがし"というプロパティを避けた方がいいです。
それこそApplication.CalculateFullを実行する前提なら整合性を取る意味でも、
ActiveSheetは、Application.ThisCell.Worksheet、とかで参照するのが
安全で妥当です。

フォームコントロールに１対１で登録するマクロの中の記述としても
ActiveSheetは普通は避けて、具体的なシートを指定するべきでしょう。
１対１で登録するのなら、その方が自然で、特に手間も掛からないでしょう。
余談ですが、最近関連した質問に応えています。
https://oshiete.goo.ne.jp/qa/8929895
参考になることがあるかも知れません。
強いて付け足すなら、フォームコントロールは
ロックとシートの保護でも付け足さない限り、
何かの拍子で簡単に位置を移動してしまうことがあるので、
.TopLeftCell では思わぬ結果を招く場合もあり得ます。
その点を勘案されて＃２様のように１対１の処理を
提案なさる意図は解ります。でも、それならActiveXコンントロールの方が
却って扱い易いように思うのです。

ところで、＃１様ご提示の様に、多対１で登録するマクロをお使いになる場合は、
そのマクロを通った時には必ずカウントが変更になるのですから、
出力先を指定して、そのマクロから出力した方が
ユーザー定義関数で後から追いかけるよりも
スッキリするような気がするのですが、いかがでしょう。
関数である必要があるのかないのか、一度は確認してみたい処です。

以上になりますが、これはこれで、設計上の提案、という意味で、
ひとつの回答スタイルにはなっている、と私は考えています。
必ずしも、他の回答者様だけに向けたコメントではありませんので。

何か不足があれば私からもお応えします。

この回答へのお礼

回答くださりありがとうございます。本当にVBAにはいろんな方法があるのだと思いました。とりあえず、参考になる部分は、今後自分のものになるよう努力したいと思います。ありがとうございました。

お礼日時：2015/02/27 20:11

No.4 回答者： WindFaller 回答日時： 2015/02/27 12:03

こんにちは。

#3のcj_mover様へ

>他の回答者様だけに向けたコメントではありませんので。
とはおっしゃられても、明らかに私宛のものだと思います。
それで、このままにされたのでは、私の書き込みには「問題あり」ということだけで終わってしまいます。本来は、#3のような発言は、トラブルの元だと思いますから、お控えになられたほうが良いかと存じます。今回は、ご質問者様をまじえてなので、その指摘に、いくつか弁明をさせてもらいます。

こちらは、まったく認知していないことならともかく、こちらは闇雲に、その選択をしているのではないことぐらいはご想像がつくはずて、ご指摘の前に、ご自身でいくつかのポイントを確認の上で、発言なされることを要望いたします。

>application.CalculateFull
>（必要のない再計算まで求めるのはどうかと思いました）
>よりは、
>sheet.Calculate

その点はご質問者様のあずかり知らないところにあるので、詳しく触れていませんでした。
逆に、こちら側から、問題点を挙げておきます。

私自身のPC(Window7)＋Excel2010特定の環境では、Calculateだけでは、値の更新もないし、Volatileでも、うまく行かないのでのことでした。以前なら、こうした掲示板では、Application.Volatileを教えるということで、解決というくだりになっていました。したがって、このような問題は発生しませんでしたし、ご質問者様のリンク先で取り上げられたユーザー定義関数の時代とは、明らかに差異があるようなのです。

それが、私の特殊な環境によるものかどうかは不明です。ただ、アンチウィルス・ソフトであろうが、なんであろうが、同じ現象は、誰かの環境でも起きうることと考え、私のミスや軽率さということではなく、いくつか選択肢の中で、間違えのない所を選んだ結果なのです。

Application.CalculateFullによる問題というのは、全部に再計算の命令が走るということで、例えば、計算の遅い配列数式などに、全部の再計算が走ってしまうということです。逆に、これはブック全体を重くするという欠陥にもつながるのは承知しています。だから、別のご質問者様のイベント・ドリブン型には、それをためらいました。私の予測では、おそらく、この種の問題は、Excel VBA自身では解決しない方向性にあるということだと思っています。

私個人、今まで、このような手法に頼ったことはありません。新バージョン(2007以降）でのVBAでの書き込みは、今回の期間（昨年の秋ごろ）が最初で、長い間Excel 2003を使ってきたのときとは、思惑と違う点が多々出てきています。

1ヶ月前から、この問題の解決方法として、暗中模索で、CalculateFullに落ち着いているということです。逆に、Exceの範囲内で、この件について、良い案があったらお教えいただきたいほどです。最近の掲示板の傾向として、ユーザー定義関数（関数型プロシージャではなく）の出現は低くなっているようです。

むろん、他にもいくつかテクニックは用意はしているのですが、まるで近所の買い物にいく乗り物に高級車を使ってというような感じやら、旧式のオート三輪で出かけるようなものなので、この場ではふさわしくないと却下しました。

>ユーザー定義関数で後から追いかけるよりも
> スッキリするような気がするのですが、いかがでしょう。
> 関数である必要があるのかないのか、一度は確認してみたい処です。

それも考えましたが、あえて、ユーザー定義関数を持って来られたので、その方法に従いました。
それと、一応、前の回のものは終わったものとなっているからで、最初からの提示であれば、それを選んでいたと思います。いずれにしても、ご質問者様は、前回のこちらの思惑とは違った反応を示したことがきっかけで、あまりご負担を掛けたくないというのが、私の選択に影響を与えています。

これは、選択の余地があるのは分っています。私個人はどうするのか、というのは、後になってから、ああすればよい、こうすればよいと思う心情は分かるのですが、最初の設計からの話は、行きがかりで戻せない部分があるのです。ただし、そこでエラーが出ていれば、また別です。

また、私は、ご質問者様の質問や環境に従うということは、経験的に、この世界の一つのルールか、と思っています。初めから、掲示板に自分の思うままのコード提示したり、他人のミスを指摘しても、近年、質問者様たちには声は届かぬのは、どこの掲示板でも同じようです。OkWaveとの分離で、身勝手な掲示は減り、まともなレベルには戻ったようですが、それでも、初心者にも分かるレベルの掲示を提示した方が良いことが多いのです。多くのVBAのベテランの人々は、掲示板から消えていき、自分の世界の中に入ってしまっています。それも、やむを得ないことです。

>ユーザー定義関数では、
>"Activeなにがし"というプロパティを避けた方がいいです。
個別の問題の話で、標準モジュールで、フォームコントロールのCheckBoxes の親オブジェクトなしでは、エラーになります。それで、一般論で、ご指摘されても、私としては、困ったことだと思います。一般論としては、避けたほうがいいとは思うのですが、それぞれ個別のオブジェクトには、違った問題があるし、それでエラーになるなら、もう書き方のスタイルなどは話にならないと思います。前回もここで同様な話がありましたが、それでは、一体、何が悪いのか、と切換されたら答えることはできないはずです。ユーザー定義関数では、個別のオブジェクトを指定しません、というのは私自身が書いた気がしますが、あくまでも、フォームコントロール由来で、実際に何が悪いのかということも言えないはずです。

それとも、親オブジェクトを入れないで、シートモジュールに書くべきだとしたら、それは、書かないだけのことになるというだけで、本質的には何も変わらないはずです。そもそも、Function型のコードを、シートモジュールに置く意味さえ、一般的ではなくなってしまいます。

>何かの拍子で簡単に位置を移動してしまうことがあるので、
>.TopLeftCell では思わぬ結果を招く場合もあり得ます。
これも、織り込み済みです。ご質問者様の環境で、セルの高さに注目したのです。
選択肢にはありますが、それを言い始めたら、根底までを崩すことになりませんか？

今、出されている条件の中で選んだものであって、行を、コントロールの位置に対して特定せずに、コントロールのIndexを元にセル塗りつけするセルを選択することも可能です。この程度の環境の容認は、ご質問者様を信頼してもよいのかと思います。それに、現行のExcelでは、フォームコントロールの移動には、確認を求められますので、意図的に行わなければ、そういう問題は発生しません。

それに、こういう物理的な問題を出され、時には、人為的な問題を出されたら、それはキリがないと思います。一体、製作者側は、どこまで、サポートすべきか、という根本的な問題に還元してしまうと思います。だから、人為的な問題（human error)に目を向けずにExcelマクロが悪いのだ、という短絡的な発想にまで発展する人さえいます。

以上が、私の#1の回答の裏の事情です。それを、どう感ずるのか別として、私としては、ベストではなくても、今の状態からベターなものを選んだ結果です。

この回答へのお礼

回答くださりありがとうございました。なんだかややこしくなってきたので、そろそろ打ち切ろうと思います。私の当初の目的は達成されていますので、私の独断と偏見でベストアンサーを決めたいと思います。ただ、この手の掲示板の使われ方や、変遷については回答者様と同感です。私自身よく利用させていただいております。また、私の場合質問者になることしかないので、偏っているかもしれませんが、回答者様のご意見は、社風を反映したごもっともなご意見だと思います。

お礼日時：2015/02/27 20:22