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種類のコントロールは、今回記述したコードから使用不可にできるということです。
ExcelVBAのOLEObjectsでシート上のActiveXコントロールを使用可能にするコード
シート上に設置したActiveXコントロールを処理の都合から使用不可にするのはいいですが、元に戻さないといけません。その場合は簡単で、以下のように4行目をEnabled = Trueにして実行すれば、シート上に設置された全てのActiveXコントロールが使えるようになります。
Sub ActiveXコントロール() Dim c For Each c In Sheets("Sheet1").OLEObjects Sheets("Sheet1").OLEObjects(c.Name).Object.Enabled = True Next c End Sub
指定したActiveXコントロールだけ使用不可にしたい場合
この場合も特に難しくはありません。ここまで記述したコードの中に以下のようにIF文を入れると指定のActiveXコントロールだけ使用不可にできます。
Sub ActiveXコントロール() Dim c For Each c In Sheets("Sheet1").OLEObjects If c.Name Like "CommandButton*" Then Sheets("Sheet1").OLEObjects(c.Name).Object.Enabled = False End If Next c End Sub
上記のコードはFor Eachステートメントで順番に見ていくコントロールの中でオブジェクト名の中に「CommandButton」の文字列が含まれているものだけを使用不可にするコードです。
例えばシート上にコマンドボタンが10個、ラベルが10個あったら、コマンドボタン10個だけを使用不可にできます。
もう一度コントロールを使えるようにするには、上記のコードのSheets(“Sheet1”).OLEObjects(c.Name).Object.Enabled = Falseの中のFalseをTrueにすれば元に戻ります。
他にも特定のActiveXコントロール使用不可にしたいのであれば、そのActiveXコントロールのオブジェクト名を「=」で直接指定するか、「Like」と「*」を使って「特定の文字列が含まれる」形で指定することで特定のActiveXコントロールを使用不可にできます。
まとめ
以下の過去記事でも書いたのですが、自分が欲しいExcelVBAのコードというものは意外にも「見つからない」ということです。
ExcelVBAというのは一般の人の手に渡ってから20年以上経っています。そのため、必要とされるコードというのはほとんどが網羅されてインターネットや書籍などで誰でも簡単に見ることができる、と以前は思っていました。
しかし今回のコードの件の様に、一般的には「できない」と言われているけれども、色々と試行錯誤してみることで自分の意図するコードが見つかる、という場合もあります。
今回の件というのは非常に珍しいと思われるかもしれませんが、過去記事でもいくつか書いたのですが意外と結構あるものです。
ですから、インターネット上では「できない」と言われていたり、自分の欲しいコードがなかなか見つからなかった場合でも「本当に解決策はないのか」と疑って試行錯誤してみることで「実はこんなコードがあった」なんてことがあるかもしれません。
コメント
ありがとうございます!!
何日も何日も探して試行錯誤していました。
ActiveXラベルのオプションを一気に変える方法を。
本当に助かりました。
ありがとうございます!