vba creating command button when new sheet is created

[fizzyh]

I am currently trying to create a command button when a new sheet is created. However I'm facing an error. Error 438, object doesn't support this property or method. Need some assistance here. Cheers.

<CODE>Sub wdlsinflow()

    Dim r As Range, LstRw As Long, LstCo As Long
    Dim Obj As Object
    Dim Code As String

    LstRw = Sheets("sheet2").Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row
    LstCo = Sheets("sheet2").Cells.Find(What:="*", SearchOrder:=xlColumns, SearchDirection:=xlPrevious, LookIn:=xlValues).Column

    Const myCompany As String = "RECEIVABLES - INFLOWS"
    Set r = Sheets("sheet2").Columns(1).Find(myCompany, , , 1)

    If Not r Is Nothing Then
        If Not IsSheetExists(myCompany) Then
            Sheets.Add(After:=Sheets(Sheets.Count)).Name = myCompany
        End If

        With Sheets(myCompany)
            .Cells.Clear
            Range(r, Sheets("sheet2").Cells(LstRw, LstCo)).Copy .Cells(1)

         Set Obj = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", _
          Link:=False, DisplayAsIcon:=False, Left:=200, Top:=100, Width:=100, Height:=35)
             Obj.Name = "TestButton"
            'buttonn text
             ActiveSheet.OLEObjects(1).Object.Caption = "Test Button"



             Code = "Sub ButtonTest_Click()" & vbCrLf
             Code = Code & "Call Tester" & vbCrLf
             Code = Code & "End Sub"
        End With
    End If

     With Sheets(myCompany).VBProject.VBComponents(Sheets(myCompany).Name).CodeModule
        .insertlines .CountOfLines + 1, Code
    End With

End Sub</CODE>

 

[My Aswer Is This]

Try this:
To instal this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Select ThisWorkbook
Paste the below code in the VBA edit window

Private Sub Workbook_NewSheet(ByVal Sh As Object)



    ActiveSheet.Shapes.AddShape(msoShapeRectangle, 312, 149.25, 96.75, 31.5).Select
    With Selection.ShapeRange.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 0, 0)
        .Transparency = 0
        .Solid
    End With
    Selection.ShapeRange.TextFrame2.VerticalAnchor = msoAnchorMiddle
    Selection.ShapeRange(1).TextFrame2.TextRange.Characters.Text = "My Box"
    With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(1, 6). _
        ParagraphFormat
        .FirstLineIndent = 0
        .Alignment = msoAlignCenter
    End With
    With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(1, 2).Font
        .NameComplexScript = "+mn-cs"
        .NameFarEast = "+mn-ea"
        .Fill.Visible = msoTrue
        .Fill.ForeColor.ObjectThemeColor = msoThemeColorLight1
        .Fill.ForeColor.TintAndShade = 0
        .Fill.ForeColor.Brightness = 0
        .Fill.Transparency = 0
        .Fill.Solid
        .Size = 20
        .Name = "+mn-lt"
    End With
    With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(3, 4).Font
        .BaselineOffset = 0
        .NameComplexScript = "+mn-cs"
        .NameFarEast = "+mn-ea"
        .Fill.Visible = msoTrue
        .Fill.ForeColor.ObjectThemeColor = msoThemeColorLight1
        .Fill.ForeColor.TintAndShade = 0
        .Fill.ForeColor.Brightness = 0
        .Fill.Transparency = 0
        .Fill.Solid
        .Size = 20
        .Name = "+mn-lt"
    End With



End Sub

 

[fizzyh]

And how does that help me to solve my error? I have tried it. Not what i expected. But thanks for taking your time.

 

[My Aswer Is This]

I'm assuming by command button you mean a shape you can use to launch Macros. If not define command button

 

[My Aswer Is This]

I don't understand your code. Your wanting to create a command button. That what my script does. Please explain more what your attempting to do.

 

[Andrew Poulsom]

You haven't said which line causes the error. If your code worked previously this may be the issue:

http://support.microsoft.com/kb/3025036/EN-US