asked on

VBA - Create Command buttons

Hi

I'm trying to find a way to create command buttons via VBA. I would also like to rename them and attach a code to them too.

I would like the code to sit within the worksheet.

Rgonzo1971

Hi,

Pls try

Sub Macro2()

    Set cb = ActiveSheet.Buttons.Add(785.25, 90.75, 102, 43.5)
    cb.Caption = "myMacro"
    cb.OnAction = "MyMacro"
End Sub

Open in new window

Regards

Jen Payne

ASKER

Hi,

Thanks, is there a way to add a command button (active x control) instead, because my code is in the sheet module?

Rgonzo1971

then try

Sub AddingButtons()

Dim t As Range
Dim Obj As Object
Dim Code As String

ShtNm = ActiveSheet.Name

Set t = ActiveSheet.Range("B5:F6")

Set Obj = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", _
       Link:=False, DisplayAsIcon:=False, Left:=t.Left, Top:=t.Top, Width:=t.Width, Height:=t.Height)

Obj.Caption = "Show Data Selection Window"

Code = "Private Sub CommandButton1_Click()" & vbCrLf
Code = Code & "Call MySub(ShtNm)" & vbCrLf
Code = Code & "End Sub"

With ActiveWorkbook.VBProject.VBComponents(Worksheets(ShtNm).CodeName).CodeModule
    .insertlines .CountOfLines + 1, Code
End With

End Sub

Open in new window

Peter Chan

Hi,
Check this way below
https://stackoverflow.com/questions/4559094/how-to-add-a-button-programmatically-in-vba-next-to-some-sheet-cell-data

ASKER CERTIFIED SOLUTION

Jen Payne

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jen Payne

ASKER

found an example and answer on another site