エクセルでチェックボックスをクリックしたらセルに数値を入力＆消去

質問

下記のような表を作成しています。
□はチェックボックス（コントロールツールボックスから作成したもの）で、
A1のチェックボックスにチェックするとD1とE1に○印が入り、
チェックを消すと○印も消えるようにしたいのです。
（B,Cのチェックボックスは違うセルに○印を入れるのですが、
他のチェックボックスと○を付ける項目が重複している所もあります）
　ＡＢＣＤＥＦＧ
１□□□
２□□□

コードはこのようなものを書きました。
Private Sub CheckBox1_Click()
Dim teiban As Integer
teiban = ActiveSheet.CheckBox1.Value
Select Case teiban
Case True
Range("D1,E1").Value = "○"
Case False
Range("D1,E1").Value = ""
Case Else
Range("D1,E1").Value = ""
End Select
End Sub

同じ列にあるチェックボックスは違う行で同じ列に○印が入るのですが、
コードはいちいちチェックボックスごとに書かなければいけないでしょうか？

それと、行数を増やす場合があるのですが、1行目をコピーして3行目に
貼り付けてもチェックボックスはコピーされません。
何か方法がありますでしょうか？

もしチェックボックスではこのような事ができないのであれば
他にやり方などあれば教えていただけたらと思います。

使用しているのはWINDOWS98,EXCEL97です。
ご存知の方、よろしくお願いします。

回答No.4

nishi6 · ベストアンサー

もう回答してもいいんでしょうか・・・？

コントロールツールボックスのチェックボックスはデザインモードならコピーできるはずです。
ただし、この場合は各チェックボックス毎にコードを書く必要があります。共通ルーチンを呼ぶようにできますが、イベントをまとめてつかまえられないので、コントロールの数分イベントを書く必要があるでしょう。VBAには、VBのようにコントロール配列がないからです。（ユーザーフォーム上ではコントロール配列風な書き方もできますが、シート上では行ったことがありません。余りその気ももおきませんが・・・）

フォームのチェックボックスを使えば、チェックボックスを貼り付けるだけで、コードを追加しなくても（１つのコードで）対応可能です。下はその例です。１つのマクロしかありませんが、理屈では何個のチェックボックスがあってももかまいません。必要な分だけチェックボックスをコピーしてください。（何個まで可能かは確認できていません）
フォームとコントロールツールボックスのコントロールの特性の違いを理解して使うべきでしょう。

作り方
０．下のコードを標準モジュールに貼り付けます。
　　　※A、B、C列にチェックボックスがある例です。
　　　※登録したマクロ内で、チェックボックスの位置を調べています。
　　　※位置が決れば、チェックボックスの値で処理が決定できる理屈です。

１．フォームのチェックボックスを使い、セルA1に納まるように配置します。

２．セルA1のチェックボックスを右クリックし、下のマクロを登録します。

３．セルA1のチェックボックスをコピーして、B1、C1に貼り付けます。

４．１行目を選択して、３行目にコピーします。後は必要な回数繰り返します。
　　　※下のコードのB、C列の処理は例です。実状にあうように修正して下さい。
　　　　　　（Excel97 で確認）

ここから
↓
Sub checkBox_MacroTest()
　　Dim chkboxVal As Boolean　　'チェックボックスの値
　　Dim rw As Long　　　　　　　'チェックボックスのある行
　　Dim col As Integer　　　　　'チェックボックスのある列

　　'チェックボックスの値
　　chkboxVal = (ActiveSheet.CheckBoxes(Application.Caller).Value = 1)

　　'チェックボックスの位置を調べる
　　ActiveSheet.CheckBoxes(Application.Caller).Select
　　　　rw = Selection.TopLeftCell.Row
　　　　col = Selection.TopLeftCell.Column
　　　　Selection.TopLeftCell.Select

　　'位置によって『○』を付ける位置を決定する。Falseなら消す。
　　Select Case col
　　　　Case 1　　　'A列のチェックボックス。D、E列を操作している
　　　　　　Cells(rw, 4) = IIf(chkboxVal, "○", "")
　　　　　　Cells(rw, 5) = IIf(chkboxVal, "○", "")
　　　　Case 2　　　'B列のチェックボックス。F、G列を操作している(例)
　　　　　　Cells(rw, 6) = IIf(chkboxVal, "○", "")
　　　　　　Cells(rw, 7) = IIf(chkboxVal, "○", "")
　　　　Case 3　　　'C列のチェックボックス。H、I列を操作している(例)
　　　　　　Cells(rw, 8) = IIf(chkboxVal, "○", "")
　　　　　　Cells(rw, 9) = IIf(chkboxVal, "○", "")
　　End Select
End Sub

お礼

ご回答ありがとうございます。
コントロールツールボックスのチェックボックスを使うより
フォームのチェックボックスの方がこの場合はいいのですね。
教えていただいたマクロで、思っていた通りのことができました！
これで作業がだいぶはかどります。
ありがとうございました！

回答No.5

imogasi

判らなければ、回答に入れなきゃ良いのですが、興味があって、既回答を見ているのですが複雑で、下記を入れることをお許し下さい。
（１）私もマクロも好きですが、使わなくても下記でどうですか。下記の話題が出ていないのですが
　　　　○LinkedCellの使用（エクセルVBAらしいと思う
○グループボックスの使用
（２）どこか使わないセルを１つみつける。今仮にＡ２にします。（本当はA１より遠くのセルでしょうが。）
チェックボックスのプロパティのLinkedCellにＡ２をいれます。（VBAからでも入れられると思います。）
Ａ１セルに=If(a2=True,"○","")といれ、Ｄ１に=A1,
E1に=A1といれると、チェックボックスをＯＮにすると、
Ａ１，Ｅ１，Ｄ１とも○になり、ＯＦＦにすると消えます。Ａ２の文字ＴＲＵＥ等は邪魔なら、字体の色を白にして見えなくします。
（３）質問の重点が、質問の文章文字数が多い割りに判り難い。
＞A1のチェックボックス－－－＞Ａ１セルの位置に配置すると言うことですか。
＞同じ列にあるチェックボックスは違う行で同じ列に○印が入るのですが、 －－－－＞チェックボックスには行も
列もないはず。ただ、並べた見た目の位置のことですか。
グループボックス・コントロールは使えないでしょうか。
オプションボタンなら確実に使えます。
オプションボタン例えば１２個は本来１つしかONにならないが、４つのグループボックスをかぶせると、４個の状態を取れるようですし、値を取るのもボックス当たり１つで済むようですが。
＞貼り付けてもチェックボックスはコピーされません。
何か方法がありますでしょうか？ －－－－＞コントロール
は複写できるのではないですか。コントロール配列は
VBAでは今のところ許していないようですが。
まさかVBAのコードではないでしょうね。
（７）「フォームのコントロールのコピーが出来るかどうか」「コントロールの数が多い時、値を一々聞く面倒さの
回避法」などが質問ですか。

お礼

アドバイスありがとうございます。
判り難い文章ですみません…。

(1)(2)LinkedCellは質問する前にいちど試してみましたが、
作業用の列や行を作成すると、それに気づかない人がフォーマットを
操作した場合、コピー洩れ等がありそうなのでやめました。
○印が入るセルに数式を入れるのも、後で○印を●印に手動で変更するなど
他人が操作する場合があるのでできるだけ数式を入力しないようにと
考えています。
○印を最初から全てのセルに入力しておいてフォントを白にし、条件付書式で
フォントを黒にする方法も考えたのですが、同じくセルのデータを変更する
可能性があるので（しかもそれは私が関知できないので）やめました。

(3)
＞A1のチェックボックス－－－＞Ａ１セルの位置に配置すると言うことですか。
＞同じ列にあるチェックボックスは違う行で同じ列に○印が入るのですが、 －－－－＞チェックボックスには行も列もないはず。ただ、並べた見た目の位置のことですか。
その通りです。他に表現する方法を思いつかなかったもので…。
グループボックスとはどういうものなのでしょうか？
コントロール配列というものもよくわかりません。
コントロールツールボックスのチェックボックスはデザインモードでないと
コピーできないようだというのは試してみて判ったのですが、デザインモードで
コピーしてもクリック時に動くコードがコピー元と同じものになっているので、
それをVBAを使って書きかえられないかと思いました。

(7)質問の目的は、
「・チェックボックスをクリックするとチェックボックスが配置されている行の決まったセルに○印を入力もしくは消去できるマクロを作成したい。
・そのフォーマットはパソコンに不慣れな他人が操作するのでできるだけ簡単に(チェックボックスをワンクリックのみ、等)操作できるようにしたい。
・パソコンに不慣れな人が行数を増やしたりデータを一部変更しても変な動きにならないようなものを作成したい。
・コードが沢山あると修正する時に困りそうなのでできるだけ簡単にしたい」
というものなのですが、それを全部他人様に聞くのもどうかと思い、自分でできるだけ作成してみて、わからなかった項目をピックアップして質問しました。
判りづらかったようですみません。これからは気をつけたいと思います。

回答No.3

vbafriend

初めまして。
チェックボックスのコピーは、デザインモードをオンの状態にしてコピー元のチェックボックスの上にマウスポインターを当てて右クリック後表示されたプルダウンメニューの中のコピーをクリックし、貼り付けたい位置にマウスポインターをあわせて右クリックして貼り付けをクリックすると出来ると思います。(当方エクセル２０００で確認済み)

コードは間違っていないと思います。貴方様のコードをコピペして確認しましたら、うまく動作しました。
Private Sub CheckBox1_Click()
チェックボックス１を押した時の動作
End Sub

Private Sub CheckBox2_Click()
チェックボックス２を押した時の動作
End Sub

というようにチェックボックス個々のクリックイベントにコードを書かないとうまく動作しないと思います。

ご不明な点がございましたら、お気軽にご連絡下さい。

補足

ご回答ありがとうございます。
説明が少し足りませんでした…。
このフォーマットはパソコンに不慣れな人も含めて不特定多数の人が利用するので、通常の操作と同じ方法で行を増やせたら、と考えています。

マクロで、デザインモードにしてチェックボックスごと行コピーをし、
コピーした先のチェックボックスのコードに、クリック時のイベントを
新しく書き込むということは可能でしょうか？
マクロの自動記録を使って作ってみたのですがうまくいきませんでした…。

次善策として、チェックボックスの代わりにセルをダブルクリックすると
そのセルにレ印が入り、そのセルに印が入ると他の決まったセルに○印が
入る(レ印を消すと○も消える)という方法も考えました。
そうするとレ印をダブルクリックで入力するところまではできたのですが、
あとがイマイチよくわかりません…。

マクロ初心者なのでお手数をおかけしますが、よろしくお願いします。

回答No.2

uratan

すいません、仕事が入っちゃったので、続きは夜でいいですか？
今夜、他に回答がなければ続きを書かせていただきます。

では。

回答No.1

uratan

マクロについては、ちょっとこっちでやってみますね。

で・・・。
コピーに関しては、ツールのオプションから編集を選んで、「オブジェクトをセルとともに切り取り、コピー、並べ替える」にチェックをすればできますよ。

お礼

問題は解決しそうです。
どうもありがとうございました。

補足

早速のお返事、ありがとうございます。
コピーですが、その項目にはもうチェックが入っていました。
普通のオートシェイプなどは一緒にコピーされるのですが、
チェックボックスはコピーされませんでした…。
マクロ、お手数ですがどうぞよろしくお願いします。