How to create an ActiveX button and add code to it (tell it what sub to run) using vba?

mountainclimber11

Board Regular
Joined
Dec 1, 2009
Messages
79
Hello. I'd like to create an ActiveX button in a sheet and assign a code to it (i.e. tell it to what sub to run and that sub already exists).

I can create the button: (recorded)

Code:
    ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", Link:=False _
        , DisplayAsIcon:=False, Left:=41395.5882352941, Top:=234.705882352941, _
        Width:=119.117647058824, Height:=39.7058823529412).Select

But I'd like to change the caption and assign a sub to it so when someone clicks the newly created activex button it runs a sub that already lives in a module within the workbook that the button is to be created. Everything happens and lives in ThisWorkbook.

Thanks!

PS - I cannot use a Command button....I can only use ActiveX
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

mountainclimber11

Board Regular
Joined
Dec 1, 2009
Messages
79
adapted from:

Adding an Active X command button and its code using in Excel VBA

Code:
Sub AddingButtons()


    Dim btn As Button
    Dim t As Range
    Dim Obj As Object
    Dim Code As String
    
    Dim ShtNm As String
    With ThisWorkbook
        .Worksheets.Add(After:=Worksheets(1)).Name = "My New Worksheet"
        .Sheets("My New Worksheet").Activate
    End With
    
    ShtNm = ActiveSheet.Name
    Sheets(ShtNm).Select
    
    Set t = ActiveSheet.Range("D3:F4")


'create button
    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)


'button text
    ActiveSheet.OLEObjects(1).Object.Caption = "Show Data Selection Window"


'macro text
    Code = "Private Sub CommandButton1_Click()" & vbCrLf
    Code = Code & "Call MyTestSub(ShtNm)" & vbCrLf
    Code = Code & "End Sub"


MsgBox "Worksheet name is " & ActiveSheet.Name
'add macro at the end of the sheet module
With ActiveWorkbook.VBProject.VBComponents(Worksheets(ShtNm).CodeName).CodeModule
        .insertlines .CountOfLines + 1, Code
End With


End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,119,249
Messages
5,576,943
Members
412,753
Latest member
Coach_Olson
Top