Link to home
Start Free TrialLog in
Avatar of LeeHopkins
LeeHopkinsFlag for United States of America

asked on

adding a procedure to a excel workbook in

I need to create the following at runtime in
Private Sub Workbook_Open()
Application.OnKey "^q", "openForm"
End Sub
in an excel spread sheet.
I can create a Module
       Dim oModule As VBIDE.VBComponent
        oModule = book.VBProject.VBComponents.Add(VBIDE.vbext_ComponentType.vbext_ct_StdModule)
        scode = "Sub openForm()" & vbCr & "" & vbCr & "end sub"
        ' Add the VBA macro to the new code module.
but I need to have the sub in th "Thisworkbook" I know it has some thing to do with vbext_ct_doucment but I cant get to add.
Avatar of Wayne Taylor (webtubbs)
Wayne Taylor (webtubbs)
Flag of Australia image

Hi LeeHopkins,

Take a look at this site ->

To add your Workbook_Open event, you'd use something like this....

    Dim StartLine As Long
    With book.VBProject.VBComponents("ThisWorkbook").CodeModule
        StartLine = .CreateEventProc("Open", "Workbook") + 1
        .InsertLines StartLine, "Application.OnKey ""^q"", ""openForm"""
    End With


Avatar of LeeHopkins


nope not in my .net code
Microsoft.Vbe.Interop.VBComponents' cannot be indexed because it has no default property
Avatar of Wayne Taylor (webtubbs)
Wayne Taylor (webtubbs)
Flag of Australia image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
You R0CK Dude, will this work for adding some code to a sheet
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
End Sub
Yes, you'd do it like this....

        Dim StartLine As Long
        With book.VBProject.VBComponents.Item("Sheet1").CodeModule
            StartLine = .CreateEventProc("BeforeDoubleClick", "Worksheet") + 1
            .InsertLines StartLine, "ActiveSheet.ShowDataForm"
        End With

yep got it.
do you know if i can lock the vbaproject from code so my users can get in to look my pw i have on the form.
I have a user form i add that unlocks, sorts and locks the form from a button click on the form. but if the look at the code in the user form they can see the pw.
so I want to lock it but i cant find any control over that option