Excel2002・マクロ ツールバー、メニューバーの追加・変更方法

質問

Excel2002において、自分で作成したマクロを実行させるツールバー、メニューバーを追加したいのですが、
どのようにすれば、追加や変更ができるのでしょうか？

Excel Ver7.0の時に作成したマクロをExcel 2002で読み込んだのですが、ツールバー・メニューバーはちゃんと表示されているのに、VisualBasic Editorを開いてみると標準モジュール、クラスモジュールのどこにも、このコード記述が見あたりません。

Ver7.0の時は、ツールバー、メニューバーを表示させるコードを直接モジュールシートに記述しており、参照できるようになっていました。
Excel 2002では、コード記述ではなく、どこか別の所、プロパティウィンドウなどで設定するのですか？

どうすれば、ツールバー・メニューバーの追加や変更が可能になるのでしょうか？

どなたか、ご回答をよろしくお願いします。

５年のブランクがあり、新しいバージョンの仕様が
変わっているので、戸惑っています。
どなたか助けてください！
よろしくお願いします。

回答No.8

sakenomo · ベストアンサー

＃７です、なかなか難しそうですね。

そういう場合、ＶＢＥのオブジェクトブラウザで、Auto_Openを検索するとどうなるのでしょうか。

僕は開くブックによらずメニューバーに、コントロールの"操作支援ツール"が作られてしまうのかと思っていましたが、

>　そのブックをまた開くと、自分で作成したツールバーがヘルプの右横に表示されてしまいます。

ということで、問題はひとつのブックに限定されている様なので、どうしても見つからなかったら、この際新しいブックに必要なコードをコピーしてそちらをご使用になることをお勧めします。
その場合、コントロールを作るコードは、Auto_Openよりも、ThisWorkbook の Workbook_Open イベントを利用したほうがいいように思います。

なんだか投げやりな回答ですみません。m(__)m

>　あと、「バーとボタンの混同」というのは、どの部分でしょうか？

ご質問文等を拝見していてちょっと分かりにくかったのは、タイプが　msoControlPopup　のコマンドバーコントロールを、随所でメニューバーと書かれているところです。＃１のコードで作られるのはコントロールで、コマンドバーではないですよね。

もちろんcateyeさんは、違いをよく分かっていらっしゃると思います。文章にするのは難しいですよね、僕もコントロールをボタンと書いてしまっているし・・・。

お礼

お礼が遅くなってしまってすみません。
あれから、いろいろ調べてますが、まだ納得
できていません・・・f^^;
とりあえず、この件はこれで締切らせていただきますね。
根気よく、おつきあい下さり、本当にありがとうございました。

回答No.7

sakenomo

＃６です。

（Ｂ）での”起動”とは、エクセルを新規に起動した場合のことです。手動でツールバーを作ると、その後起動したりファイルを開けば作ったバーが表示されませんか。マクロでも同じで、一度作ったバーはボタンに登録されたマクロのリンク先情報を含めて何処かに保存されるようです。

（Ａ）でのファイル中のコードが消えてしまった、ということだと僕には何も分かりません。

>　重くならないようにするためには、アドインで制御した方がいいのでしょうか。

重さについては分かりませんが、ユーザ側から見ればアドインなら一度登録するだけですし、ウィンドウ中に余分なブック（マクロのファイル）が開くことも無いので使いやすいように思います。

ところで、＃１のコードはエクセルのメニューバーに"操作支援ツール"という項目を追加しますよね。
バーとボタンの混同があるのかもしれませんが、

>　私がマクロの中で作成したマクロのメニューバーは「ユーザー設定」の中にはありませんでした。

消したいのが上記の"操作支援ツール"ならば、このボタンを「ユーザー設定」ダイアログの中にドラッグすれば削除できます。

中途半端な回答で申し訳ありません。

補足

丁寧にありがとうございました。
少し前進しましたが、あともう一歩です・・・。
お二人のアドバイスのおかげで少しずつ進歩していますが、肝心の独自メニューバーがどうしても消えてくれません(>_<)
旧バージョンのメニューバー制御コードが表示されないのも不思議なのですが・・・
消すためには、やはり自分で作成したメニューバー制御コード（VisualBasic)を表示させて、それを削除するか編集するなりしないといけない気がするのですが・・。

＞”起動”とは、エクセルを新規に起動した場合のことです。
＞手動でツールバーを作ると、その後起動したりファイルを開けば作ったバーが表示されませんか。
＞マクロでも同じで、一度作ったバーはボタンに登録されたマクロのリンク先情報を含めて何処かに保存されるようです。

これは、理解できます。
一度ツールバー（ここでは「支援ツール」という名前とします)を作成すると、Excel全体を終了させて、別のファイルを開いても、そのツールバー「支援ツール」が表示されます。
なので、ver7.0で作成した際は、＃１で書いたように、マクロのコード中で、支援ツールを使うブックを開いた時のみ「支援ツール」のメニューを表示させ、そのブックを閉じる際、ツールバーを初期状態に戻すようにしています。（「支援ツール」のメニューを削除する）

このコードが、Excel 2002では、Editorを開いても表示されないにもかかわらず、メニューとして表示されているので困っています。どうしても消せません。
やはり、コードから編集できないと消せないのでしょうか。

＞消したいのが上記の"操作支援ツール"ならば、このボタンを「ユーザー設定」ダイアログの中にドラッグすれば削除できます。

試してみましたが、そのブックを開いている時に
「ユーザー設定」ダイアログの中にドラッグすると
自分の作成したメニューがそっくりそのまま、ユーザー設定に移りました。
これなら、マクロをひとつひとつ登録しなおす手間が省けますね。
それに一度、ユーザー設定に登録すると、その設定は
ブックを終了した後も記憶されるようなので、
表示・非表示を切り替えるだけですむようですね。

ただ、最初に自分で作成したコード（＃１のコード）によるメニューバーがどうしても消えないのです。
ユーザー設定にドラッグした時点では、このメニューバーを削除することができました。

しかし、いったんそのブックを閉じるorExcel 2002を終了して、そのブックをまた開くと、自分で作成したツールバーがヘルプの右横に表示されてしまいます。
「ユーザー設定」がオンになっていると、２つのメニューが表示されてしまいます・・・。
「ユーザー設定」をオフにしても、自分で作成したメニューバーが表示されています。
元々ツールバーのユーザー設定ではなく、VisualBasicのコードでメニューを表示させるように制御していますので、何度起動してもVisualBasicの方からコードを読み込んでメニューが強制的に表示されるようになっています。
なので、このコードを編集したいのですが、そのコードがVBの編集画面のどこにも見あたらないので困っています。
もちろん、他のブックのマクロを参照している、というようなことはありません。
ブック本体にしかマクロを作成していません。

あと、「バーとボタンの混同」というのは、どの部分でしょうか？
ボタンとバーのちがいは私なりに理解しているつもりですが・・・
画像がなく言葉で説明するのはとても難しいですねf^^;

ツールバー、メニューバーというのは、画面の上部にあるテキストのメニューやアイコンのことですよね？
ユーザー設定では、ボタン表示（フェイスマーク）もあるようですが、テキスト表示もできます。
テキスト表示モードにすれば、メニューバーになりますが・・・。

うまく文章にまとめるのができず、失礼しますが、イメージが分かって頂けましたでしょうか・・・。
もし、他に何か考えられることがありましたら、よろしくお願いしますm(__)m

回答No.6

sakenomo

＃２です。またまた自信なしですが、ひょとしたらこういう事でしょうか。

（Ａ）Ver7.0のファイルをエクセル２００２で開き（ツールバーが追加された）、これを上書き保存した。

（Ｂ）その後エクセルを起動すると、（Ａ）で追加されたバーが表示されるが、バーを作るコードはどこにも見当たらない。

これでしたら、普通のことだと思うのですが・・。バー上のコマンドボタンをクリックするとマクロが動くのなら、そのコードが書かれたファイルが自動的に開いているはずです。

（Ａ）で開いたファイルからもコードが消えてしまった、ということでしたらすみません。

あと、アドインを配布する場合のコードですが、ひとつのメニューバーに複数のファイルのマクロを登録したい、ということでしょうか。

例として、各アドインが共通で使うバーの名前をmybarとします。

WorkbookAddinInstallイベントで、配布先のエクセル上にすでに自分の他のアドインが組み込まれているかどうか、mybarがあるかどうかを調べ、あるならボタンだけを追加し、無いならmybarを作成する、といった方法ではいかがでしょうか。

Workbook_AddinUninstallでは、解除されるアドインのボタンだけを削除し、解除されるのが自分が配布した物の最後のひとつの場合にはバーも削除、というようにします。

お礼

コメントありがとうございました。
「Ver7.0のファイルをエクセル２００２で開き（ツールバーが追加された）、これを上書き保存したあと、エクセルを起動すると、追加されたバーが表示されるが、バーを作るコードはどこにも見当たらない。」
のは当然、というのは何故でしょうか？

確かにExcel 2002で開いた時に、そのまま上書き保存した記憶があります。
旧バージョンのコードは全てそのまま読み込んでコードも表示してくれると思っていましたので、バーを表示させるコードだけが表示されなくなる、という現象がどうしても理解できないのです・・・。
「コードが書かれたファイルが自動的に開いているはず」というのは私もそう思います。ちゃんとバーが表示されてバーからのマクロ実行ができますから。
ただ、バー表示と解除を制御するコードがVisual Basic Editorでどこにも表示されないのが不思議なのです・・・。
他の方が教えて下さったツールバーの「ユーザー設定」を見てみましたが、私がマクロの中で作成したマクロのメニューバーは「ユーザー設定」の中にはありませんでした。
「ユーザー設定」機能を使う場合、新規に登録しなおす必要があるようですし、それ以前に「ユーザー設定」を
使うか否かにかかわらず、以前作成したメニューバーが
表示されたままなので、これを編集or削除できる状態にしないと、次の作業に進めません。
（従来通りマクロでメニューバー表示制御するか、アドインにするか、ユーザー設定ツールバーにするか・・・を検討して、改良したいのです）

アドインに関しては、WorkbookAddinInstallイベントはまだ使ったことがありませんでした。挑戦してみます。
今までは、マクロを使いたいファイルを開く時に、バーを自動的に追加し、そのワークブックを閉じる時に、追加したバーを削除するというコードを使用していました。
この方法でも特に不便は感じていませんでしたが、ワークブックの中にマクロを含むので重くなるおそれがありますよね。
重くならないようにするためには、アドインで制御した方がいいのでしょうか。

回答No.5

notnot

入れ違いでしたね。

確かに大量にあるマクロを一気に登録するのは手間ですね。私の場合はマクロを作るたびに汎用なら登録してたんで手間はその都度だけだったんですけど。

添付したファイルを開けばそのツールバーが表示されるはずです。
他人に配るのは考えたこと無かったですが、添付した配布ファイルとpersonal.xlsを同時に開いて、personal.xlsに添付しなおすことで、その人のpersonal.xlsに登録できると思いますけど。

あるいは、ツールバーを添付しただけの空ファイルを、「ウィンドウ→表示しない」にしておいて、各自のoffice\xlstart に入れる手もあるかな。これでOKなら各自の手間は楽ですね。
ちょっと、自分で試してまで書く気力は無いので、ご確認ください。

回答No.4

notnot

＃１、３です。
元々ある、ファイルや編集メニューのように２段以上階層化されたプルダウンメニューを作って、それぞれにマクロの実行を割り当てたいということでいいでしょうか？

できますよ。

(0) まず、ユーザ設定を開いて
(1) 例えば分類「新しいメニュー」のコマンド「新しいメニュー」をヘルプの横にドラッグドロップします。するとヘルプの横に「新しいメニュー」が出来ます(名前はあとで右クリックで変更)。
(2) 次に分類「マクロ」の「ユーザー設定メニュー項目」をドラッグしてさっきの「新しいメニュー」の上に持っていくと灰色の正方形が表れますから、その上でドロップします。
(3)「新しいメニュー」を左クリックするとメニューの項目が追加されたことがわかります。それを右クリックすると名前を変えたりマクロをわりあてたりできます。
(4) もう一項目作りたければ再度「ユーザー設定メニュー項目」をドラッグしてさっきの項目の上か下にドロップします。
(5) サブ階層を作りたければ、また「新しいメニュー」をさっき作った「ユーザー設定項目」の上か下にまたドロップします。

それを続ければどうとでも作れると思います。
例では既存の標準ツールバーのヘルプの横でしたが、新規ツールバーをまず作ってそこにドロップするのももちろんOKです。

最後に、新たに作ったツールバーは、ユーザ設定のツールバータブの「添付」でpersonal.xls以外のファイルに添付できます。

お礼

入れ違いでコメント下さったようで、ありがとうございます。
おっしゃる通り、できました。
ありがとうございました。

で、「添付」というのがあるんですか。
これを使えばファイルごとに、使うマクロのメニューを使い分けることができるのでしょうか？

ファイルごとに、表示させるユーザー設定メニュー（マクロのメニュー）を記憶させることができると？
これができれば、便利ですよね。

詳しくは、下記のお礼のところにも書きましたが、
他の人のパソコンで、使ってもらいたいとき、
私の方で作成した「ユーザー設定メニュー」を配布
することもできるのですか？
使ってもらう人には、できるだけ負担をかけたくありません。

補足

根気よく教えて下さってありがとうございました。
おかげさまで、最初の質問の２つのうち、１つは分かりました。
コードを書かなくても登録できるのはスゴイ！
が・・・まだExcel 2002の機能を使いこなしきれてないので、希望の仕様にするには、どの機能を生かせばいいのか悩みますね。
１．こちらでユーザー設定を登録して、配布？
２．マクロでメニュー設定をコード記述しておき、マクロごとアドイン形式で保存。
どっちがいいのかしら？

あと、最初の質問の２つめはまだ未解決です。
ver7.0でコード記述で作成したツールバーとメニューが表示されてしまうんですね。
ユーザー設定を使うとすれば、マクロで設定したメニューはいらないわけで・・・。
そのコードを編集（削除）したいのに、肝心のコードがどこにも見あたらないのです。
ワークシートを表示させると、ツールバーが表示されてるしマクロも実行してくれるので、マクロが有効になってるとは思うんですが。
どこにいってしまったんでしょう？？？

回答No.3

notnot

＃１です。私の説明したのは、
＞[ツール]-[マクロ]-[マクロ]から実行したいマクロを選択して実行させたり、マクロオプションでショートカットキーを設定すること

ではありません。私の書いた方法で、

＞複数あるマクロを、機能別に項目分けして、マクロ専用のメニューバーを起動時に常にメニューバーとして表示させるようにしたいのです。

が出来るはずですが、実際にツールバー右クリックのユーザ設定を見られましたか？
ツールバータブで「新規ツールバー」を作って、そこにコマンドタブから「新しいメニュー」をいくつかドラッグしてそのメニューの中に、コマンドタブの「マクロ」の「メニュー項目」をドラッグすれば出来るはずです。

ただ、この新規ツールバーをExcel起動時共通ではなくファイルごと固有にしたいということなら、はずしてますけど。でも、「常に表示させる」でいいんですよね。

お礼

補足の補足です。

先ほどやっと何とか複数のマクロをひとつのツールバーにまとめる手順が分かりました。
「コマンド」→「マクロ」から「ユーザー設定項目」を
直接ツールバーにドラッグしてしまっていました。
この方法ですと、横にずらりと増える一方でした。
ユーザ設定でメニュー作成、マクロ登録できることは分かりました。こういう機能がついていたとは驚きました。すごいですね。
便乗質問で申し訳ありませんが、よろしければもう少し教えていただけませんか？

＞「この新規ツールバーをExcel起動時共通ではなくフ＞ァイルごと固有にしたいということなら、はずしてま＞す。でも、「常に表示させる」でいいんですよね。

今回のマクロは、一応そうなんですが。
ファイルによっては、いらない場合もあります。
一人で使う分には自分で登録したり、表示をはずしたり、と、この方法でもいいかもしれませんが・・・。
何しろマクロの数が多いので、一個一個登録していくのは自分一人だけで使うとしてもかなり面倒です。
コードでまとめて書いてしまった方が楽かも。

あと、他の人にも配布する場合も想定してます。
その場合、その人のパソコンでユーザー設定項目の設定をその都度しないといけませんか？

１．personal.xlsをスタートアップに入れる
２．ユーザー項目の設定、それも１０個以上のマクロを登録する（？）

という手間がかかるのは、分かりづらいし面倒だと思うのですね。

Excel ver7.0で作成した時は、Personal.xlsのマクロ本体の中に、メニュー表示の設定をコード記述してありましたので、
Personal.xlsを開けば自動的にマクロのメニューが
ツールバーに表示されるようになっていました。
こうすれば、Personal.xlsだけ配布して、スタートアップに入れてもらうだけですみますし。
ただ、この方法だと、ファイル別に表示、非表示の切り替えができません。
不要な時もツールバー表示されてしまう。
（VBAでこれも制御できるかもしれませんが）

なのでアドイン形式で配布して、必要なときにアドインを組み込むという方がいいかな、と思っています。
重くならないためにも・・・。

重さの観点からいえば、notnotさんのおっしゃるユーザー設定の方が重くならないでしょうか？

操作性、利便性、汎用性の観点からいえば、ユーザー設定は使いにくい気がします・・・。

補足

重ねてのご返答ありがとうございます。
質問がまた足りず、お手数をおかけして失礼します。

ツールバーのユーザー設定も試したのですが、
私の希望の仕様はできないみたいです。

「ユーザー設定項目」に登録できるのは
１つのマクロだけなのでしょうか？
ひとつのツールバーで、複数のメニューバーを
作成することはできないのでしょうか？
他のツールバーのように、サブメニューを登録できないのでしょうか？

「ユーザー設定項目」で試すと、マクロごとに横にずらりとツールバーが増えてしまいます。
上のバーが２段になってしまうのは避けたいので、
マクロの「ツールバー」は１つのみで、その中の
サブメニューで、複数のマクロを表示させるようにしたいのです。

回答No.2

sakenomo

まったく自信なしですが、ThisWorkbookの中は探されたのでしょうか。

補足

コメントありがとうございました。
もちろん、探しました。
最初は、Personal.xlsではなく、ThisWorkbookの中の
標準モジュールに入ってました。
Personal.xlsはコピーしただけです。
両方のブックの中の探したんですが、どっこにもないんです・・・。
Ver7.0でコード作成したはずだし、ワークブックを起動すると、ちゃんと指定した通り、マクロ用の独自ツールバーとサブメニューも表示されてるのに、
Visual Basic Editorの中を見ると、そのコードがどこにもない・・・。
不思議です。どこに消えた？！

Excel 2002で、最初から作り直すしかないのでしょうか？

回答No.1

notnot

Excel2000の場合ですがたぶん同じでしょう。
ツールバーを右クリックでユーザ設定を選び、
ツールタブでツールバーの新規追加。
コマンドタブから新規・既存のツールバーに機能のアイコンをドラッグできます。
マクロ実行なら「マクロ」の機能からメニュー項目かボタンをドラッグすればいい。そのボタン等にマクロを割り当てるのは右クリック。

補足

説明の仕方が悪かったようで失礼します。

私がやりたいことは、
[ツール]-[マクロ]-[マクロ]から実行したいマクロを選択して実行させたり、マクロオプションでショートカットキーを設定することではありません。

複数あるマクロを、機能別に項目分けして、マクロ専用のメニューバーを起動時に常にメニューバーとして表示させるようにしたいのです。
これには、
　・誰が見てもどんなマクロがあるのかひと目で分かるようする
　・機能別にマクロを整理して表示させ、操作性を高める（マクロの階層化）
この２つの目的があります。

　マクロ（メニューバー）以下はサブメニュー。
　　→書式設定-全シート一括設定,一括クリア
　　→表示設定-全シート一括設定,一括削除
　　→シート名一括変更
といったものです。
ワークシートタブのプロパティから「全てのシートを選択」してやればいいかもしれませんが、この方法ですと、全てのシートに対して実行させることができないものもあります。
　・全シートの同じ位置にウィンドウ枠固定をかける
　・全シートにおけるオートフィルターの一括設定、一括クリア
　・シート名の一括変更
など。
同じフォーマットのシートがたくさんあるファイルではこういう支援ツールが役に立ちます。

様々なシーンで操作性向上ツールを作成したものがたまってきたので、機能別に整理してメニューバーに独自に表示させて「支援ツール」としたいわけです。

この支援ツールは、Personal.xlsとしてExcel起動時には常に読み込むorアドインとして組み込めるように、独立させています。

Excel 2002でゼロから作成したファイルでは、
Visual Basic Editorを開いて、標準モジュールシートに直接、下記のような記述をしました。
Private Sub Auto_Open()
’ブック起動時
AddMenu 'マクロメニューバー追加
End Sub

Private Sub AddMenu()
Set NewMenu = CommandBars("Worksheet Menu Bar").Controls.Add(Type:=msoControlPopup)
NewMenu.Caption = "操作支援ツール(&M)"
Set MenuItem = NewMenu.Controls.Add(Type:=msoControlPopup)
MenuItem.Caption = "セル書式"
Set SubMenu1 = MenuItem.Controls.Add
SubMenu1.Caption = "一括設定"
SubMenu1.OnAction = "セル書式_一括設定"
Set SubMenu2 = MenuItem.Controls.Add
SubMenu2.Caption = "一括クリア"
SubMenu2.OnAction = "セル書式_一括クリア"

Set MenuItem = NewMenu.Controls.Add(Type:=msoControlPopup)
MenuItem.Caption = "入力規則"
Set SubMenu1 = MenuItem.Controls.Add
SubMenu1.Caption = "一括設定"
SubMenu1.OnAction = "入力規則_一括設定"
Set SubMenu2 = MenuItem.Controls.Add
SubMenu2.Caption = "一括クリア"
SubMenu2.OnAction = "入力規則_一括解除"
End Sub

Private Sub Auto_Close()
'ブック終了時
CommandBars("Worksheet Menu Bar").Reset 'メニューバーを標準に戻す
End Sub

というようなスクリプトを記述し、独自のツールバーとしてうまく表示できています。

ところが、Excel ver7.0で作成したマクロをExcel 2002で読み込んでみると、上記の記述がどこにも見あたらないのです。
開いているファイル（作業用ファイル）、Personal.xls（マクロ本体）の両方の標準モジュール、クラスモジュールを探しましたが見あたりません。
にも関わらず、独自の「マクロ」のメニューバーが「ヘルプ」の隣に表示されていますが、このスクリプトがどこへ行ってしまったのか分からないのです。

Excel 2002では、
・いちいちスクリプトを書かなくても、どこか別のところで設定できるようになっているのか？
・旧バージョンで作成したコードはどこに消えた？
ということが知りたいのです。

話が複雑で失礼しますが、よろしくお願いします。