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

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

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

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

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にすると文字色もグレーになって無効になったことがわかりやすいのですが、フォームコントロールだと自動では文字色は変更されません。

 

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

 

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

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

 

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

 

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

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

 

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

フォームコントロールの種類コントロール名
ボタンButton
コンボボックスDrop Down
チェックボックスCheck Box
リストボックスList Box
スピンボタンSpinner
オプションボタンOption Button
フレームGroup Box
ラベルLabel
スクロールバーScroll Bar

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

 

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

まとめ

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

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

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

 

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

 

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

 

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

あわせて読みたい

こんな記事も読まれています

コメント