Excel VBA ワークシートにコマンドボタンを挿入してマクロをつけるマクロ add button with macro on worksheet

手動というか基本

ワークシートにボタンを作成し、マクロを割り当てて実行できるようにしたい - helpの森

大体手動でやる人が多いのですが、
VBAでやることもできます。

自分が作ったものと記録で作って編集したものを比較

Sub SetButtonsOnActiveSheet()
'For Excel VBA
'Buid by Q11Q From Qiita
'https://detail.chiebukuro.yahoo.co.jp/qa/question_detail/q11106096531
'[マクロでボタンを挿入する - moug](https://www.moug.net/tech/exvba/0150104.html)
Dim wb As Workbook: Set wb = ThisWorkbook
Dim ws As Worksheet: Set ws = ActiveSheet
Dim shp As Excel.Shape
'Dim r As Range: Set r = ws.Range("G9:E9") '連続したセルでも結合したセルでもよい
Dim r As Range: Set r = ws.Range("H9")
Dim obj As Object
Dim shpRng As Excel.ShapeRange
With r
Set obj = ws.Buttons.Add(.Left, .Top, Application.CentimetersToPoints(3.65), Application.CentimetersToPoints(0.56)) 'プロパティシートはCm単位なので、Cm単位で指定している。しかし実際は誤差が出るため、出力されたボタンのサイズは同じにならない。
obj.Caption = "A1に日付を出力" 'Captionでも指定できる
obj.Characters.Text = "自作のボタン"
obj.OnAction = "sampleInsertToday" 'マクロの名前を入れる
'obj.Enable = True '使用可能にする しかしエラーになるため使えない
obj.PrintObject = False 'False 印刷しない
obj.Visible = True '可視
obj.Font.Name = "ＭＳ Ｐゴシック"
obj.Font.FontStyle = "標準"
obj.Font.Size = 11
obj.Font.Color = vbRed
obj.Font.Bold = False
obj.Font.Italic = False
obj.Font.Underline = xlUnderlineStyleNone
obj.Locked = True
obj.LockedText = True
End With
Set shp = ws.Shapes(ws.Shapes.Count)
shp.ZOrder msoBringToFront '最前面に移動
Set shpRng = ws.Shapes.Range(Array(shp.Name))
'Stop
End Sub
Sub InsertButtonOnSheet()
' For Excel VBA
' 自動記録を参考に作成したバージョン
'F3にボタンを設置する
'A1に今日の日付を入力するマクロが起動するようにする
Dim wb As Workbook: Set wb = ThisWorkbook
Dim ws As Worksheet: Set ws = ActiveSheet
Dim Rng As Range
Dim obj As Object
Dim shp As Excel.Shape
Dim shpRng As Excel.ShapeRange
Set Rng = ws.Range("F3")
Rng.Select
Set obj = ws.Buttons.Add(273.75, 40.5, 90.75, 15.75)
With obj
    obj.Characters.Text = "text editing"
    .OnAction = "sampleInsertToday"
    With obj.Characters(Start:=1, Length:=12).Font
        .Name = "ＭＳ Ｐゴシック"
        .FontStyle = "標準"
        .Size = 11
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = 1
    End With
End With
Set shp = ws.Shapes(ws.Shapes.Count)
shp.ZOrder msoBringToFront '最前面に移動
Set shpRng = ws.Shapes.Range(Array(shp.Name))
shpRng.Select
    With Selection.Font
        .Name = "ＭＳ Ｐゴシック"
        .FontStyle = "標準"
        .Size = 10
        .Strikethrough = True
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
'        .ThemeColor = xlThemeColorLight1
'        .TintAndShade = 0
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .ReadingOrder = xlContext
        .Orientation = xlHorizontal
        .AddIndent = False
    End With
    With obj
        .Placement = xlFreeFloating
        .PrintObject = True 'objectが印刷される
    End With
End Sub


Sub sampleInsertToday()
'A1に今日の日付を入力するマクロ
Dim wb As Workbook: Set wb = ThisWorkbook
Dim ws As Worksheet: Set ws = ActiveSheet
Dim Rng As Range: Set Rng = ws.Range("A1"): Rng.Activate
Rng.Value = Date
End Sub

buttonは複雑な把握のされ方をする

まずButtonという型はないようです。
自動記録させてみると、
object
shape
shperange
の少なくとも3つの把握のされ方をしています。

公式のボタンのメソッドとプロパティ

VBAには項目がない?

Docs Office365
Docs.NET.NET API ブラウザーMicrosoft.Office.Tools.Excel.ControlsButton Button Class

    Button
        プロパティ
            AltHTML
            Application
            Border
            Bottom
            BottomRightCell
            Creator
            Height
            Index
            Interior
            Left
            Locked
            OLEType
            OnAction
            Parent
            Placement
            PrintObject
            Right
            ShapeRange
            SourceName
            Top
            TopLeftCell
            Visible
            Width
            ZOrder

Method メソッド

            Activate
            BringToFront
            CopyPicture
            Delete
            Select
            SendToBack
            SetVisibleCore
            Update

Office 365 からVBAのリファレンスに飛び Buttonで検索してみても出てこない

定数 / 列挙体

使用する定数でわかりにくいものをメモ代わりに

Office.MsoZOrderCmd のメンバー

最前面に配置するときに使っています。
Const msoBringForward = 2
Const msoBringInFrontOfText = 4
Const msoBringToFront = 0
Const msoSendToBack = 1

horizontalAlignment VerticalAlignment

文字列の縦横の位置を決定する
horizontalalignment
Object.HorizontalAlignment 横位置を設定 設定/取得
Object.VerticalAlignment 縦位置を設定 設定/取得

ReadingOrder

Excel.Constants のメンバー
Const xlContext = -5002 (&HFFFFEC76)

文字列の方向 Orientation

Excel.XlOrientation のメンバー
Const xlHorizontal = -4128 (&HFFFFEFE0)
Const xlUpward = -4171 (&HFFFFEFB5)
Const xlVertical = -4166 (&HFFFFEFBA)
Const xlDownward = -4170 (&HFFFFEFB6)

Placement セルとともに移動するか

Excel.XlPlacement のメンバー
Const xlFreeFloating = 3
Const xlMove = 2
Const xlMoveAndSize = 1

参考

Excelでワークシート上のコマンドボタンをいつも浮動させるには？

Forms And ActiveX

Sub AddControlsAndAxCtrlsOnWorkSheet()
Dim wb As Workbook: Set wb = ThisWorkbook
Dim ws As Worksheet: Set ws = ActiveSheet
Dim objButton As Object, objDropDown As Object, objCheckBox As Object, objListBox As Object, objOptionBtn As Object, objGroupBoxes As Object, objScrBar As Object
Dim objoleCmdBtn As OLEObject, objOleObjs As OLEObjects, objOleLabel As OLEObject, objOleImg As OLEObject, objOLETgl As OLEObject, objOLEListBox As OLEObject, objOleTxtBox As OLEObject, objOLEChkBox As OLEObject, objSpnBtn As OLEObject, objOleCmbBox As OLEObject, var
Dim shp As Shape, shprng As ShapeRange
'Delete controls and activeX controls
If ws.Shapes.Count > 0 Then
For Each shp In ws.Shapes
shp.Delete
Next
End If
If ws.OLEObjects.Count > 0 Then
For Each var In ws.OLEObjects
var.Delete
Next
End If
With ws
Set objButton = .Buttons.Add(132.75, 12.75, 43.5, 23.25)
Set objDropDown = .DropDowns.Add(129, 51, 51.75, 39)
Set objCheckBox = .CheckBoxes.Add(135.75, 107.25, 51, 24.75)
Set objListBox = .ListBoxes.Add(243, 18, 53.25, 25.5)
Set objOptionBtn = .OptionButtons.Add(243, 57.75, 24, 27.75)
With objOptionBtn
.Value = xlOn
.LinkedCell = "$G$3"
.Display3DShading = True
End With
Range("F10").Select
Set objGroupBoxes = .GroupBoxes.Add(328.5, 68.25, 46.5, 39)
Set objScrBar = .ScrollBars.Add(406.5, 56.25, 119.25, 39.75)
' Add ActiveX Controls
'objectではなくoleobjectになる
'デザインモードでしか編集できない
'右クリックのメニューが違う
'オブジェクト自体をクリックするなどのイベントによってコードが起動できる(上のほうはマクロの登録、セルの値の変更しかできない)
Set objoleCmdBtn = _
ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", Link:=False _
, DisplayAsIcon:=False, Left:=492.75, Top:=18, Width:=48, Height:= _
9.75)
Set objOleCmbBox = _
ActiveSheet.OLEObjects.Add(ClassType:="Forms.ComboBox.1", Link:=False, _
DisplayAsIcon:=False, Left:=573, Top:=20.25, Width:=54, Height:=15.75 _
)
Set objOleLabel = ActiveSheet.OLEObjects.Add(ClassType:="Forms.Label.1", Link:=False, _
DisplayAsIcon:=False, Left:=398.25, Top:=114, Width:=31.5, Height:= _
24.75)
Set objSpnBtn = ActiveSheet.OLEObjects.Add(ClassType:="Forms.SpinButton.1", Link:=False, _
DisplayAsIcon:=False, Left:=462, Top:=123.75, Width:=54.75, Height:=21.75)
Set objOLETgl = ActiveSheet.OLEObjects.Add(ClassType:="Forms.ToggleButton.1", Link:=False, _
DisplayAsIcon:=False, Left:=540, Top:=122.25, Width:=56.25, Height:=70.5)
Set objOleImg = ActiveSheet.OLEObjects.Add(ClassType:="Forms.Image.1", Link:=False, _
DisplayAsIcon:=False, Left:=258, Top:=131.25, Width:=34.5, Height:=33)
Set objOLEListBox = ActiveSheet.OLEObjects.Add(ClassType:="Forms.ListBox.1", Link:=False, _
DisplayAsIcon:=False, Left:=331.5, Top:=141, Width:=67.5, Height:=30)
Set objOleTxtBox = ActiveSheet.OLEObjects.Add(ClassType:="Forms.TextBox.1", Link:=False, _
DisplayAsIcon:=False, Left:=408, Top:=208.5, Width:=79.5, Height:=39)
Set objOLEChkBox = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CheckBox.1", Link:=False, _
DisplayAsIcon:=False, Left:=429.75, Top:=162.75, Width:=41.25, Height _
:=22.5)
End With

End Sub

公式の説明はVBAのConceptにあった

Excel VBA ≫ 1．基礎編 - コントロールの設置 ≫ (10)-a．コントロールの種類
フォーム、フォーム コントロール、およびワークシート上の ActiveX コントロールの概要 - support.office.com
ActiveX コントロールを追加または登録する - support.office.com

ワークシートの上で直接すべての ActiveX コントロールを使用することができます。いくつか使用できますのみで Visual Basic for Applications (VBA) のユーザー。これらのコントロールを使用する場合は、他のワークシートに追加しようとした場合にオブジェクトを挿入することはできませんメッセージが表示されます。
ActiveX コントロールを使用する前に、リボンの [開発] タブを有効にする必要があります。詳細については、 [開発] タブを表示するを参照してください。

HOME ≫エクセル2010基本講座：目次 ≫ エクセル2013基本講座：目次 ≫ フォーム・ActiveX・マクロ(VBA)の共通メニュー
Docs/ Office VBA Reference/Excel/Concepts/Controls, dialog boxes, and forms/Use ActiveX Controls on sheets シートで ActiveX コントロールを使用する - Docs

シートのコントロールを処理する場合は、次に示すことに注意してください。

• ActiveX コントロールで利用できる標準プロパティのほか、次のプロパティも Microsoft Excel の ActiveX コントロールで利用できます: BottomRightCell 、 LinkedCell 、 ListFillRange 、 Placement 、 PrintObject 、 TopLeftCell 、および ZOrder 。

これらのプロパティは、ActiveX コントロール名を使用して値の取得および設定ができます。次の使用例は、CommandButton1 の位置がブック ウィンドウの左上隅になるようにブック ウィンドウをスクロールします。

Set t = Sheet1.CommandButton1.TopLeftCell
With ActiveWindow
.ScrollRow = t.Row
.ScrollColumn = t.Column
End With

?ActiveX コントロールをアクティブにすると、一部の Microsoft Excel Visual Basic のメソッドやプロパティが使用できなくなります。たとえば、 Sort メソッドは、コントロールをアクティブにすると使用できません。そのため、次の使用例では、ボタンの Click イベント プロシージャは失敗します。ユーザーがクリックした後も、コントロールがアクティブな状態を保つためです。
失敗したプロパティまたはメソッドを使用する前に、シート上の他の要素をアクティブにします。たとえば、範囲を並べ替えるコードは次のようになります

• 別のアプリケーションの文書に埋め込まれた Excel ブックのコントロールは、編集のためにそのブックをユーザーがダブルクリックしても機能しません。このコントロールが機能するには、ユーザーがブックを右クリックしてショートカット メニューで [ 開く ] をクリックします。
• Excel 5.0、および Excel95 のブック形式を使用して、Excel ブックを保存すると、ActiveX コントロール情報は失われます。
• シートの ActiveX コントロールのイベント プロシージャ内の Me キーワードは、コントロールではなく、シートを参照します。

Visual Basic でコントロールを追加する

Excel では、ActiveX コントロールは、 OLEObjects コレクションの OLEObject オブジェクトで表されます。すべての OLEObject オブジェクトは Shapes コレクションにも含まれています。プログラムでシートに ActiveX コントロールを追加するには、 OLEObjects コレクションの Add メソッドを使用します。次の使用例は、ワークシート 1 にコマンド ボタンを追加します。

Worksheets(1).OLEObjects.Add "Forms.CommandButton.1", _
Left:=10, Top:=10, Height:=20, Width:=100

Visual Basic でコントロールのプロパティを使用する

Visual Basic コードでは、通常 ActiveX コントロールを名前で参照します。次の使用例は、"CommandButton1" というコントロールのキャプションを変更します。

Worksheets(1).OLEObjects("CommandButton1").Left = 10

OLEObject オブジェクトのプロパティとして表されない、コントロールのプロパティは Object プロパティを使用して実際のコントロール オブジェクトを取得して設定することができます。次の使用例は、"CommandButton1" というコントロールのキャプションを変更します。

Worksheets(1).OLEObjects("CommandButton1"). _
Object.Caption = "run me"

すべての OLE オブジェクトは Shapes コレクションのメンバーでもあるため、このコレクションを使用して、いくつかのコントロールにプロパティを設定できます。次の使用例は、ワークシート 1 のすべてのコントロールの左端の位置を設定して揃えます。

For Each s In Worksheets(1).Shapes
If s.Type = msoOLEControlObject Then s.Left = 10
Next

コレクションおよび OLEObjects コレクションでコントロール名を使う

ワークシートの ActiveX コントロールは、2 つの名前を持ちます。シートを表示したときに [ 名前 ] ボックスでコントロールが含まれる図形の名前、および [ プロパティ ] ウィンドウの [(名前)] の右にあるセルでコントロールのコード名を確認できます。最初にワークシートに追加したコントロールでは、図形の名前とコード名が一致しています。しかし、図形の名前かコード名のどちらかを変更しても、もう片方の名前が一致するように自動的に変更されることはありません。
コントロールのイベント プロシージャの場合は、コントロールのコード名を使います。 Shapes または OLEObjects コレクションからコントロールを取得する場合は、コード名ではなく、図形の名前を使ってコントロールを指示します。たとえば、コード名および図形の名前が既定の CheckBox1 というチェック ボックスを追加したとします。コントロールのプロパティ ウィンドウで Name プロパティを「chkFinished」と設定してコード名を変更した場合、イベント プロシージャでは必ずコントロールのコード名を使い、 Shapes または OLEObject コレクションからコントロールを取得する場合、次のように CheckBox1 を使います。

Private Sub chkFinished_Click()
ActiveSheet.OLEObjects("CheckBox1").Object.Value = 1
End Sub

AcriveXを挿入するときStopステートメントをいれたりBreakPointを設けると中断モードでは実行できませんエラーが発生する

中断モードでは入力できません　とか
このサイトのVBAはFormsを参照設定しているようだ。
64Bit: C:\Windows\System32\FM20.DLL
32Bit: C:\Windows\Syswow64\FM20.DLL
Microsoft Form 2.0 Object Library

総合するとAcriveXのオブジェクトは使用禁止

このサイトにあるようにActiveXコントロールはかなりクセがある。しかもソートができなくなるなど、致命的だ。しかも一般的にソートができなくなることがActiveXコントロールが原因だとだれも思わないだろう。知らなければ絶対に不可能だ。
たしかにActiveXContorolはイベントがたくさん使えるが、ワークシートに貼るオブジェクトに複雑な命令を使うとよけい可読性がなくなる。このため使うメリットが全くない。
以上から推奨とか生易しいことはいわない。使うな。使用禁止は推奨とかではない。強要して強制して押し付ける。