Remove code from file after running

Posted by Coreen on February 13, 2001 11:59 AM

I have a file with the following code that runs upon opening.

Private Sub Workbook_Open()
Application.ScreenUpdating = False
Range("A17").Value = "Issue Date: " & Date
Range("A19").Select
Application.Dialogs(xlDialogSaveAs).Show
End Sub

Is there some way to automatically delete this after it finishes running or on close or on save? I don't want to change the date and be prompted to save every time I open the file. (I am currently manually deleting.)

Thank you,
Coreen

Posted by Celia on February 13, 2001 2:41 PM

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
On Error Resume Next
With ThisWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule
.DeleteLines .ProcStartLine("Workbook_Open", 0), _
.ProcCountLines("Workbook_Open", 0)
End With
End Sub

Celia

Posted by Ivan Moala on February 13, 2001 5:37 PM

I think Celia forgot to mention that you need
a reference in your project to the,
Visual Basic for Applications Extensibility object library.........

Ivan

Posted by Celia on February 13, 2001 5:54 PM

Ivan.
Thanks. Where've you been?
Celia

Posted by Ivan Moala on February 14, 2001 5:50 AM

Celia
Been away from all msg boards for a while now
programing for work.....nice to see others
giving help.

Ivan

Posted by Coreen on February 14, 2001 1:56 PM

Thank you *so much* for your help Celia and Ivan!
I finally got it to work! (^_^)
I used Workbook_BeforeClose instead of BeforeSave as my code involves saving.
I also had it delete both the "Workbook_Open" & "Workbook_BeforeClose" subs before closing so it's left with nothing.
Coreen