【Excel VBA】マクロ名一覧の表示について

質問

Excel2003を使用しています。

ある1つのBookにマクロが25個あります。
マクロを実行する際、目的のマクロが見つけやすいよう、マクロ名にある程度の規則性を持たせているものの、もう少し使い勝手がよくならないかと考え中です。
マクロ名は、下記のようになっています。
(1)．あ01_○×、あ02_×○、あ03_○○…
(2)．い01_●●、い02_●×…
(3)．う01_××、う02_×□、う03_□×、う04_□□…
(4)．え01_●□、え02_□●、…

例えば、こんなことが可能かどうかもわからないのですが…。
この25個のマクロは、マクロの内容から大きく分けて、上記(1)～(4)の4種類で、シート上にボタンを4つ配置(仮に、ボタン1、ボタン2、ボタン3、ボタン4)して、ボタン1をクリックしたら、ボタン1に割り振られているマクロ名(例えば、(1)のマクロ名)のみ表示して、その中から目的のマクロを選択すると、そのマクロが実行されるようにすることは、可能でしょうか？

イメージとしては、ボタンを配置するということに限らず、入力規則のリストから選択するような感じで、どうにかできないかなと思っていますが、何か良い方法はないでしょうか？

わかりづらい文章で申し訳ありませんが、よろしくお願いします。

回答No.3

misatoanna · ベストアンサー

こんにちは、＃１です。

> Bookの中にユーザーフォームも数個あり、そのコードも表示されています
> コンボボックスを4つ作成して、モジュール毎にリスト化

手直ししてみましたので、前回の回答は破棄してください。

1.新しいツールバーを作成して、名前を「MyMacro」にします。
2.Sheet1のモジュールに、以下を記述します。

Sub CrtMacroBox()　　'コンボボックスを作成・更新。
　Dim C, myCtl, i, POL, j
　C = 0
　For Each myCtl In CommandBars("MyMacro").Controls
　　If myCtl.Caption <> "更新" Then myCtl.Delete
　Next
　For i = 1 To Application.VBE.ActiveVBProject.VBComponents.Count
　　If Application.VBE.ActiveVBProject.VBComponents(i).Type = 1 Then
　　　C = C + 1
　　　With CommandBars("MyMacro").Controls.Add(Type:=msoControlComboBox)
　　　　.Caption = "MacroBox" & C
　　　　.DropDownLines = 10
　　　　.OnAction = "Sheet1.MacroExec"
　　　　.AddItem "Select Macro"
　　　End With
　　　With Application.VBE.ActiveVBProject.VBComponents(i)
　　　　POL = ""
　　　　For j = 1 To .CodeModule.CountOfLines
　　　　　If .CodeModule.ProcOfLine(j, 0) <> POL Then
　　　　　　CommandBars("MyMacro").Controls("MacroBox" & C).AddItem .CodeModule.ProcOfLine(j, 0)
　　　　　　POL = .CodeModule.ProcOfLine(j, 0)
　　　　　End If
　　　　Next
　　　End With
　　　CommandBars("MyMacro").Controls("MacroBox" & C).ListIndex = 1
　　End If
　Next
End Sub

Sub MacroExec()　　'コンボボックスから選択したマクロを実行。
　Dim CName
　CName = CommandBars("MyMacro").Controls.Item(Application.Caller(1)).Caption
　Select Case CommandBars("MyMacro").Controls(CName).Text
　　Case Is = "Select Macro"
　　Case Else
　　　Run CommandBars("MyMacro").Controls(CName).Text
　End Select
　CommandBars("MyMacro").Controls(CName).ListIndex = 1
End Sub

3.ツールバー（MyMacro）に、ユーザー設定"コマンド"タブの「分類」から
　"ユーザー設定ボタン"を登録し、次のとおり設定します。
　　・名前を「更新」にしてテキストのみ表示にします。
　　・マクロの登録で "AddMacroBox" を指定します。

[更新]アイコンをクリックすると、コンボボックスを作成・更新します。

お礼

misatoanna さん、こんにちは。
再度の回答ありがとうございます。

教えていただいた方法で試してみたところ、希望通りのことができました。
[更新]アイコンで、コンボボックスを作成・更新できるところなどは、当初、考えにはなかったことで、嬉しい限りです！

ツールバーにコンボボックスを作成したことで、見た目にもスッキリしていて、とても満足しています。
ありがとうございました。

回答No.5

misatoanna

各モジュールの先頭に、次のような空白のみのダミーマクロを記述しておきます。

モジュール１
　Sub ★GroupＡ()

　End Sub

モジュール２
　Sub ★GroupＢ()

　End Sub

以下モジュール３、４も同様に

Sub CrtMacroBox()　の中央程の次の１行を削除します。
　.AddItem "Select Macro"

Sub MacroExec()　の中味を次の５行だけにします。
　Dim CName
　CName = CommandBars("MyMacro").Controls _
　　　.Item(Application.Caller(1)).Caption
　Run CommandBars("MyMacro").Controls(CName).Text
　CommandBars("MyMacro").Controls(CName).ListIndex = 1

前回は、すべてのコンボボックスに同じ文字列（"Select"）が表示されましたが、
上のように変更すると、"★GroupＡ"、"★GroupＢ"、のように表示されますので、
識別しやすいかと思います。

お礼

またまた、回答をいただき、ありがとうございます。

実は、今回アドバイスいただいたように、識別しやすいよう

>Sub CrtMacroBox()　の中央程の
>　.AddItem "Select Macro"

を削除して、それぞれのモジュールの先頭のマクロ名が表示されるようにしていました。
幸いマクロ名にグループを識別できるような文字も含んでいましたので…。

>各モジュールの先頭に、次のような空白のみのダミーマクロを記述しておきます。

各モジュールの先頭にダミーのマクロを記述することで、そのマクロ名が表示されれば、さらに識別しやすくなるので、早速、手を加えたいと思います。

おかげさまで、当初考えていたものより、ずっと良いものが完成しました。
何度もご丁寧にありがとうございました♪

回答No.4

misatoanna

＃３の最後のほうに書き間違いがありました。

マクロの登録で "AddMacroBox" を指定します。
　は、
マクロの登録で "CrtMacroBox" を指定します。
　でした。

回答No.2

xls88

Application.Runで、セルデータ(マクロ名)を拾って実行できます。

例えば
Application.Run "'D:\ExcelBook\教えてgoo.xls'!" & ActiveCell.Value
といった感じです。

フルパス名前後の記号を忘れると実行できません。
同一ブックの場合はフルパス名を省略できます。

お礼

回答ありがとうございます。

今回、Applivation.Run を初めて知りましたので、練習がてら、例に挙げていただいた方法で試してみたところ、うまくいきました。

おかげさまで、またひとつ勉強になりました。
ありがとうございました！

回答No.1

misatoanna

ユーザーフォームの設計が面倒に思えまして、
ツールバーに、アクティブブックの「標準モジュール」内のマクロをリスト化した
コンボボックスを作成するマクロと、コンボボックスで選択したマクロを実行する
マクロの試作です。

ヒントになるでしょうか。

リストにこのマクロ自体が取り込まれないように、Sheet1のシートモジュールに
コピペして、AddMacroBox、ReNewData の順に１度ずつ実行します。
※Sheet1という名前のシートが無い場合は作成し、マクロ実行後に非表示にして
　ください。

Sub AddMacroBox()　'ツールバーにコンボボックスを作成します。
　With CommandBars("Standard").Controls.Add(Type:=msoControlComboBox)
　　.Caption = "MacroBox"
　　.DropDownLines = 30
　　.OnAction = "Sheet1.MacroExec"
End With
End Sub
'
Sub ReNewData()　'コンボボックスのリストのデータを更新します。
　Dim i, j, POL, C
　On Error GoTo pfm
　For i = 1 To 100
　　CommandBars("Standard").Controls("MacroBox").RemoveItem 1
　Next
pfm: On Error GoTo 0
　With CommandBars("Standard").Controls("MacroBox")
　　.AddItem "Select Macro"
　　.AddItem "ReNew"
　　For i = 3 To Application.VBE.ActiveVBProject.VBComponents.Count
　　　With Application.VBE.ActiveVBProject.VBComponents(i)
　　　　POL = ""
　　　　For j = 1 To .CodeModule.CountOfLines
　　　　　If .CodeModule.ProcOfLine(j, 0) <> POL Then
　　　　　　C = C + 1
　　　　　　CommandBars("Standard").Controls("MacroBox") _
　　　　　　　　　　.AddItem .CodeModule.ProcOfLine(j, 0)
　　　　　　POL = .CodeModule.ProcOfLine(j, 0)
　　　　　End If
　　　　Next
　　　End With
　　Next
　End With
　CommandBars("Standard").Controls("MacroBox").ListIndex = 1
End Sub
'
Sub MacroExec()　'コンボボックスのリストから選択したマクロ実行します。
　Select Case CommandBars("Standard").Controls("MacroBox").Text
　　Case Is = "Select Macro"
　　Case Is = "ReNew"
　　　Application.Run "Sheet1.ReNewData"
　　Case Else
　　　Application.Run CommandBars("Standard").Controls("MacroBox").Text
　End Select
　CommandBars("Standard").Controls("MacroBox").ListIndex = 1
End Sub

リスト内の並び順は考慮していません。
順番を指定したい場合は、いったんマクロ名をセルに書き出して並べ替えたものを
コンボボックスに取り入れる方法もありますね。

お礼

回答ありがとうございます。

ツールバー上にコンボボックスを作成するなんて、思いもつきませんでしたが、この方法にすれば、随分使い勝手がよくなりそうです♪
それで、いくつか教えていただきたいことがあるのですが、よろしいでしょうか？

>リストにこのマクロ自体が取り込まれないように、Sheet1のシートモジュールにコピペして

回答いただいたように、マクロ実行後、シートを非表示にしましたが、リストにこのマクロが取り込まれているようです。

>アクティブブックの「標準モジュール」内のマクロをリスト化

とありますが、このBookの中には、ユーザーフォームも数個ありまして、そのコードもリストの中に表示されていますが、このコードも「標準モジュール」の中のひとつということになるのでしょうか？

質問文にも書いておりますが、マクロの内容から大きく分けて4種類あり、標準モジュールも4つに分けているのですが、コンボボックスを4つ作成して、モジュール毎にリスト化できれば、さらに見やすくなると思うのですが、可能でしょうか？