ワークシート上に配置したコマンドボタンの操作

質問

OS：WinXP-Home
Office：Excel2000
を使用して、VBAのマクロを作成しています

ワークシートに複数のコマンドボタンを配置して
各ボタンのcaptionに複数のワークシート名をそのまま代入したいと思っているのですが
以下のソースでは実行できませんでした
記述先はコマンドボタンを貼り付けたシートです
試しに11枚のワークシートと10個のコマンドボタンで実行してみました
Private Sub Worksheet_Activate()
For i = 1 To 10
Controls("CommandButton" & i).Caption = Sheets(i + 1).Name
Next i

End Sub

以下のように一つずつ指定すればうまく行くのですが

CommandButton1.Caption = Sheets(2).Name
CommandButton2.Caption = Sheets(3).Name
・
・

ユーザフォーム上のコントロールと違って
「Controls（）」での操作は出来ないのでしょうか？
自力で検索したところ

OLEObjects("CommandButton" & i).Caption = Sheets(i + 1).Name
Controls.Item("CommandButton" & i).Caption = Sheets(i + 1).Name

の方法も試してみましたがやはりうまく行きませんでした

よろしくお願いします

回答No.4

Wendy02

こんにちは。

#3の訂正があります。

×　If MsgBox(cn.Object.Caption & "のシートを加えますか？", vbQuestion + vbOKOnly) = vbOK Then

　　　　↓

If MsgBox(cn.Object.Caption & "のシートを加えますか？", vbQuestion + vbOKCancel) = vbOK Then

このようにしたほうが良いです。

回答No.3

Wendy02

こんにちは。

#2の回答者です。

今回のObject の問題は、やっぱり、私も教わって覚えたものです。書籍では出てきませんね。この手のものには、さっぱり分からないものが多いです。

>シートをユーザが（順番を）動かしたり、削除してしまう可能性があったので動的にボタンのcaptionや機能（押されると対応して表示するシート）を設定したいと思っていました

そういう問題は、その都度変えるのではなくて、もう少し、一捻りしたほうがよいかもしれません。ちょっと難しいですね。

名前付け自体は、最初のイベントではない普通のマクロで良いです。

その次なのですが、シートの順番を動かしたときに、少なくとも、

Worksheets(i).Name

と、インデックスで取ってはおかしくなってしまいます。順番自体は、一旦、名前が付けたら、それはそのままです。Worksheets("Sheet1")は、どこにあっても、"Sheet1" です。しかし、今度は、名前を替えられたらどうするか、ということも入ってきますが、それは、今は、考えないことにします。

シート自体は、削除されないように、ツールの中の保護のブック－シート構成で、プロテクトする方法もありますが、Sheet1 にこんなマクロを考えてみました。

ただし、シート名に関しては、順序自体は、最初のシートの次に入れることにしました。少し、研究してみてください。

'-------------------------------------------------
Private Sub Worksheet_Activate()
　　Dim cn As Object
　　Dim dummy As Variant
　　For Each cn In ActiveSheet.OLEObjects
　　　　If TypeOf cn.Object Is MSForms.CommandButton Then
　　　　　　On Error Resume Next
　　　　　　dummy = Worksheets(cn.Object.Caption).Name
　　　　　　If Err.Number > 0 Then
　　　　　　　　MsgBox cn.Object.Caption & "のシートがありません。"
　　　　　　　　If MsgBox(cn.Object.Caption & "のシートを加えますか？", vbQuestion + vbOKOnly) = vbOK Then
　　　　　　　　　Worksheets.Add After:=Me
　　　　　　　　　ActiveSheet.Name = cn.Object.Caption
　　　　　　　　　Me.Activate
　　　　　　　　End If
　　　　　　End If
　　　　　　On Error GoTo 0
　　　　End If
　　Next cn
End Sub

'-----------------------------------

回答No.2

Wendy02

こんにちは。

>ユーザフォーム上のコントロールと違って「Controls（）」での操作は出来ないのでしょうか？

今、調べてみたら、私も気がつかなかったけれども、種類としては、同じなのですね。しかし、親オブジェクトのプロパティとしての扱い方かが違うわけです。ワークシート側には、直接のプロパティはなく、単に、大きなCollection の一つの中にあるようです。そこが、フォーム（ツール）との違いのようです。

上記で述べたように、コントロールツールのCaptionの変更でしたら、マクロでは、オブジェクトの取得の仕方の違いがあります。これは、グラフなどでも同様です。Selection で直接なら、Caption ですが、OLEObjects は、Object プロパティのCaptionです。

　If TypeOf cn.Object Is MSForms.CommandButton Then
なお、これは、コマンドボタン以外にもある場合に、このように仕分けします。

それと、Worksheet_Activate イベントでつけるのは、私は、あまり関心しないですね。

コントロールツールは、なんといっても、名前の通りブックとの関係は、OLEという間柄(Object Linking and Embedding)で、シートとは結びついていますが、直接の配下（member)とはいえませんから、あまり頻繁に操作はしないほうがよいのではないかと思います。あえて、すべきではないと言うつもりはないのですが。

その点で、私は、フォーム（ツール）側のほうが、マクロでは気安く扱えますね。

'--------------------------------------

Sub NamingObjects()
Dim cn As Object
Dim n As Integer
Dim i As Integer
　n = Sheets.Count
　i = 2
　For Each cn In ActiveSheet.OLEObjects
　If TypeOf cn.Object Is MSForms.CommandButton Then
　　 If n >= i Then
　　　'ワークシートの名前でしたら、Sheet ではありません
　　　cn.Object.Caption = Worksheets(i).Name
　　　i = i + 1
　　 End If
　End If
Next cn
End Sub

お礼

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

1の方への補足へも書きましたが独学でやっているせいで中途半端な知識になっているようです
OLEObjectsの操作が分かってないようでした
記述していただいたソースで思ったとおりの動作が確認できました
感謝します

＞それと、Worksheet_Activate イベントでつけるのは、私は、あまり関心しないですね。

＞コントロールツールは、なんといっても、名前の通りブックとの関係は、
＞OLEという間柄(Object Linking and Embedding)で、シートとは結びついていますが、
＞直接の配下（member)とはいえませんから、あまり頻繁に操作はしないほうがよいのではないかと思います。
＞あえて、すべきではないと言うつもりはないのですが。

上記の件ですが、製作しているものが件の例で言いますと

12枚のシートで1枚目が質問したコマンドボタンを配置したメニュー画面
2～11枚目がデータシート
（実際には2～4枚目がデータ入力、5～6枚目が集計表示、7～11枚目が分析などの用途で使われます）

12枚目が、非表示にするマスタデータ
の構成となっています

私はこれを作るだけで実際に使用するのは別のユーザです

2～11への各シートの入力フォームはそれぞれで完成していまして
メニュー画面のボタンを押すと該当のユーザフォームを開きつつ、そのシートをアクティブにする予定です
メニュー画面自体をおっしゃるとおりユーザフォームで作ってもよいのですが
ユーザの使い勝手（ワークシートの移動及び表示や説明文の表示、ユーザフォームの大きさ）
などから、シート上にメニューを配置した方が良いと思ったためですが
シートをユーザが（順番を）動かしたり、削除してしまう可能性があったので
動的にボタンのcaptionや機能（押されると対応して表示するシート）を設定したいと思っていました

ボタンの機能はcaptionから引き継げそうですので
Worksheet_Activate　イベントではなく
Workbook_Open　イベントで一度きりの設定にする事も検討してみます
アドバイスありがとうございました

回答No.1

zap35

＞ユーザフォーム上のコントロールと違って「Controls（）」での操作は出来ないのでしょうか？
を見る限り、ある程度VBAに慣れていらっしゃるのだと推察します。

ならばなぜボタンが「フォーム」のコマンドボタンか、「コントロールツールボックス」のコマンドボタンかを明示しないのでしょう？
以下は「フォーム」のコマンドボタンと想像して回答します

Private Sub Worksheet_Activate()
Dim idx As Integer
Dim act
　　Set act = Selection
　　For idx = 1 To Worksheets.Count
　　　　ActiveSheet.Shapes(idx).Select
　　　　Selection.Caption = Sheets(idx).Name
　　Next
　　act.Select
End Sub

補足

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

＞＞ユーザフォーム上のコントロールと違って「Controls（）」での操作は出来ないのでしょうか？
＞を見る限り、ある程度VBAに慣れていらっしゃるのだと推察します。

＞ならばなぜボタンが「フォーム」のコマンドボタンか、「コントロールツールボックス」のコマンドボタンかを明示しないのでしょう？

VBAは独学で書籍なども買わずにネットだけで検索しつつやっているので
部分的には慣れていると思うのですが、基本的な理解が足りてないのだと思います
質問したかったのはワークシート上に「コントロールツールボックス」で貼り付けた
コマンドボタンのcaptionを操作したかったのですが、説明不足で申し訳ないです