asked on

adding a procedure to a excel workbook in vb.net

I need to create the following at runtime in vb.net
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 & "Userform1.show" & vbCr & "end sub"
' Add the VBA macro to the new code module.
oModule.CodeModule.AddFromString(scode)
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.

Wayne Taylor (webtubbs)

Hi LeeHopkins,

Take a look at this site -> http://www.cpearson.com/excel/vbe.htm

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

Regards,

Wayne

LeeHopkins

ASKER

nope not in my .net code
Microsoft.Vbe.Interop.VBComponents' cannot be indexed because it has no default property

ASKER CERTIFIED SOLUTION

Wayne Taylor (webtubbs)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

LeeHopkins

ASKER

You R0CK Dude, will this work for adding some code to a sheet
beforedoubleclick
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
ActiveSheet.ShowDataForm
End Sub

Wayne Taylor (webtubbs)

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

Wayne

LeeHopkins

ASKER

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