Removing a line of code

[Guest]

I call a module in excel each time the workbook is opened. I would like to
remove that line that performs the call once the module that is called is
finshed. I'm looking for a line of code that I could put at the end of the
called module that would remove it. Is this possible? The reason for this is
becasue a lot of work is done automatically and after the work is done the
worksheet is saved but then when it's reopened it useless becasue that call
is made but the user is just trying to view the results they already ran not
re-run the work. Any ideas?

 

[Guest]

Private Sub Workbook_Open()
if thisworkbook.Path = "" then
' perform the one time actions
end if
End Sub

 

[Guest]

Thanks for the speedy reply Tom but I'll never know where the user is running
the program from so I can't specify a path. Any other thoughts? Also, I'm
having trouble saving the workbook with a different name because I have the
code protected. I really want to have the code protected so it can't be
lifted but it's been an issue.

 

[Guest]

I don't see anywhere in the code I provided that I asked you to specify a
path. My code checks if the workbook has no path. This is the situation
when it is first created using a template -- which is what your description
sounded like. It that isn't the case, then you will have to use the
beforeclose event to remove the code after you check if its work has been
performed. If you want to protect the code, then you will not be able to
remove the code. You will have to design it to check if its work is done as
the first step and exit the sub if it has.

For code to remove code:
http://www.cpearson.com/excel/vbe.htm

 

[Tim Marsh]

it's not as neat as Tom's solutions, but you could have a cell on a sheet
somewhere that the code checks each time it runs. the first time the code
runs, it puts a value into the cell, so that the next time it runs, it sees
the entry in the cell and exits the sub without running the rest of the
code.

hth,

tim