CommandButtonのCaptionを変化させたい

質問

VBAを始めたばかりの初心者です。
sheet1にコントロール（CommandButton）を10個ほど配置しました。各CommandButtonのCaptionにsheet2～11のセルA1の値を表示させたいと思っています（このA1の値は変化します）。
Private Sub Workbook_Open()
Dim Mrang1 As String
Mrang1 = Worksheet(2).Range("a1").Value
CommandBottan1.Caption = Mrang
End Sub
上記のようなものを考えてみましたが、ダメでした。
根本的に何か間違っているのでしょうか。
よろしくお願いします。

回答No.2

imogasi · ベストアンサー

質問の真意というか、困っている状況が良く伝わりませんが推定して
私も試行してみました。上手く行かない原因は「イベント」の捕らえ方
選び方にあると思います。イベントとは「クリックしたら」とか「ワークブックを開いたら」とか言うプログラムが実行される「きっかけ」のことですよね。
以下回答を済ませてから良く見ると質問はコマンドボタン
を貼りつけるものでした。済みません置き換えてください。変わりはないと思います。
新規WorkbookのSheet１を開き、メニューバーの右余白部分で
右クリック－コントロールツールボックス－ラベル（Aの表示のあるもの）とクリックし、ラベルをSheet1上にドラッグして貼りつけました。
それを３回繰り返し３つラベルを貼りつけました。その後
ツール－マクロ－ＶｉｓｕａｌＢａｓｉｃＥｄｉｔｏｒ－コードをクリックします。
イベントとしてWorksheetのActiｖateを選びました。
コーディングの本体は貴殿と同じです。
Private Sub Worksheet_Activate()
Label1.Caption = Worksheets("sheet1").Range("a1")
Label2.Caption = Worksheets("sheet2").Range("a1")
Label3.Caption = Worksheets("sheet3").Range("a1")
End Sub
そしてメニューの「実行」をクリックします。その後Worksheetに切り替えてSheet2のA1に文字列値（ロとする）を入れ、Sheet3のA1に文字列値（ハとする）を入れSheet1に戻ります。最後にSheet１のA1に文字列（イ）値を入れると、Sheet1の３つのラベルの文字がそれぞれイ、ロ、ハになります。
Sheeｔ２を開いてA1の値を変えたり、Sheeｔ3を開いてA1の値を変えたりして、Sheet1に戻るとSheet1の３つラベルは変えたように変わります。
ただこのWorksheetはSheet1を指すようで、Shｅet2のA1に「ロ」を
入れた時やShｅet3のA1に「ハ」を入れた時に、Shｅet１のラベル
が変わるのではないようです。
しかし貴殿の意図のケースではSheet1にもどった時に変わっていれ
ば差し支えないのではないでしょうか。

お礼

丁寧な回答で、もやもやとしていたイベントの概念もよーくわかりました。
大きなヒントになったのは、Worksheets("sheet1")の"sheet1"の部分でした。
はずかしながら、Worksheets(1)とWorkshhts("sheet1")は全く同じであると思っておりまして、「ボタン」を配置したシートの指定がまちがっていたようです。
あとは回答いただいた通り作成してみたところ、うまくいきました。本当に有り難うございました。

回答No.3

kirara_pika

こんな感じはどうでしょう。
コントロールは配列で配置して、

Dim Mrang(10) as string
Dim i as integer

FOR i=0 to 10
Mrang(i)=Worksheets(i+2).Cells(1,1)
WorkSheets(1).CommandButton(i).Caption=Mrang(i)
NEXT i

～まず配列Mrang()にそれぞれの文字列を代入して、コマンドボタンのキャプションに表示させます。

お礼

そんな感じでとてもいいです。
10個のコマンドボタンを配置して…と質問に書いた後40個に増えました。なので、この配列の回答はぜひ使わせていただきたいと思います。有り難うございました。

回答No.1

tamagawa49

シート１に作ったボタンというのは「フォーム」ツールバーの「ボタン」でしょうか。もしそうならば、それはCommandButtonとは呼ばないようです。
マクロの記録では　Shapes("Button 1")となっていました。
そこで、普通に「フォーム」ツールバーの「ボタン」のテキストを表示変更するマクロを作ってみました。

Sub Auto_Open()
For a = 1 To 10
Worksheets(1).Shapes("Button " & a).Select
Selection.Characters.Text = Worksheets(a + 1).Range("A1")
Next
End Sub

ただ、ksjqaさんはWorkbook_OpenというイベントマクロやPrivate Sub～もご存知の方なので「フォーム」ツールバーの「ボタン」のことではないのかもしれませんが。

もし質問の意味が違ったのでしたら、また補足して下さい。

お礼

わかりやすい回答をありがとうございました。
「フォーム」ツールバーではなく、「コントロール」ツールバーのボタンのことだったのですが、tamagawa49さんの説明で、「フォーム」ツールバーの「ボタン」の扱いが理解できました。
皆さんの回答を試していてお礼が遅れた事をお詫びします。