code that cleans up after itself...
Posted by rm on December 24, 2001 1:47 PM
...is there a way for a vba routine to run only once then delete itself from the "ThisWorkbook" object?
I am creating a template that when launched, will create a new pre-designed workbook for the user, run a vba routine, then save as a default name based on a cell's value...the above problem is the last hurdle to complete this project...many thanks in advance...rm
Posted by Jacob on December 24, 2001 1:58 PM
I dont think that code can delete itself, but this workaround will work.
sub
if sheets("SomeSheet").range("SomeRange") = 1 then
your code here
end of your code
sheets("SomeSheet").Range("SomeRange").formulaR1C1= 0
else
end if
end sub
Put 1 in the range you refer to then when the code runs it will change it to a 0 so the code will never trigger again since the If statement will always be false.
Hope this helps
Jacob
Posted by Gary Bailey on December 24, 2001 2:02 PM
Say if you want to call your book "xyz" then the value in A1 try
thisworkbook.saveas "xyz" & range("a1").value
Replacing Range("a1") with wherever your filename is. You could use a named range for clarity.
Gary
Posted by Gary Bailey on December 24, 2001 2:17 PM
I'm not so sure you can't get code to delete itself. Try
Dim objVBP As VBProject
Dim objVBComp As VBComponent
Set objVBP = ActiveWorkbook.VBProject
For Each objVBComp In objVBP.VBComponents
objVBComp.CodeModule.DeleteLines 1, objVBComp.CodeModule.CountOfLines
Next objVBComp
Gary
Posted by Gary Bailey on December 24, 2001 2:24 PM
SORRY FORGOT
I forgot. You'll need a reference to the VB IDE object library for my last post to work.
Tools->References
Microsoft VBA Extensibility library (or something like that!)
Sorry
Gary
Posted by Ivan F Moala on December 24, 2001 3:06 PM
Yes...you actually can cleanup code......BUT you
have to be real careful. The code you gave is a start.....then you have to delete all code in
worksheets + Userforms
Ivan I'm not so sure you can't get code to delete itself. Try Dim objVBP As VBProject
Posted by Gary Bailey on December 24, 2001 3:15 PM
The code below deleted all code behind worksheets and userforms when I tried it. Did it behave differently on your machine?
I guess the main thing is - do it as late as possible and don't call any procedures for the first time after done the deleting.
Merry Xmas
Gary Yes...you actually can cleanup code......BUT you
Posted by Ivan F Moala on December 24, 2001 3:23 PM
Gary
Yes it deletes ALL code but leaves the Modules
and useforms in place....so when loading the
file up it will comeup with enable macros etc.
Have a merry Xmas Gary
Ivan The code below deleted all code behind worksheets and userforms when I tried it. Did it behave differently on your machine? I guess the main thing is - do it as late as possible and don't call any procedures for the first time after done the deleting. Merry Xmas Gary : Yes...you actually can cleanup code......BUT you
Posted by rm on December 24, 2001 7:18 PM
...mahalo! (thank you)
...big thanks to all...have a great holiday!
rm Gary
