シート上のオプションボタンを削除するVBAコード

vbaopbtnformdeleyecatch

オプションボタンを削除するVBAを説明します。
コントロールを設置する注意点として、変更の時は、それまでのものを一旦削除することです。

それで重複設置を避けることができます。

こんにちは、じゅんぱ店長(@junpa33)です。

今回は、オプションボタンを削除するVBAコードについてです。

使っているうちに、設置したオプションボタンを変更・修正をしないといけない場合が出てきます。

オプションボタンを修正や変更する時には、注意しておくことがあります。

オプションボタンは、図形描画系と同じShapesコレクションに属します。

なので図形描画と同じで、オプションボタンを上書きして修正変更すると、オブジェクトが(2重3重の)重なった設置になってしまいます。

つまり、単にボタンの設置コードを再実行するのではなく、変更したい部分のオプションボタンを一旦削除しておくことが必要です。

オプションボタンのシート設置記事編成

オプションボタンを削除するVBA

vbaopbtnform026

オプションボタンを削除する方法は、

シート上の操作では、「Ctrl」+「左クリック」でボタンを続けて選択して「Delete」です。操作は単純ですが、20個30個のボタン削除となると結構手間な作業となります。

けれども、VBAコードで削除を行うと手間暇かけずに、1回のコード実行で行うことが出来ます。

オプションボタンを削除するパターンはいくつかありますが、今回は

  • 選択項目のグループを一度に削除する。(選択項目の更新・入れ替えなどの時)
  • グループ内の選択項目の一部を削除する。(選択項目が無効になった時)
  • オプションボタンを全面的に削除する。(選択項目を含め、全てを白紙にする時)

を説明していきます。

オプションボタンを設置するところまでのVBA

先回の記事でも説明しましたが、オプションボタンを削除するときに、ボタングループを作成しておくと選択項目のボタンを丸ごと削除するときに便利です。

今までに説明を行った内容のまとめです。ここで「ボタンをグループにします」が事前に準備しておくグループ作成のコードになります。

VBA
Sub フォームCNTLオプションボタン16()
    Dim s, t, n As Long
    Dim ws As Worksheet
        Set ws = ActiveSheet
        For t = 2 To 6 Step 2
'グループボックスを設置
            ws.Range("B1,D1,F1").ColumnWidth = 10.5
            With Range(Cells(1, t), Cells(31, t))
                 ws.GroupBoxes.Add(.Left, .Top, _
                            .Width, .Height).Select
                 With Selection
                     .Characters.Text = "グループ" & t & "列"
                     .Name = "ボタンGrp" & t & "列"
                 End With
            End With
'セルにオプションボタンを設置
            n = 1
            For s = 2 To 31
                With Cells(s, t)
                    ws.OptionButtons.Add(.Left, .Top, _
                                .Width, .Height).Select
                    With Selection
                        .Characters.Text = "ボタン" & t & "列" & n
                        .Name = "botan" & t & "_" & n
                        .LinkedCell = Cells(1, t).Address
                    End With
                End With
                n = n + 1
            Next s
'ボタンをグループにします
            For Each ob In ws.OptionButtons
                If ob.Name Like "botan" & t & "*" Then
                    ob.Select Replace:=False
                End If
            Next ob
            Selection.ShapeRange.Group.Name = "グループ" & t
            Range("A1").Select
'リンクセルの表示設定
            With ws.Cells(1, t)
                .Select
                With .Font
                    .Size = 6
                    .Color = RGB(255, 255, 255)
                End With
            End With
        Next t
End Sub
vbaopbtnform020

選択項目のグループを一度に削除

「オプションボタンの一部だけ削除する」ということにもつながる内容です。

オプションボタンを纏める

'ボタンをグループにします
            For Each ob In ws.OptionButtons
                If ob.Name Like "botan" & t & "*" Then
                    ob.Select Replace:=False
                End If
            Next ob
            Selection.ShapeRange.Group.Name = "グループ" & t
            Range("A1").Select

このグループ名「”グループ” & t」が付いている ” オプションボタンの集まり ” を纏めて削除することが出来ます。

ボタン名を選択する「”botan” & t & “*”」の部分を修正して ” ボタンの集まり ” を変更することが出来ます。

グループのオプションボタンを削除する

グループでまとめたオプションボタンを削除します。

選択範囲を纏めるグループボックスも同時に削除する必要があります。

VBA
Sub フォームCNTLオプションボタン17()
    Dim m As Long
    Dim ws As Worksheet
        Set ws = ActiveSheet
        m = 4
        On Error Resume Next
        With Range(Cells(1, 2), Cells(31, 2))
            ws.Shapes("グループ" & m).Select
            Selection.Delete
            ws.GroupBoxes("ボタンGrp" & m & "列").Select
            Selection.Delete
        End With
        On Error GoTo 0
        Cells(1, m).Clear
End Sub

グループ名の番号を「m」として「4」を直接指定していますが、

入力ボックスでその都度、グループ名を指定するようにすれば汎用性が広がります。

「Group」か「GroupBOX」のどちらかでも設定されていない(あるいは、既に削除されている)とコードエラーが発生します。

「On Error Resume Next」から「On Error GoTo 0」の間に、削除コードを挟んでエラーストップを回避します。

最後にリンク先セルの値をクリアしておきます。

vbaopbtnform021

グループ内の選択項目の一部を削除

例えば選択項目の中で、「ボタン13」の項目が不要になった場合の「横通し」の削除の方法です。

「ボタン2列13」「ボタン4列13」「ボタン6列13」を削除します。

基本的なVBAコードはコレ

VBA
Sub フォームCNTLオプションボタン18()
    Dim r As Long
    Dim ws As Worksheet
    Dim ob As OptionButton
        Set ws = ActiveSheet
        r = 13
        For Each ob In ws.OptionButtons
            If ob.Name Like "botan" & "*" & r Then
                ob.Delete
            End If
        Next ob
End Sub

コード内で「r=13」とボタン番号を直接指定していますが、「入力ボックス」でその都度指定できるようにすれば、コードの自由度が上がります。

シートにあるオプションボタンすべてを調べて「ボタン番号13」を見つけ削除します。

オプションボタンがグループ化されている場合は、個別要素のオプションボタンを削除することが出来ません。

グループ化したオプションボタンはグループ解除する

オプションボタンを(「グループボックス」でない方の)クループ化していると、少々厄介です。

最初にグループ化を解除しないといけません。

シート上の操作では、グループ化されたオプションボタンでも、その要素の1つのボタンだけの削除は可能です。

同じことをVBAコードから操作を行った場合、無反応のまま実行コードが終了します。つまり削除することが出来ません。

コードの実行は、コードエラーでストップしたわけではありません。正常終了です。

これには理由があります。

記事頭のオプションボタンの例示で解説します。

vbaopbtnform020

この合計90個のオプションボタンは、列ごとに「グループボックス」で値のグループ化と、列ごとの「グループ化」で設置のグループ化を行っています。

次のVBAコードでこのシート上にあるすべてのシェイプオブジェクトの名前を取り出します。どのようなオブジェクトがあるかを調べてみます。

VBA
Sub フォームCNTLオプションボタン48()
'シェイプオブジェクト名を全羅列する
    Dim r As Long
    Dim ws As Worksheet
    Dim obj As Shape
        Set ws = ActiveSheet
        For Each obj In ws.Shapes
            Debug.Print obj.Name
        Next obj
End Sub
vbaopbtnform022

結果、「グループボックス名の3つ」と「グループ名の3つ」のみしか認識されていないことが分かりました。

それでは、グループ4のグループを解除してみます。

vbaopbtnform023

グループ4のグループを解除することで、その要素のボタンが認識されました。

この後に「フォームCNTLオプションボタン18」プロシージャーを実行すると

vbaopbtnform024

認識された「ボタン4列13」が削除されました。

このように、「グループ化」を解除しておかないと、個別要素としてのオプションボタンを削除することがでないということです。

グループ化された要素の一部のオプションボタンを削除する

削除を実行するコードの前にグループを解除するコードを記述しておきます。

VBA
Sub フォームCNTLオプションボタン18改()
    Dim r, s, t As Long
    Dim ws As Worksheet
    Dim ob As OptionButton
    Dim Grp As Shape
        Set ws = ActiveSheet
'オプションボタングループを解除する
        For Each Grp In ws.Shapes
            If Grp.Name Like "グループ*" Then
                Grp.Select
                Selection.ShapeRange.Ungroup
            End If
        Next
        Range("A1").Select
'指定のオプションボタンを削除する
        r = 13
        For t = 2 To 6 Step 2
            For Each ob In ws.OptionButtons
                If ob.Name = "botan" & t & "_" & r Then
                    ob.Delete
                End If
            Next ob
        Next t
End Sub

グループを解除するには「Ungroupメソッド」を使用します。

vbaopbtnform025

「ボタン13」を削除することが出来ました。

必要であれば削除後に、再度「グループ化」を行っておきましょう。

一部の要素のオプションボタンを削除した後の処理

オプションボタンを一部だけ削除すると、グループ内のオプションボタンが減数します。

上の例で言うと、

「ボタン13」が削除され、それぞれのグループボックス内でのボタンクリック時の戻り値が変化します。

戻り値をどのようにデータに生かしているかは、それぞれ異なりますが、適切値になる様に必ず修正をしておきましょう。

オプションボタンを全面的に削除

シートそのものを全くの白紙に戻します。

VBA
Sub フォームCNTLオプションボタン22()
        With ActiveSheet
            .Cells.Clear
            .DrawingObjects.Delete
            .Cells.UseStandardHeight = True
            .Cells.UseStandardWidth = True
        End With
End Sub

オプションボタンだけでなく、全てを削除クリアします。

使用時は注意が必要です。

オプションボタンの削除 まとめ

vbaopbtnform027

オプションボタンの削除は、

選択項目を修正変更する時にそれに伴って、オプションボタンを再設置する場合に必要になります。

オプションボタンを削除しなくて再設置すると、2重設置、3重設置と積み重なることになります。

オプションボタンの設置のVBAを組むときは、まずそれまでのオプションボタンを、削除するコードから記述を始めると良いでしょう。

エクセルVBAを独習するのに参考書は欠かせません。 参考書選びは自分に合った「相棒」にできるものを選んでいきたいです。

vbastudyeyecatch2 エクセルVBAの独習でおすすめ参考書を7冊選ぶ。良書との出会いは大切です

今回の記事はここまでです。   最後までご覧いただき有難うございました。

エクセルVBA最速理解で必要な知識を集めよう!

エクセルVBA業務ツールで日常の業務改善を行いましょう。

VBAコードの記述記事においては、その記述には細心の注意をしたつもりですが、掲載のVBAコードは動作を保証するものではりません。 あくまでVBAの情報の一例として掲載しています。 掲載のVBAコードのご使用は、自己責任でご判断ください。 万一データ破損等の損害が発生しても当方では責任は負いません。

アンケートでポイ活しよう!!

アンケートに答えれば答えるほど ”使える” ポイントがたまります。

NTTコム サーチ

af_banner01

Dstyle web

dstyleweb_logo
dstyle_320x50-min