Hi,
I have a procedure that adds a new workbook and I need this new workbook to have code in its workbook sheet as a Workbook_Open event.
How do I add this to the code that adds the new workbook?
Thanks.
Hi,
I have a procedure that adds a new workbook and I need this new workbook to have code in its workbook sheet as a Workbook_Open event.
How do I add this to the code that adds the new workbook?
Thanks.
Re: Code To Write Code For Workbook Open Event
hi Kj,
i modified Chip Pearson's code a litte to suit yours
see his website here:
http://www.cpearson.com/excel/vbe.htm
QuoteBefore using these procedures, you'll need to set a reference in VBA to the VBA Extensibility library
Sub AddProcedure()
Dim VBCodeMod As CodeModule
Dim LineNum As Long
Set VBCodeMod = ThisWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule
With VBCodeMod
LineNum = .CountOfLines + 1
.InsertLines LineNum, _
"Sub Workbook_Open()" & Chr(13) & _
" Msgbox ""Here is the new procedure"" " & Chr(13) & _
"End Sub"
End With
End Sub
Display More
hth
Re: Code To Write Code For Workbook Open Event
Thank you xlite.
That works just fine.
Only problem is that I have to set the reference each time I open the originating workbook. Do you know how I can set it up so that the 'VBA Extensibility Libaries' is checked when I open the originating workbook (as vaious other references are)
Thanks again.
Re: Code To Write Code For Workbook Open Event
Ok sorted ty (I had a blonde moment )
Don’t have an account yet? Register yourself now and be a part of our community!