Compile Error when using the workbook_open event

[mark100]

I have a workbook that contains the code below in the 'ThisWorkbook' module:

Private Sub Workbook_Open()
On Error GoTo MyErrorHandler
Call manipulate
Me.Save

Line1:

Exit Sub

MyErrorHandler:

Resume Line1

End Sub


I want the workbook to execute the 'manipulate' macro the first time the workbook is opened and then never again.

To try and acheive this I have set up the 'manipulate' macro to delete the module it is inside at the end of its procedure. However, this causes a problem when the workbook is next opened as the call fails because the macro is missing, resulting in the Compile error 'Sub or function not defined'.

I have tried to use an error handler to exit the code, skipping the save line, in the hope that it would open the spreadsheet, but it just keeps returning to the Compile Error message.

Has anyone any suggestions on how I can get around this? If my coding or explanation is poor it is because I am new to VBA, so please be gentle.

From a confused Mark

 

[mark100]

I have managed to sort this out by keeping the 'manipulate' macro and adding the statement

If ThisWorkbook.Name <> "MyWorkbookName.xls" Then Exit Sub

to the 'ThisWorkbook' module code.

It works as the 'manipulate' macro forces the user to save the workbook with a new name, therfore when the new workbook is subsequently opened it will end the procedure before running the macro.

I found the code on an Ozgrid.com forum posted by a royUK.

 

[erik.van.geit]

Hi, mark100

it's nice you are posting the solution !!
you deserve a warm
<font face="Courier New" size="+1" color="blue" style="background: url(http://www.balloondoggies.com/vonpookie/misc/sparkle.gif)">WELCOME TO THE BOARD !!!!</font>

kind regards,
Erik