ワークシート上のコントロールの操作方法

Question

既に作成してあるワークシート上のコマンドボタンをVBAで移動させたいのですが


http://www.happy2-island.com/excelsmile/smile03/capter00716.shtml

上記URLを参考にして以下のコードを作成してみました。


Sub sAdd_OLEObject()

     Dim objOLE As OLEObject
     
     '画面更新を一時的にOFF
     Application.ScreenUpdating = False
         'コントロールを追加する
        Set objOLE = Worksheets("data").OLEObjects("Forms.CommandButton.1")
         objOLE.Object.Left = 10           '横位置を設定
        objOLE.Object.Top = 20  '縦位置を設定
        

     '画面更新をONに戻す
    Application.ScreenUpdating = True

 End Sub


しかし、
WorksheetクラスのOLEObjectsのプロパティを取得できません。
というエラーが出て実行することができません。

そもそも、作成していあるコントロールのコマンドボタンの名称が
"Forms.CommandButton.1"
で正しいのかどうか分からないのですが
どうすれば名称を確認することができますか？
右クリックメニューなどでは確認することができないのですが
どうすれば良いでしょうか？

WindFaller · Accepted Answer

こんにちは。

>.Width = Range(Cells(1, 1), Cells(1, 256)).Width
>
>でうまくいくと思うのですが
>おおよそ指定した幅になるのですが
>なぜか5%程度大きめのサイズになってしまいます。
>これはなぜでしょうか？

この事象を思い出すまで、しばらく時間がかかりました。かなり昔のデータですが、現在のExcelにも当てはまるはずです。これは、Excel独特の問題だと思います。

シート全体に対して
　1．フォントサイズが、8～14以外が含まれている。
　2．セルを一杯に文字や数式の解を入れて、同じ行の右方向のセルに、データが入っている。
　3． 縦の太い罫線がある。

この場合、右端がずれる原因です。

こちらでは、よくみるとピタリと入っていましたが、実験的に何も余計なデータなどは入っていないからです。ちなみに、これほど大きなボタンを作ったことがありません。代替で、オートシェイプでもボタンと同じ機能をしますので、試してみてください。もともと、このフォームボタンはShape の仲間です。機能的には、同じ結果になるはずですし、同じ現象が現れるかもしれません。

'Excel 2010 で作っていますので、2003以下では、一部コードを変えなくてはなりません。
Sub AlternativeWay()　'代替の方法
Dim shp As Object
Dim x1 As Double, x2 As Double
Dim y1 As Double, y2 As Double
Dim rw As Long
　rw = 256　'256 行目に(ひとつずらしました）
　With ActiveSheet
　 x1 = .Cells(rw, 1).Top
　 x2 = .Cells(rw, 1).Offset(1, 0).Top
　 y1 = 0
　 y2 = .Cells(1, rw +1 ).Left
　 Set shp = .Shapes.AddShape(msoShapeRectangle, x1, y1, x2 - x1, y2 - y1)
　　With shp
　　　　.Top = x1
　　　　.Left = y1
　　　　.Height = x2 - x1
　　　　.Width = y2 - y1
　　　　.Fill.Visible = msoTrue
　　　　.Fill.ForeColor.ObjectThemeColor = msoThemeColorBackground1
　　　　.Fill.ForeColor.TintAndShade = 0
　　　　.Fill.ForeColor.Brightness = -0.05
　　　　.Fill.Transparency = 0
　　　　.Fill.Solid
　　　　.Line.Visible = msoFalse
　　　　.OnAction = "Button_Click"　'登録マクロ(前回のサンプル・マクロ）
　　End With
　　.Cells(rw, 1).Select
　End With
End Sub
'//

>Buttons(1).Top
>というスタイルがver95の古い用法ということでしょうか？
そうです。こちらが古いです。呼び名は、フォームコントロールと言います。
Shape の仲間、言い換えれば、Officeの内部のオブジェクトです。
直感的に、こちらが良いと思われたなら、その勘は正しいと思います。(あくまでも個人的な意見ですが、私もそうしているからです）ですが、今は、こういうところは、ネット内で教わらないと、書籍等では情報は入りにくいのではないでしょうか。分からないことは、古い人に聞いたほうが早いようです。

>それとも
>OLEObjectの方が古いのでしょうか？
こちらのほうが新しいです。ActiveX コントロールといいます。こちらは、外部オブジェクトです。このカテゴリで、この件に関連した、VB6 Runtimeのお話がされています。

「MSCOMCTL.OCXのアップデートの方法 」(No.9108824) 2015/11/13

>どちらを使った方が良いでしょうか？
Microsoft は、OLEObject つまり、ActiveX コントロールに統一するはずだったのですが、両方使ってみれば分かりますが、フォームコントロールのほうが軽いのは、BUCHURUNさんもお気づきかと思います。

ActiveXコントロール(OLEObject)は、右クリック・左クリック、コントロールを押しながらとか、豊富な操作ができますが、あまり数多く作ると、シート自体が重くなる傾向にあります。どちらかという扱いにくい部分があります。数がは多くない時は、OLEObject でもよいと思います。

WindFaller · Answer

こんにちは。

#3ではなくて、#2なのですが……、

>With Worksheets("data").Buttons(1)
>.Top = Cells(257, 1).Top
>.Left = Cells(257, 1).Left
>End With
>
>でうまくいきました。

それは良かったでした。Excel 2007以降は、このスタイルのほうが一般的になってしまいましたね。
変だと思いませんか？
それは、
　Buttons(1)　
こういう書き方、ヘルプに載っていませんよね。これは、Excel Ver.5(95) のスタイルなのです。
Microsoft は、このスタイルをずっと辞めるつもりで、ヘルプからなくしたようです。

私の持っている Ver.5 のヘルプでざっと調べてみました。
Buttons, DropDowns, CheckBoxes, Labels, ListBoxes, OptionButtons

前置きが長過ぎますね。すみません。

>ボタンの幅もセル単位で指定したいのですが
>どのようにすれば良いでしょうか？

ついでに、高さも加えるとこのようになります。
これでいかがでしょうか。

With Worksheets("data").Buttons(1)
　.Top = Cells(257, 1).Top
　.Left = Cells(257, 1).Left
　.Width = Cells(257, 1).Width
　.Height = Cells(257, 1).Height
End With

別のサンプルを残しておきます。
参考になるか分かりませんが、実際は、私は、このようにしていました。

'標準モジュールを使います。

Sub AddFormButton()
Dim x1 As Double, x2 As Double
Dim y1 As Double, y2 As Double
With Range("A1")
　x1 = .Left
　y1 = .Top
　x2 = .Offset(, 1).Left
　y2 = .Offset(1).Top
End With
With ActiveSheet.Buttons.Add(x1, y1, x2 - x1, y2 - y1)
　.Caption = "ボタン" & .Index
　.OnAction = "Button_Click"　'標準モジュールにマクロを置きます。
End With
End Sub

Sub Button_Click()
　MsgBox Date & " " & Time
End Sub

x-1919 · Answer

"Forms.CommandButton.1" これはワークシートの上に置かれているコマンドボタンの名前ではなく種類を表すものです。
従いまして "Forms.CommandButton.1" ではワークシート上の特定のコマンドボタンを捕まえることはできません。

各コントロールの名前は以下の手順で確認できます。
1. Excel の [開発] タブ を開く。（既定では表示されていないので、表示のさせ方は検索してください）
2. [コントロール] グループにある [デザインモード] を押してデザインモードにする。（三角定規のボタン）
3. コントロールを右クリックして [プロパティ] をクリックする。

VBE の画面にも登場するプロパティ ダイアログが表示されます。
グリッドの先頭行にそのオブジェクトの名前が表示されています。
プロパティ ダイアログでは自分なりの命名規則に合わせてコントロールの名前を変更することも可能です。

Sub hoge4()
　Dim sht As Worksheet
　Set sht = Worksheets(1)
　
　Dim objOle As OLEObject
　Set objOle = sht.OLEObjects("CommandButton1")
　
　objOle.Top = 10
　objOle.Left = 10
　
　objOle.Object.Caption = "ここをクリック"
　objOle.Object.ForeColor = RGB(255, 0, 0)
　objOle.Object.AutoSize = True
End Sub

WindFaller · Answer

こんにちは。

>どうすれば名称を確認することができますか？

デザインモードにして、コマンドボタンを選択すると、
数式バーのところに、
=EMBED("Forms.CommandButton.1","")
などと出てくるはずです。

もし、出てこないのでしたら、フォームコントロールのコマンドボタンです。

そうした場合は、Nameボックスに、「ボタン＊」と出るはずです。
オブジェクトは、このようなスタイルになるはずです。
Worksheets("data").Buttons(1)

Buttons(*)は、インデックスですから、順番に1,2,3となります。

nicotinism · Answer

あなたが参考にされたリンク先の使用例２を試してみれば解決。

ワークシート上のコントロールの操作方法

こんにちは。

こんにちは。

"Forms.CommandButton.1" これはワークシートの上に置かれているコマンドボタンの名前ではなく種類を表すものです。

こんにちは。

あなたが参考にされたリンク先の使用例２を試してみれば解決。

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

このQ&Aを見た人はこんなQ&Aも見ています

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

このQ&Aを見た人がよく見るQ&A

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

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