Compile Error when using the workbook_open event

mark100

New Member
Joined
Oct 23, 2006
Messages
2
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
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

mark100

New Member
Joined
Oct 23, 2006
Messages
2
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,113,845
Messages
5,544,629
Members
410,626
Latest member
rkmadasu
Top