こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

締切り済みの質問

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

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

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

よろしくお願いします

投稿日時 - 2008-03-25 12:19:05

QNo.3893047

すぐに回答ほしいです

このQ&Aは役に立ちましたか?

1人が「このQ&Aが役に立った」と投票しています

回答(4)

ANo.4

こんにちは。

#3の訂正があります。

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

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

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

投稿日時 - 2008-03-27 13:51:50

ANo.3

こんにちは。

#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

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

投稿日時 - 2008-03-25 15:24:22

ANo.2

こんにちは。

>ユーザフォーム上のコントロールと違って「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

投稿日時 - 2008-03-25 13:40:03

お礼

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

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 イベントで一度きりの設定にする事も検討してみます
アドバイスありがとうございました

投稿日時 - 2008-03-25 14:33:03

ANo.1

>ユーザフォーム上のコントロールと違って「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

投稿日時 - 2008-03-25 12:51:24

補足

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

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

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


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

投稿日時 - 2008-03-25 14:29:39

あなたにオススメの質問