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

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

 

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

 

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

 

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

 

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

 

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

 

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

 

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

 

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

 

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

  1. ExcelVBAのShapesとIf文でシートのフォームコントロールを無効にするコード
  2. 1つ目の「フォームコントロール」プロシージャの説明

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のヘルプにもあるように「仕様」でどうしても実質的に無効にはできないようです。

 

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

 

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

 

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

 

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

 

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

次へ
12

コメント