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

ExcelVBAで何らかのシステムを作る場合、フォームを利用する人もいればシートを利用する人もいるかと思います。シートを利用する場合、何かの動作の起点として「コマンドボタン」などのボタンをシート上に設置して、そのボタンを押すことでコードの内容を実行させるでしょう。

 

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

 

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

 

そういった時に、ExcelVBAのコードでシート上のボタンを押せないように出来たら便利でしょう。しかし、「シート上に設置したボタンを使用不可にする」ためのコードをインターネット上で調べてみたのですがなかなか見つけられませんでした。

 

フォームを利用した場合のコントロールを使用不可にするコードはたくさん見かけるのですが、シート上に設置するコントロールを使用不可にするコードが見つけられないんです。

 

自分の探し方が悪かったのかもしれませんが、そもそも「シート上に設置したコントロール(ボタン等のこと)は使用不可にはできない」という言葉をよく見かけました。「それはExcelの仕様上できない」と。

 

そういった文章を自分の調べた範囲で何度も目にしたのですが、そうは言ってもその時は「何とかしてボタンをユーザーが使えないようなシステムを作らなければならない」という状況だったので色々と試行錯誤してみました。

 

その結果偶然にも「シート上に設置したボタンを使用不可にできるコード」を見つけたので、このことについて今回は整理していってみます。

 

もう一度確認しておきますが、フォームモジュールに設置するボタンではなく、シート上に設置するボタンを使用不可にする方法になります。

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

結論としては次のコードになります。Excel2013で動作を確認しています。ちなみに今回のコードはシート上に設置した「ActiveXコントロール」を使用不可にできるコードになります。

Sub ActiveXコントロール()
Dim c
For Each c In Sheets("Sheet1").OLEObjects
   Sheets("Sheet1").OLEObjects(c.Name).Object.Enabled = False
Next c
End Sub

順番にコードの解説をしていきます。

 

2行目で変数cを宣言。3行目のFor Eachステートメントで「Sheet1」シートにあるActiveXコントロール、埋め込みオブジェクト、リンクオブジェクトを順番に変数cに格納していきます。

 

ここがひとつポイントで3行目の「OLEObjects」でフォームコントロールではなく、ActiveXコントロールを変数cに格納できます。このOLEObjectsオブジェクトについてはMicrosoftの以下のヘルプのページには次のように書かれています。

各OLEObject オブジェクトは、ActiveXコントロール、リンクされたOLEオブジェクト、または埋め込まれているOLEオブジェクトを表します。

簡単に言うとOLEObjectsはActiveXコントロールが含まれるということです。これがOLEObjectsではなく上記のコードで「Shapes」を使うとエラーになるので注意してください。

 

VBAの書式風に言い換えると、OLEObjectsオブジェクトは、指定されたワークシートにあるすべてのOLEObjectオブジェクトのコレクションです。OLEObjectオブジェクトが個別のActiveX コントロール、またはワークシートにリンクあるいは埋め込まれているOLEオブジェクトになります。

 

ちょっと脱線しますが、自分は最初にこのキーワードを見たとき「Objectオブジェクトってオブジェクトという言葉が連続してない?間違っているのでは?」と思った時もありました。

 

しかしインターネット上の情報やいくつかの書籍を見ても同様に書かれているのでどうやら間違っていないようです。

 

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

 

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

 

 

Objectが、コントロールの名前が「c.Name」になる個別の具体的なオブジェクトを指します。最後のEnabled = Falseでコントロールを使用不可にする、ということになります。

 

ポイントはOLEObjects(c.Name).EnabledではなくOLEObjects(c.Name).Object.Enabledという風に「Object」が途中に入るので、見落としやすいので注意してください。

 

ここまで書いたような4行目のコードにすることで、正しく特定のActiveXコントロールを指せるようになっているんだと思います。5行目のNext cで変数cに格納するActiveXコントロールを変えていきそれを繰り返す、という流れになります。

 

シート上に設置した各ActiveXコントロールが「使用不可」になると、コマンドボタンなどは表示されている文字列がグレーになってクリックしても押せなくなります。

 

以上のコードからシート上に設置したボタンを使用不可にできることを確認したのですが、他にどの種類のボタンが使用不可にできるのでしょうか。自分が確認した範囲では、Excelに標準で搭載されている以下の11種類のボタンになります。

ActiveXコントロールの種類コントロール名
コマンドボタンCommandButton
コンボボックスComboBox
チェックボックスCheckBox
リストボックスListBox
テキストボックスTextBox
スクロールバーScrollBar
スピンボタンSpinButton
オプションボタンOptionButton
ラベルLabel
イメージImage
トグルボタンToggleButton

一覧にした上記以外でも追加できるコントロールがあるのですが、Excelの画面から「開発」→「挿入」で表示されるフォームコントロールとActiveXコントロールを選択できるドロップダウンリストがあります。

 

その中のActiveXコントロールグループの右下部分にある「コントロールの選択」から追加できるコントロールについては確認はしていません。とりあえず最初から選択できる上記の11種類のコントロールは、今回記述したコードから使用不可にできるということです。

コメント