ExcelVBAのShapesとIf文でシート上のフォームコントロールを無効にする方法

前回のActiveXコントロールと同様に今回はフォームコントロールを無効にする方法についての話です。ExcelVBAでシートを利用して何らかのシステムや簡易的なマクロを作る場合、ボタンなどの1つや2つ設置することもあるかと思います。

システム作りを進めていると「こういった場合はユーザーにボタンを押させたくない」とか「こういった場合はユーザーにこのボタンだけを押させたい」といった状況も出てくるでしょう。

例えば、処理の流れからある特定のボタンしか押せないようにすることでユーザーを迷わせないようにする、セキュリティ上、ある特定の場合は全てのボタンを押せないようにする、といった状況も考えられます。

もちろんフォームモジュールを使ったフォームを使うほうがシステムを作るには楽でしょう。そうは言っても業務の都合上、機能の都合上どうしても「Excelのシート上にフォームコントロールを使ったシステム」を作らざるを得ない場合もあるかもしれません。

そういった時に、ExcelVBAのコードでシート上のボタンの押せる押せないを何らかのアクションをトリガーとして切り替えられたら便利です。しかし、「シート上に設置したフォームコントロールを無効にする」ためのコードをインターネット上で調べてみたのですがなかなか見つけられませんでした。

フォームモジュールのコントロールを無効にするコードはたくさん見かけるのですが、シート上に設置するフォームコントロールを無効にするコードは見つかりません。自分の探し方が悪かったのかもしれませんが、そもそもシート上に設置したコントロールはExcelの仕様上、無効にはできないようです。

自分の調べた範囲でそのような文章を何度も目にしたのですが、「何とかしてフォームコントロールを無効にできる方法はないか」と考えていたので色々と試行錯誤してみました。

その結果、偶然にも「シート上に設置したフォームコントロールを無効にできる方法」を見つけました。

Excelの開発タブから設置するボタンの種類には大きく「フォームコントロール」と「ActiveXコントロール」があり、以下の前回の過去記事ではActiveXコントロールを「使用不可」にするコードについて説明しました。

• ExcelVBAのOLEObjectsでシート上のActiveXコントロールを使用不可にする方法

今回はシート上に設置したフォームコントロールをShapesとIf文を使って無効にできる方法について説明していきます。

ExcelVBAのShapesとIf文でシートのフォームコントロールを無効にするコード

今回の機能の概要としては、コードを2つ使います。1つ目のコードで指定のフォームコントロール(ボタン等)のEnabledプロパティの状態をFalseにし、2つ目のコードをフォームコントロールのボタンにマクロとして登録します。

その登録したマクロのコード内にIf文を入れてボタンのEnabledプロパティがFalseの時はボタンを押しても機能させない、EnabledプロパティがTrueの時は目的の機能を使えるようにする、という方法になります。

完全にフォームコントロールのボタンを無効にはできませんが、「形」として無効にするというやり方です。

おそらくここまでの説明では何を言っているかわからないと思うので順番に説明してきます。結論としては次の2つのコードを使います。1つだけではできません。ちなみにExcel2013で動作を確認しています。

Sub フォームコントロール()
Dim c
For Each c In Sheets("Sheet1").Shapes
   Sheets("Sheet1").Shapes(c.Name).DrawingObject.Enabled = False
Next c
End Sub

今回は上記のコードに加えて以下のコードも必要になってきます。いずれも標準モジュールに入力します。

Sub テスト1()
Dim c
For Each c In Sheets("Sheet1").Shapes
   If Sheets("Sheet1").Shapes(c.Name).DrawingObject.Enabled = False Then
      MsgBox "aaa"
      Exit Sub
   End If
   MsgBox "bbb"
Next c
End Sub

今回は「フォームコントロール」プロシージャと「テスト1」プロシージャを記述しました。この2つのコードについて順番に解説をしていきます。

1つ目の「フォームコントロール」プロシージャの説明

わかりやすく説明できるように一度以下に再掲します。

Sub フォームコントロール()
Dim c
For Each c In Sheets("Sheet1").Shapes
   Sheets("Sheet1").Shapes(c.Name).DrawingObject.Enabled = False
Next c
End Sub

このコードの意味として、まず2行目で変数cを宣言。3行目のFor Eachステートメントで「Sheet1」シート上にあるフォームコントロールを順番に変数cに格納していきます。

ここがひとつポイントで3行目の「Shapes」でフォームコントロールではなく、ActiveXコントロールの両方とも変数cに格納できます。このShapesコレクションというのは基本的にはシート上にある全ての「描画オブジェクト」を指します。

例えば円とか四角の図形とかグラフ、画像といったオブジェクトです。ですからActiveXコントロールやフォームコントロールもShapesコレクションに含まれることになります。

4行目のSheets(“Sheet1”).Shapes(c.Name).DrawingObject.Enabled = Falseの意味は、Sheets(“Sheet1”)は「Sheet1」シートを指します。

Shapes(c.Name)は指定したシート上にある全ての描画オブジェクトの中でコントロールの名前が「c.Name」になるものを指します。c.Nameは順次描画オブジェクトをcに格納していって、Nameプロパティからcに格納した描画オブジェクトの名前を取得できるようにしています。

DrawingObjectが、描画オブジェクトの名前が「c.Name」になる個別の具体的なオブジェクトを指します。最後のEnabled = Falseで今回の記事の目的であるフォームコントロールを無効にする、ということになります。

ポイントはShapes(c.Name).EnabledではなくShapes(c.Name).DrawingObject.Enabledという風に「DrawingObject」が途中に入ります。またDrawingObjectsではなく「DrawingObject」という風に最後に「s」は入らないので、見落としやすいので注意してください。

もうひとつポイントとしては、今回のFor eachステートメントを使うとシート上にある全ての描画オブジェクトを見ていくことになります。仮にその中に「吹き出し」といったコントロールとは別のオブジェクトなど、一部はエラーになってしまうものがあります。

自分が確認した範囲では、フォームコントロール、ActiveXコントロールのEnabledプロパティは全てFalseに設定できました。そのためそれらのコントロール以外のオブジェクトをシート上に設置している場合は、

• 「c.Name」の部分で最初からフォームコントロールに設定している文字列を指定する
• If文を使って指定のオブジェクト名のフォームコントロールだけEnabledプロパティをFalseにできるようにする

といった風にして指定のフォームコントロールにだけ設定がされるようにした方が良いと思います。

ここまで書いたような4行目のコードにすることで、それぞれ特定のフォームコントロールを指せるようになります。5行目のNext cで変数cに格納する描画オブジェクト(今回の記事ではフォームコントロール)を変えていきそれを繰り返す、という流れになります。

ここまでのコードの注意点としては、ここまでではシート上に設置した各フォームコントロールはEnabled=Falseという状態を保持しただけで、「無効」にはなっていません。

これは以下のMicrosoftのヘルプにもあるように「仕様」でどうしても実質的に無効にはできないようです。

• Excel 2010 でフォーム コントロールなどの Enabled プロパティを False に設定しても、登録されたマクロが実行される

題名が「Excel2010」となっているので、バージョンがExcel2007の時はここまでに記述したコードでもフォームコントロールを無効にできたと思います。

しかし自分のパソコンで使っているExcel2013でのここまでのコードでは、確かにEnabled=Falseにしてもフォームコントロールのボタンは押せたままになっています。試しにボタンを押したらメッセージボックスが表示されるようにしたのですが、どうやってもメッセージボックスが表示されました。

最初の内は自分の入力したコードが良くなかったのではないかと思い色々と試行錯誤してみましたが上手くいかず、やはりコードの問題ではないようです。

何か良い方法がないか悩んでいる時に見つけたのが先程引用したMicrosoftのページです。ページの題名に「Excel 2010 でフォーム コントロールなどの Enabled プロパティを False に設定しても、登録されたマクロが実行される」とあるので、一見するともうどうしようもないかのように思えます。

しかしそのページから次のような方法を思いつきました。

If文からEnabled=Falseの状態を利用してフォームコントロールの有効無効を切り替える

先述の以下のMicrosoftのページには次のように書かれています。

• Excel 2010 でフォーム コントロールなどの Enabled プロパティを False に設定しても、登録されたマクロが実行される

この現象を回避するには、以下の例のように、処理の冒頭にマクロが実行されないよう制御文を追加してください。

Sub ボタン1_Click()

‘ —– 回避策 : ここから —–

If Sheets(“Sheet1”).Buttons(“ボタン 1”).Enabled = False Then Exit Sub

‘ —– 回避策 : ここまで —–

MsgBox “ボタン 1 がクリックされました”

End Sub

このコードを見てピンときました。「なるほど」と。ここまでで、Excel2010以降では、フォームコントロールをEnabled=Falseにしても無効にはできず、「その状態を保持するだけ」と書きました。

この部分がポイントで、If文からEnabled=Falseの状態を利用してフォームコントロールの有効無効を切り替える、というコードになります。

しかし引用したコードが適用できるのは「ActiveXコントロール」の場合です。というのも1行目に「Sub ボタン1_Click()」とあり、ボタンのクリックイベントになっているからです。

実験してみたのですが、フォームコントロールの場合はこのクリックイベントのプロシージャを作ることが出来ませんでした。例えば、指定のシートにフォームコントロールがあってもコードウィンドウのオブジェクトボックスにはそのオブジェクト名は表示されません。

また、フォームコントロールのボタンのオブジェクト名が「作成」だったとして、手入力で「Sub 作成_Click()」のイベントプロシージャを作って中に何かコードを入力しても動作しません。

「マクロの登録」で登録されるコードが優先されてしまうのですが、逆にこれを利用します。今回の記事の最初の方で書いた2つ目のコードを以下に再掲します。

Sub テスト1()
Dim c
For Each c In Sheets("Sheet1").Shapes
   If Sheets("Sheet1").Shapes(c.Name).DrawingObject.Enabled = False Then
      MsgBox "aaa"
      Exit Sub
   End If
   MsgBox "bbb"
Next c
End Sub

上記のコードをボタンの「マクロの登録」で登録します。まず今回の記事で記述したフォームコントロールのEnabledプロパティをFalseにする1つ目のコードを実行します。

その後、上記のコードを登録したボタンを押すと、そのボタンのEnabledプロパティがFalseの場合は「aaa」の文字列が書かれたメッセージボックスが表示されます。逆にEnabled=Trueだと「bbb」の文字列が書かれたメッセージボックスが表示されます。

「aaa」と「bbb」の文字列が表示されるメッセージボックスは、Enabledプロティの状態によってちゃんと条件分岐できるか確認しているだけで、通常は「aaa」の行は削除すれば、Enabled=Falseであれば何も起きないようにできます。

「bbb」の行の部分に、そのフォームコントロールに動作させたいコードを入力すれば、フォームコントロールが有効の場合(Enabled=Trueの状態)は目的の動作をさせることができます。そして無効の場合は何も起きない、という機能にできます。

複数のフォームコントロールに適用したいのであれば、別にモジュールかプロシージャを作って、そちらにさせたい動作のコードを入力してcallステートメントとプロシージャ名を「bbb」の行に置き換えるという方法もあります。

また、ひとつひとつフォームコントロールにマクロの登録をするのが面倒くさければFor eachステートメントを利用してSheets(“Sheet1”).Shapes(c.Name).DrawingObject.OnAction = “○○○”という風にコード上で設定するという方法もあります。(“○○○”の部分に目的のプロシージャ名を入れる)

使う人が使いやすい形で変えていただければと思います。

今回のコードとフォームコントロールの補足

ここまでで必要な一連の処理は書けました。ここでは、ここまで記述したこと以外で次の3点について書いていきます。

1. フォームコントロールを無効にしても文字色までは変わらない
2. Shapesを使うとActiveXコントロールも「無効」にできる
3. フォームコントロールで「無効」にできるのは標準で搭載されている8種類の全てのフォームコントロール

1について説明してきます。

今回のコードではフォームコントロールを形として「無効」にすることはできますが、同時に文字色までは変えることはできません。ActiveXコントロールだとEnabled=Falseにすると文字色もグレーになって無効になったことがわかりやすいのですが、フォームコントロールだと自動では文字色は変更されません。

そのため、必要であればフォームコントロールの文字色を変更するコードが別途必要になります。そのコードについては以下の記事を参照していただければと思います。

• ExcelVBAでシートにあるフォームコントロールのボタンの文字色を変更する方法

2について説明してきます。

Shapesを使うとActiveXコントロールも「無効」にできるのですが、ここまで説明してきたようにShapesはそのブック上、シート上にある全ての描画オブジェクトを指します。

そのためFor eachを使う際、If文などで何も制限をかけないと、フォームコントロールもActiveXコントロールも一部のその他のオブジェクトも無効にしてしまいます。

前回以下のようなActiveXコントロールだけを無効にする過去記事を書いたのは、フォームコントロールやActiveXコントロールを分けて使いたい人がいる場合を考慮してのことです。

• ExcelVBAのOLEObjectsでシート上のActiveXコントロールを使用不可にする方法

ですから特定のフォームコントロールやActiveXコントロール、もしくは特定のコントロールだけを無効にしたい、有効無効を切り替えたい場合は、適宜使い分けるか、If文などでオブジェクト名を指定していただければと思います。

3のフォームコントロールで「無効」にできるのは標準で搭載されている9種類の全てのフォームコントロールについてですが、一覧にすると以下のような形になります。

試しに上記のフォームコントロール全てでEnabledプロパティをFalseにしてみましたが、Enabled=Falseという状態は保持するのですが、無効にはなりませんでした。

もしかしたら自分の手違いなどもあるかもしれませんが、Excel2010以降で全てのフォームコントロールを状況によって無効にしたい場合は、今回の処理が必要になってくるかと思います。

まとめ

今回はActiveXコントロールの時と違ってかなりてこずりましたし、途中で挫折しそうになりました。というのも以下のコードをF8キーで1行ずつデバッグする時に黄色のラインがエラーなく通過したので、これでいけると思ったからです。

Sheets(“Sheet1”).Shapes(c.Name).DrawingObject.Enabled = False

デバッグが無事終了後、うきうきしながらボタンを押しました。すると設定されているマクロのメッセージボックスが表示されてしまい愕然としました。「えっ？何で？」という風に。

上記のコードを試行錯誤しながら見つけるだけでも大変だったのに、解決策を見つけるためにまた調べなければならないとなると気持ちが落ち込むばかりでした。

なんとか偶然も重なって解決策が見つかったわけですが、今回の件はExcelのバージョンの仕様上どうしようもない、という点はありました。けれどもそれを補うための「代替案」というものを見つけられたのは色々と勉強になりました。

今回の件から、今後はそのアプリケーションの仕様上どうしてもできない機能があっても「何か代替案はないか」ということを考えながらコードを見ていこうと思います。

あわせて読みたい

• ExcelVBAで外部アプリ操作やクラスモジュールのコードが見つからない問題の考察
• ExcelVBAのWebBrowserとsetZoomでPDFを拡大縮小させる方法
• ExcelVBAのWebBrowserとsetZoomScrollでPDFの表示位置を調整する方法
• ExcelVBAを使いこなすにはもう一段上のレベルの知識が必要なようです
• 新時代のテクノロジーの利益を享受できるかは本人の勉強と選択次第