エクセル2003 自作ツールバー(コマンドバー)の選択戻り値取得方法

質問

ツールバーのショートカットを自作して
入力を楽にしようと考えております。
ツールバーには、直接入力するリストを表示させ
そのリストの中から選んで、セルに記入させると
言うマクロを作ろうとしているのですが、
選択された、リストの戻り値を取れないため、
苦戦しております。
戻り値の取得方法とかあるのでしょうか？
宜しくお願いいたします。
コードを以下に記します。

Option Explicit
Option Base 1
Dim myCB As CommandBar
Dim myCBCtrl As CommandBarButton
Dim myData() As String
Dim n As Integer, i As Integer
----------------------------------------------------------------
Sub AddCmdBarBtn()

Call 配列
On Error Resume Next
CommandBars("MyMacro2").Delete

Set myCB = Application.CommandBars.Add(Name:="MyMacro2", _
Position:=msoBarPopup, Temporary:=True)

For i = 1 To 5
Set myCBCtrl = myCB.Controls.Add(Type:=msoControlButton)

With myCBCtrl
.Caption = myData(i)
.OnAction = "ツールバーマクロ"
End With
Next i
myCB.ShowPopup
End Sub
----------------------------------------------------------------
Private Sub ツールバーマクロ()
ActiveCell.Value = myCB.Controls.Item.Caption<<<此処を上手く処理できません(泣)
End Sub
----------------------------------------------------------------
Private Sub 配列()
ReDim myData(5)
For n = 1 To 5
myData(n) = "名前" & n
Next n
End Sub
----------------------------------------------------------------
このマクロを右クリックイベントで使って行きたいと考えている
のですが。

回答No.2

Wendy02 · ベストアンサー

こんにちは。

なんとなく違和感のあるコードかなって思います。
確かに、マクロのコードとしては十分なのですが。

>Private Sub ツールバーマクロ()
>ActiveCell.Value = myCB.Controls.Item.Caption<<<此処を上手く処理できません(泣)
>End Sub

　これは、Application.Caller を使えばよいです。

Private Sub ツールバーマクロ()
Dim i As Integer
i = Application.Caller(1)
　ActiveCell.Value = myCB.Controls(i).Caption
End Sub

たぶん、こんな感じでよいかなって思います。

お礼

ありがとうございます。
このような事が出来るんですね。
感動です。
違和感が在るコードと言うご指摘なのですが、
やっぱ、使い方が変ですかね～。
確かに、コマンドバーオブジェクトの中にプロシャージを
振り分けるようになっているのに、それを一つの
プロシャージに戻ってくるようにしてるんで、
本来の使い方から外れてるのかな、とか思ったりします。
こんな風にできるなんて。ありがとうございます。
で、お礼と共に、ご回答いただいた
i = Application.Caller(1)
のコードなのですが、
もし、お手数でなければ、補足していただけないでしょうか？
ヘルプを見ますと、呼び出した方法についての情報を返す
とあるのですが、この場合、何が返ってきているのでしょうか？
お手数でなければ、ご指導いただけないでしょうか。

回答No.5

Wendy02

こんばんは。

>配列の件なのですが、運用時のコードでは、2次元配列で格納してあり、このコード以外でも利用するので、

いろいろ、してみるのが良いかと思います。
失敗の数が、上達をさせるような気がします。実際の私のコードは、２次元配列を、グローバル変数にするようなことはしませんが、配列の「元」になるものを、以下のようなグローバル定数にして対応することがあります。

例：

Public Const MOTO As String = "A,B,C,D,E,F,G"
Private myAr As Variant

Sub CheckArray()
Dim i As Integer
If IsArray(myAr) = False Then　'配列になっているかチェック
　myAr = Split(MOTO, ",")
End If

i = InputBox("Please Enter between 1-7")
If i > 0 And i < 8 Then
　MsgBox myAr(i - 1)
Else
　MsgBox "invalid number!"
End If

End Sub

前回も書いたように、配列変数のまま、やり取りするよりは安全なのです。例えば、配列の格納が失敗していたら、それを、また、作るようなことをします。

お礼

お返事ありがとうございます。
こちらの返事が遅くなってすみません。
こちらのコードも参考にして、今後も勉強させていただきます。
沢山のアドバイスやご回答を頂き、誠にありがとうございます。

回答No.4

bonaron

Private Sub ツールバーマクロ()
　　ActiveCell.Value = CommandBars.ActionControl.Caption
End Sub
だけでよろしいかと思います。

お礼

お返事ありがとうございます。
このような形で出来るのですね。
またまた、一つ勉強になりました。
誠にありがとうございます。

回答No.3

Wendy02

こんにちは。

>呼び出した方法についての情報を返すとあるのですが、この場合、何が返ってきているのでしょうか？

返ってきているのは、そのControlsインデックスですが、他は、あまり良く意味が分からないです。4105 というのは、ボタンのイベントの種類かなって思いますが。

自分が投稿した時点では、自分の中で整理させていませんでしたが、後に、いろいろ考えてみました。

>振り分けるようになっているのに、それを一つの
>プロシャージに戻ってくるようにしてるんで、
>本来の使い方から外れてるのかな、とか思ったりします。

いいえ、マクロ全体としては、問題はないと思います。
もうマクロとしても、出来上がっている内容ですので、もう少し、私のつたない経験的なことなので、話半分でも構いません。

一般的に、#1さんのご指摘が正答ですが、この場合のマクロは、一段高いレベルの方法です。実際、クラス設定をする場合に、そのように一元管理しますから、そういう使い方はあります。その場合は、オブジェクトを配列ではなくて、Collection化してしまうのです。しかし、今回の場合は、幸運にもインデックスが取れるので、あえてクラス設定の必要性がありません。

例：自作メニューのクラス設定のサンプル（私の作－今回には、参考にはなりません。）
http://oshiete1.goo.ne.jp/kotaeru.php3?qid=2899966
#12,#13 （カプセル化させています）

擬似コントロール配列の作成
http://www.moug.net/skillup/opm/opm08-05.htm
（これ自体は単にテクニックだけです。実用にはいまひとつです。）

私が思うのは、VBとか、専用のプログラミング言語と違って、マクロは、割り込みが入りやすい欠陥を持っているので、グローバル変数（ここでは、モジュールレベルですが）は、サブルーチンの中で働いていくのならよいのですが、OnAction として、独立して使うとなると、ちょっと弱いのです。実際に使って、他のマクロも使ったりすると、エラーが出やすくなるのです。

他のマクロでエラーが発生すると、それだけで、他のグローバル変数にも影響受けます。

ActiveCell.Value = myCB.Controls(i).Caption

この[myCB]のオブジェクトが弱いように思うのです。それと、配列による設定は、あまり意味がありません。以下のようにして、出来てしまいますね。

Sub AddCmdBarBtnR()
Dim myCB As CommandBar
Dim myCBCtrl As CommandBarButton
Dim i As Integer　'Variant でも可－そういう場合は、以下のCStr(i)で、文字列化は要らない

　　On Error Resume Next
　　CommandBars("MyMacro2").Delete
　　On Error GoTo 0　　'ここでエラートラップは締めます。

　　Set myCB = Application.CommandBars.Add _
　　(Name:="MyMacro2", _
　　 Position:=msoBarPopup, _
　　 Temporary:=True)

　　For i = 1 To 5
　　　　Set myCBCtrl = myCB.Controls.Add(Type:=msoControlButton)
　　　　With myCBCtrl
　　　　　　.Caption = "名前" & CStr(i)
　　　　　　.OnAction = "ツールバーマクロ"
　　　　End With
　　Next i
　　myCB.ShowPopup
End Sub

Private Sub ツールバーマクロ()
'このOnActionマクロは、モジュール上では独立しています。
Dim i As Integer
　i = Application.Caller(1)
　ActiveCell.Value = CommandBars("MyMacro2").Controls(i).Caption
End Sub

それと、
>このマクロを右クリックイベントで使って行きたいと考えているのですが。

Worksheet_BeforeRightClick
ということだと思いますが、できれば、

CommandBars("CELL").Controls.Add

に入れたほうがよいと思います。右クリックイベントに入れると、右クリックメニューを占有してしまいます。

お礼

ご解説頂き、誠にありがとうございます。
まだまだ、解説いただいた言葉の中にも理解しきれないところが
ありますが、この内容、キーワードを基に、いろいろと調べたり
試したりして勉強していきたいと思います。
配列の件なのですが、運用時のコードでは、2次元配列で格納して
あり、このコード以外でも利用するので、質問時には別プロシャージ
で質問させていただきました。
このように、ご丁寧に補足していただき誠にありがとうございます。

回答No.1

deecyan

でけへんと思う　（ツールバーにもマクロ指定はできるけど
引数がないのしか選択できないでしょ？）

.OnAction = "ツールバーマクロ"　を
.OnAction = "ツールバーマクロ"&i として
Sub ツールバーマクロ1()
Sub ツールバーマクロ2()
Sub ツールバーマクロ3()
Sub ツールバーマクロ4() の様なハンドリングをします。

お礼

お返事ありがとうございます。
通常、やっぱりそう言う使い方ですよね。
No2さんのご回答でなんとかなりそうです。
ありがとうございます。