Link to home
Start Free TrialLog in
Avatar of Peter Chan
Peter ChanFlag for Hong Kong

asked on

Problem to call function

Hi,

Once I call this function
Public Function CreateButton(xDate As Date)
    
    ''UnhideSheets
    Dim NName As String
    NName = "btn1"

    Dim myCmdObj As OLEObject
    Set myCmdObj = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", Left:=470, Top:=5, Width:=60, Height:=25)
     ' Define buttons name
    myCmdObj.Name = NName
     ' Define buttons caption
    myCmdObj.Object.Caption = "refresh"
    myCmdObj.PrintObject = False
    
     ' Inserts code for the button
    With ThisWorkbook.VBProject.VBComponents(ActiveSheet.CodeName).CodeModule
        N = .CountOfLines
        .InsertLines N + 1, "Private Sub " & NName & "_Click()"
        .InsertLines N + 2, vbNewLine
        .InsertLines N + 3, vbTab & "refresh"
        .InsertLines N + 4, vbNewLine
        .InsertLines N + 5, "End Sub"
    End With
    
    Dim myCmdObj1 As OLEObject
    NName = "btnNextMonth"
    Set myCmdObj1 = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", Left:=560, Top:=5, Width:=60, Height:=25)
    myCmdObj1.Name = NName
    myCmdObj1.Object.Caption = "Next Month"
    myCmdObj1.PrintObject = False
    
    With ThisWorkbook.VBProject.VBComponents(ActiveSheet.CodeName).CodeModule
        N = .CountOfLines
        .InsertLines N + 1, "Private Sub " & NName & "_Click()"
        .InsertLines N + 2, vbNewLine
        .InsertLines N + 3, vbTab & "goNextMonth"
        .InsertLines N + 4, vbNewLine
        .InsertLines N + 5, "End Sub"
    End With
    
    Dim myCmdObj2 As OLEObject
    NName = "btnPrevMonth"
    Set myCmdObj2 = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", Left:=620, Top:=5, Width:=60, Height:=25)
    myCmdObj2.Name = NName
    myCmdObj2.Object.Caption = "Prev Month"
    myCmdObj2.PrintObject = False
    
    With ThisWorkbook.VBProject.VBComponents(ActiveSheet.CodeName).CodeModule
        N = .CountOfLines
        .InsertLines N + 1, "Private Sub " & NName & "_Click()"
        .InsertLines N + 2, vbNewLine
        .InsertLines N + 3, vbTab & "goPrevMonth"
        .InsertLines N + 4, vbNewLine
        .InsertLines N + 5, "End Sub"
    End With
        
End Function

Open in new window

within Excel 2003 VBA codes, I get this error

User generated image
why?
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

Instead of trying to create Subs in the workbook you could create and distribute a template workbook  (.xlt) that already contains the subs.
Avatar of Peter Chan

ASKER

Can I have more details to your way? But I'm using Excel 2003, while the codes do work, and only such error happens.
I ran your code in Excel 2010 and created the attached Excel 2003 template file. By the way your CreateButton sub requires that it be passed a date but that date is never used.
28714201.xlt
Thanks. I see your Excel file.
But why did the error come out, due to the same codes, within my Excel workbook?
I'm sorry but I don't have Excel 2003.
ASKER CERTIFIED SOLUTION
Avatar of byundt
byundt
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
In Excel 2003, record a macro when adding a button. That recored macro should show you how to add one using your VBA code.