deactivate VBA code

Question

Dears,

kindly please i have a question related to VBA code that i want to determine a date that when match it, the running code will be deactivate,

that i want the operation of this code will go deactivated at #12:00:00 PM, 1-Mar-2014, the users can use this code till the mentioned date only.

Answer 1

Here's an even more 'dastardly' solution: if the date is on or after the specified date, then the workbook immediately closes after an announcement that the license has expired.

This goes into the ThisWorkbook code module

Private Sub Workbook_Open()

  Dim termDate As Date

  termDate = #3/1/2014 12:00:00 PM#

  If Now() >= termDate Then

    MsgBox "The license on this software has expired.", _

     vbOKOnly + vbCritical, "License Expired"

    ThisWorkbook.Close

  End If

End Sub

If you'll set the VBAProject security to require a password to view the code, then they will have a difficult time even getting to their data.  While they can hold the [Shift] key while opening the file to stop the shutdown code from running, then none of the other VBA code in the workbook is going to work either.

If you do assign a password to the VBAProject, be sure to keep a copy of the file without that lockdown so that if you forget the password in the future, you'll have a copy you can open and work with easily.  I'd also actually 'write down' the VBAProject password in a .txt file and store it in the same folder on your computer with the source files.  That helps you remember it, and since presumably others don't have access to your computer, it's still secure.

Answer 2

Hi,

Try this. As soon as the expiry date is gone then the code simply exits sub

Sub somesub()

Dim mydate As Date

' 1st March 2014

mydate = #3/1/2014 12:00:00 PM#

If Now() > mydate Then

Exit Sub

'your code

End If

Answer 3

If you want to just totally lock down a worksheet on a particular date, you could do it by locking all of the cells on that sheet and assigning it a password in the code:

Private Sub Workbook_Open()

Dim termDate As Date

  termDate = ThisWorkbook.Worksheets("ControlSheet").Range("B1")

  If Now() >= termDate Then

'lock down sheet named "SheetToLock" - change as needed

    With ThisWorkbook.Worksheets("SheetToLock")

       .Cells.Locked=True

       .Protect password:="secretPassw0rd"

    End With      

  End If

End Sub

That assumes that the sheet is not protected to begin with, if it is, you'd need to unprotect it in order to lock the cells.   This would do that and give it a different password than normal:

Private Sub Workbook_Open()

Dim termDate As Date

termDate = ThisWorkbook.Worksheets("ControlSheet").Range("B1")

If Now() >= termDate Then

'lock down sheet named "SheetToLock" - change as needed

With ThisWorkbook.Worksheets("SheetToLock")

.Unprotect password:="currentPassword" ' change as needed

.Cells.Locked=True

.Protect password:="secretPassw0rd" ' change as desired

End With

End If

End Sub

Answer 4

Set up a cell on some worksheet to hold the date you want to use.  This could be an existing worksheet or a new one - and the sheet can even be hidden from view.

Let's say you name that sheet ControlSheet and you put the date into cell B1 on it, then my code could be rewritten as:

Private Sub Workbook_Open()

Dim termDate As Date

  termDate = ThisWorkbook.Worksheets("ControlSheet").Range("B1")

  If Now() >= termDate Then

    MsgBox "The license on this software has expired.", _

       vbOKOnly + vbCritical, "License Expired"

     ThisWorkbook.Close

  End If

End Sub

You could do something similar with the code that Mike H.. provided.