2
Contributors
1
Reply
2
Views
5 Years
Discussion Span
Last Post by davesexcel
0

You can add a button from the activeX Contols, then write a code for that button in the worksheet module

Sub AddButtonMakeMacro()
    Dim MySht As Worksheet
    Dim MyOle As OLEObject

    Set MySht = ActiveSheet
    MySht.Range("H1").Select
    Set MyOle = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", _
                                           Left:=237.75, Top:=21, Width:=93, Height:=22.5)

    With MyOle
        .Object.Caption = "Click Me"
        .Name = "myMacro"
    End With

    With ThisWorkbook.VBProject.VBComponents(MySht.CodeName).CodeModule
        .InsertLines .CreateEventProc("Click", MyOle.Name) + 1, _
                     vbTab & "If Range(""A1"").Value > 0 Then " & vbCrLf & _
                     vbTab & vbTab & "Msgbox ""Hi""" & vbCrLf & _
                     vbTab & "End If"

    End With

End Sub

Edited by happygeek: spam link deleted

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.