Time Lapsed code to delete code !

[Eurekaonide]

Hi All

What sort of code would I need to write which could be triggered by a date and effectively delete VBA Code?

I have something that needs to run till a said time and then be deleted as it could interfere with other codes running after this time.

 

[VoG]

This should help http://www.cpearson.com/excel/VBE.aspx

 

[Eurekaonide]

Thanks for this its really helpful.

Just one area I'm having toruble with is getting it to run only on a set date.

My initial thought was to ahve the function Now() embedded into a worksheet and then name this cell DAY - then use this as a variable in the project coding Dim DAY but I';m struggling to get the VBE code to understand that If DAY = 19/05/2011 (don't care the format) then execute code - if it does not state this date exit the code without running.

Any ideas?

 

[VoG]

Try using TODAY() instead of NOW()

or maybe just

If Date = DateValue("19/05/2011") Then

 

[Eurekaonide]

Fab thank you soo much !!!!

 

[Eurekaonide]

.. Just one thing more.

Will this code automatically execute or will I need to set an event procedure?

Sub DeleteAllVBACode()
        Dim VBProj As VBIDE.VBProject
        Dim VBComp As VBIDE.VBComponent
        Dim CodeMod As VBIDE.CodeModule
        
        Set VBProj = ActiveWorkbook.VBProject
        
        If Date = DateValue("20/05/2011") Then
        
        For Each VBComp In VBProj.VBComponents
            If VBComp.Type = vbext_ct_Document Then
                Set CodeMod = VBComp.CodeModule
                With CodeMod
                    .DeleteLines 1, .CountOfLines
                End With
            Else
                VBProj.VBComponents.Remove VBComp
            End If
        Next VBComp
        Else: Exit Sub
        End If
    End Sub

 

[VoG]

It won't run by itself. You could place a call to it in the Workbook_Open event in ThisWorkbook.

 

[Eurekaonide]

Ok I'm having a bad moment here, why cannot I not get a call procedure to work?

I was putting in the This workbook area

Sub Workbook_Open()
GoTo (then the Sub name)
End Sub

Should I use something other than GoTo?

 

[VoG]

You need to use Call instead of GoTo.

 

[Eurekaonide]

I ahve not used a Call code before can anyone give me the guidance in what i should be using?

This is upon a Sub Workbook_Open()

to Call a piece of code called TimePlease which is a Sub not private sub