CODEWITHSUNDEEP
vbaexcel
diem_L
diem_L

Reputation: 399

VBA Add CommandButton With Code - Runtime Error 9

In my Workbook i have a CommandButton which opens a New Workbook and adds a CommandButton.

My Problem now is, that i always get the run time Error 9 when i click the Button.

This is my Code for the new Workbook:

Sub PM_Controlling_Click()
    Dim relativePath As String

    Workbooks.Add
    relativeString = ThisWorkbook.Path & "\Test2"
    ActiveWorkbook.SaveAs Filename:=relativeString & ".xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled

    Call Import_UserForm
    Call Import_Modul
    Call Working_Structur
End Sub

This i my Import Code:

Sub Import_UserForm
    Workbooks("Test2.xlsm").VBProject.VBComponents.Import Filename:= _
    "C:\Users\Desktop\Code_Samples\UserForm1.frm"
End Sub

Sub Import_Modul
    Workbooks("Test2.xlsm").VBProject.VBComponents.Import Filename:= _
    "C:\Users\Desktop\Code_Samples\AddAZ.bas"
End Sub

And here is my Working_Structur Modul where i try to add the Button with Code:

Sub Working_Structur()
    Dim btn1 As Object
    Dim Code As String

    Set btn1 = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", Link:=False _
    , DisplayAsIcon:=False, Left:=105, Top:=175, Width:=50, Height:=25)
    ActiveSheet.OLEObjects(1).Object.Caption = "Watch"
    btn1.Name = "Watch AZ"
    Code = "Sub Watch_Click()" & vbCrLf
    Code = Code & "Call Watch_AZ_Sheet" & vbCrLf
    Code = Code & "End Sub"     

    ' Next Part causes the run time error
    With ActiveWorkbook.VBProject.VBComponents(ActiveSheet.Name).CodeModule 
        .insertlines .CountOfLines + 1, Code
    End With

I hope someone can help me to solve this problem.

EDIT:

The error appears in Sub Working_Structure, the Line

With ActiveWorkbook.VBProject.VBComponents(ActiveSheet.Name).CodeModule

causes the error.

Upvotes: 0

Views: 576

Answers (1)

nagarajannd
nagarajannd

Reputation: 715

My understanding is that Subscript out of range runtime error 9 is thrown when part of what is being referenced does not exist or is undefined.

Maybe the cause of the error is ActiveSheet.Name is not being the VBAComponent name

Please check the names of the VBA components matching your sheet actual name.

Below example throws error since the sheet name ActualSheetName is not the component name Sheet1

enter image description here

Renaming the Component name will fix the issue. Something like :

enter image description here

Update: You can directly use the codename property of worksheet in the code.

Worksheet.CodeName MSDN

With ActiveWorkbook.VBProject.VBComponents(Worksheets(ActiveSheet.Name).CodeName).CodeModule

Upvotes: 3

Related Questions