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

Question

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

Wendy02 · Accepted Answer

こんにちは。なんとなく違和感のあるコードかなって思います。確かに、マクロのコードとしては十分なのですが。 >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 たぶん、こんな感じでよいかなって思います。

Wendy02 · Answer

こんばんは。

>配列の件なのですが、運用時のコードでは、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

前回も書いたように、配列変数のまま、やり取りするよりは安全なのです。例えば、配列の格納が失敗していたら、それを、また、作るようなことをします。

bonaron · Answer

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

Wendy02 · Answer

こんにちは。

>呼び出した方法についての情報を返すとあるのですが、この場合、何が返ってきているのでしょうか？

返ってきているのは、その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

に入れたほうがよいと思います。右クリックイベントに入れると、右クリックメニューを占有してしまいます。

deecyan · Answer

でけへんと思う　（ツールバーにもマクロ指定はできるけど
引数がないのしか選択できないでしょ？）

.OnAction = "ツールバーマクロ"　を
.OnAction = "ツールバーマクロ"&i として
Sub ツールバーマクロ1()
Sub ツールバーマクロ2()
Sub ツールバーマクロ3()
Sub ツールバーマクロ4() の様なハンドリングをします。

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

こんにちは。

こんばんは。

Private Sub ツールバーマクロ()

こんにちは。

でけへんと思う （ツールバーにもマクロ指定はできるけど

似たような質問が見つかりました

関連するカテゴリからQ&Aを探す

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング

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

でけへんと思う　（ツールバーにもマクロ指定はできるけど