[[20170106170445]]　『チェックボックス（ActiveXコントロール）の挙動ａx（荒瀬　富貴）　ページの最後に飛ぶ

『チェックボックス（ActiveXコントロール）の挙動について』（荒瀬　富貴）

初投稿となります。情報の不足があれば、ご指摘お願いします。

シートに、以下のようなチェックボックスとデータとボタンを配置しています。
チェックボックスとボタンは、ActiveXコントロールです。

 表１　　　　　　 表２　　　　　　 表３

 |□|データ１|　　|□|データ１|　　|□|データ１|
 |□|データ２|　　|□|データ２|　　|□|データ２|
 |□|データ３|　　|□|データ３|　　|□|データ３|
                  |□|データ４|　　|□|データ４|
 [追加][削除]　　　　　　　   　   |□|データ５|
                  [追加][削除]　　　　　　      
                                   [追加][削除]

[追加]ボタンを押すと、表毎に、１行、空のデータ行が末尾に追加されます（データ行が表１→表２、表２→表３になるイメージです）。

チェックボックスにチェックを入れて[削除]ボタンを押すと、表毎に、１行データ列が削除されます。

この[追加]ボタンを押した際のチェックボックスの挙動について、行き詰っています。

VBAで、空のデータ行を表毎に追加することは出来たのですが、チェックボックスを新たに追加することができません。

データの追加と削除を繰り返すため、チェックボックスのオブジェクト名（例えば「CheckBox1」の「1」の部分）は動的に変わります。

また、データの追加時、チェックボックスをLinkedCellプロパティで新しいセルとリンクさせないといけないのですが、そちらも行き詰っています。

何か、良案はございますでしょうか。

以上、宜しくお願い致します。

＜使用 Excel：Excel2016、使用 OS：Windows10 ＞

 とりあえず、現行のコードを、そのまま　コピペでアップされたらよろしいかと思います。

 >>VBAで、空のデータ行を表毎に追加することは出来たのですが、チェックボックスを新たに追加することができません。

 チェックボックスを挿入する操作を行い、それをマクロ記録してみましたか？

 >>データの追加と削除を繰り返すため、チェックボックスのオブジェクト名（例えば「CheckBox1」の「1」の部分）は動的に変わります。

 削除しても、他のチェックボックスの名前はかわりませんよね？

 >>また、データの追加時、チェックボックスをLinkedCellプロパティで新しいセルとリンクさせないといけないのですが、そちらも行き詰っています。

 これはなんとでもなりますが、LinkedCell って必須ですか？
 実際に、どのような使い方をするのかわかりませんが、マクロ処理をするなら、LinkedCell なしでも、まったく問題ないですが。

（β） 2017/01/06(金) 17:52

 追加で。

 そもそも、（ActiveXであろうがフォームコントロールであろうが）　チェックボックスを増やしたり減らしたりするのが
 目的ではないですよね？

 そのチェックボックスで【何かを行う】、その　何か　が　重要ですね。

 チェックボックスを増やしたり減らしたりするぐらいは、手作業でやればいいのではないですか？

 そもそも、この　増やしたり減らしたりするのは、このシートを使う一般ユーザですか？
 それとも、管理者である　荒瀬　富貴　さんですか？

 前者（一般ユーザ）だとしたら、増やしたり減らしたりして何をするんでしょうかね？

（β） 2017/01/06(金) 17:57

チェックボックスコントロールを使わず、セルにチェック文字を書いて代用してはいかがでしょうか？

例えば、A列のみ有効で、右クリックするとチェックがON/OFFする例なぞ。
範囲指定してからの右クリックでもOKですから、オブジェクト使用より使いやすいかと思います。

 Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    Dim R As Range

    If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub
    For Each R In Intersect(Target, Range("A:A"))
        If R.Value = "□" Then
            R.Value = ChrW(9745)
        Else
            R.Value = "□"
        End If
    Next
    Cancel = True
 End Sub
（???） 2017/01/06(金) 18:04

同じく、チェックボックスに見える文字を使用

あぁ、右クリックの方がいいのかなぁ。。。

Option Explicit

Private Sub CommandButton1_Click()

    With Me.Range("A1").CurrentRegion.Resize(, 2)
        .Rows(.Rows.Count + 1).Insert
        .Cells(.Rows.Count + 1, 1).Value = ChrW(&H2610)
    End With
End Sub

Private Sub CommandButton2_Click()

    Dim c As Range

    With Me.Range("A1").CurrentRegion
        For Each c In .Columns(1).Cells
            If c.Value = ChrW(&H2611) Then
                Intersect(.Cells, c.EntireRow).Delete
            End If
        Next
    End With
End Sub

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

    If Target.Column > 1 Then Exit Sub
    If Target.Count > 1 Then Exit Sub
    Cancel = True

    With Target
        If .Value = ChrW(&H2611) Then
            .Value = ChrW(&H2612)
        ElseIf .Value = ChrW(&H2612) Then
            .Value = ChrW(&H2610)
        Else
            .Value = ChrW(&H2611)
        End If
    End With
End Sub

図形っぽいものを作ったり削除したりを頻繁に行う事が、個人的に好きじゃないので。。。
（まっつわん） 2017/01/06(金) 18:33

 繰り返しになりますが、例示されているようなチェックボックス群があったとして、そのチェックボックスを選択したり
 選択を外したりして何を行いたいのか、そして、その　行いたい処理の中で　動的なチェックボックスの増減が、必要なのかどうか。

 そのあたりが全く見えません。
 ですから、

 >>チェックボックスのオブジェクト名（例えば「CheckBox1」の「1」の部分）は動的に変わります。

 変わったとして、その、やりたいことの中で、何か不都合がでるのでしょうか？

 ★ちなみに　４個目のチェックボックスを追加するのは造作もないことですけど、そこに データ4 という文字をいれるんですか？
 　その　データ4 という文字列は、どこから持ってくるんでしょうね？

（β） 2017/01/06(金) 18:39

皆様、お忙しい中ご回答、誠にありがとうございます。

少し、共同開発者と協議しますので、詳細な回答は今しばらくお待ち下さい。
（荒瀬　富貴） 2017/01/06(金) 19:08

β様

コメントありがとうございます。前提をお伝えしておらず、誠に申し訳ございません。
現在、エクセルベースの社内システム（勤務表の自動作成ツール）を開発しており、お伝えした画面は、勤務表を自動作成するための各種設定画面となります。

このツールを使用するのは、社内の一般ユーザで、エクセルに疎い社員に使用してもらう予定です。
このため、システムをガチガチに固めるような作りをしています。

仮に「データ１」～「データ５」と表しましたが、これらはドロップダウンリストとチェックボックスです。
「早出」「日勤」「遅出」などの勤務形態を各従業員に設定します。

部門や時期によって、従業員数が大幅に変わるため、今回、行の追加と削除ボタンを実装しています。
チェックボックスは、行を削除する際に、チェックを入れて削除ボタンを押下してもらう仕組みです（直感的に分かりやすいため）。

ソースについては、全く書けていないのが現状です（すみません、VBA初心者です）。
マクロの記録はしてみました。「Checkbox1」から「Checkbox2」にコピーすることは出来ましたが、複数列に存在するチェックボックスをコピーする際に、どうやって実装すれば良いのかが分からない状態です。

他の方がご指摘してくれた通り、チェックボックスを使わずに、図形を使う方法も現在検討しています。

細やかなご指摘、誠にありがとうございます。

（荒瀬　富貴） 2017/01/10(火) 13:35

???様
まっつわん様

具体的な解決策をありがとうございます。こちらで動作確認したところ、以下のことが分かりました。

１．複数のチェックボックスに（例えば４つ）チェックをつけて削除を実行すると、１度に行が削除されない。

２．チェックボックスのあるセルにカーソルを合わせてバックスペースボタンを押すと、チェックボックスが消えてしまう。

１については、こちらでソースを確認して対応できそうですが、２については、解決策が皆目見当がつかない状態です。

こちらから聞いてばかりで申し訳ございません。何か良案はございますでしょうか。

参考サイト等ございましたら、そちらの提示でも構いません。

どうか、よろしくお願い致します。
（荒瀬　富貴） 2017/01/10(火) 13:41

複数行削除できないのは、ここに書かれていない、貴方のコードが問題ですね。頑張ってください。

チェックボックスは、消えても良いのでは無いですか？消えたということは、チェックしていないという事であり、対象外にすれば良いだけです。説明通りに右クリックせず、BSキーやDELキーを押した人が悪い。元々、空欄を右クリックすると□を表示するようにしているので、すぐ復活できるでしょう。見た目だけの問題かと思いますよ。

または、□枠のないチェック文字 chrW(10004) もありますので、空欄とvだけで表現する事もできます。
（???） 2017/01/10(火) 14:08

ちなみに、行削除で陥りやすい罠として、行を消すと以降の行番号が変わってしまうので、ループ変数とずれてしまうという問題になりがちです。
これは、行末から先頭に向かって、逆順で処理することで対応するのが一般的。
（???） 2017/01/10(火) 14:13

 >１．複数のチェックボックスに（例えば４つ）チェックをつけて削除を実行すると、１度に行が削除されない。

あぁ、いい加減にかいてましたね＾＾；
以下のような表になってるとして。

 ┌─────┬────┬───┐
 │削除フラグ│勤務形態│名前  │
 ├─────┼────┼───┤
 │□        │A       │東京  │
 ├─────┼────┼───┤
 │レ        │b       │大阪  │
 ├─────┼────┼───┤
 │□        │a       │博多  │
 ├─────┼────┼───┤
 │レ        │ｃ      │名古屋│
 ├─────┼────┼───┤
 │レ        │あ      │京都  │
 ├─────┼────┼───┤
 │□        │b       │広島  │
 ├─────┼────┼───┤
 │レ        │c       │仙台  │
 └─────┴────┴───┘

Private Sub CommandButton2_Click()

    Dim c As Range

    Application.ScreenUpdating = False
    With Me.Range("A1").CurrentRegion
        .AutoFilter Field:=1, Criteria1:=ChrW(&H2611)
        Intersect(.Cells, .Offset(1)).EntireRow.Delete
        .AutoFilter
    End With
    Application.ScreenUpdating = True
End Sub

今回は削除部分だけしか見てないので、
他のコードへの影響は考慮してません。

 >２．チェックボックスのあるセルにカーソルを合わせて
 >バックスペースボタンを押すと、チェックボックスが消えてしまう。 
シートにシートの保護を掛けてユーザーに勝手に変更させないように
します。
そのうえで、
ThisWorkbookモジュールに

Private Sub Workbook_Open()

    ActiveSheet.Unprotect
    ActiveSheet.Protect UserInterfaceOnly:=True
End Sub

と記述して、名前を付けて保存して、閉じて、
開きなおします。
つまり、これで、手動での操作は禁止しつつ、
マクロからの値の変更は許可することが出来ます。

あと、ボタン類は一番上に置いておいて方が、いちいち移動しなくて
いいと思いました（個人の感想です）

（まっつわん） 2017/01/10(火) 14:36

追記：

チェックボックスを文字で代用したから、
フィルターで抽出できるし、それを一括で行削除できますね＾＾

まぁ、別にコントロールを使ってもどうにでもなるんでしょうが。。。

（まっつわん） 2017/01/10(火) 14:53

???様
まっつわん様

素早い＆ご丁寧なご回答誠にありがとうございます！
VBAを勉強しながら、頑張って実装してみます！

また分からないことがあれば、こちらでご質問させて頂きますので、どうぞ宜しくお願い致します。

重ね重ねになりますが、ご回答、誠にありがとうございました！
（荒瀬　富貴） 2017/01/10(火) 16:49

ちなみに、チェックボックスをシート上に追加するコードはマクロの記録ででます。

Option Explicit

Sub Macro1()
'
' Macro1 Macro
'

    ActiveSheet.OLEObjects.Add(ClassType:="Forms.CheckBox.1", Link:=False, _
        DisplayAsIcon:=True, Left:=179.25, Top:=251.25, Width:=133.5, Height _
        :=60.75).Select
End Sub

↑を参考に、こんな書き方も一応できます。

Sub test()

    Dim chk As OLEObject

    Set chk = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CheckBox.1")

    With chk
        .Top = ActiveCell.Top
        .Left = ActiveCell.Left
        .Height = ActiveCell.Height
        .Width = ActiveCell.Width
    End With
End Sub

逆にすでに配置されているものから情報を得るには、

Sub test2()

    Dim v As OLEObject

    Set v = ActiveSheet.OLEObjects(1)

    MsgBox TypeName(v.Object)
    MsgBox v.Object.Value
    MsgBox v.TopLeftCell.Address(False, False)
End Sub

こんな感じで得られそうです。
（まっつわん） 2017/01/10(火) 17:31

コメント返信：

[ 一覧(最新更新順) ]

[[20170106170445]] 『チェックボックス（ActiveXコントロール）の挙動ａx（荒瀬 富貴） ページの最後に飛ぶ

[[20170106170445]]　『チェックボックス（ActiveXコントロール）の挙動ａx（荒瀬　富貴）　ページの最後に飛ぶ