Set expiry date 10 days from opening workbook

[bptaw]

I currently have the following code in a user form:
Private Sub cmdLogin_Click()

Dim user As String
Dim password As String
user = Me.txtUserID.Value
password = Me.txtPassword.Value

If Date > DateValue("14-09-2023") Then
MsgBox "This Trial version has now expired and the Workbook will close."
ActiveWorkbook.Close SaveChanges:=False
Exit Sub

End If
If Date <= DateValue("14-09-2023") Then
MsgBox "This is a trial version and will expire on 14 September 2023."

End If

I want to change it so that the expiry date is 10 days from first opening the workbook. Anyone help?

 

[eiloken]

you can use "open" event of worbook to save number of times you opened workbook and date of first time opened to a sheet and hide that sheet:

Private Sub Workbook_Open()
    Sheet1.Cells(1, 1).Value = Sheet1.Cells(1, 1).Value + 1 'count number of times the file is opened
    If Sheet1.Cells(1, 1).Value = 1 Then 'detect first time you open workbook
        Sheet1.Cells(1, 2).Value = Date  'save the date of first time
    End If
End Sub

 

[eiloken]

you can use "open" event of worbook to save number of times you opened workbook and date of first time opened to a sheet and hide that sheet:

Private Sub Workbook_Open()
    Sheet1.Cells(1, 1).Value = Sheet1.Cells(1, 1).Value + 1 'count number of times the file is opened
    If Sheet1.Cells(1, 1).Value = 1 Then 'detect first time you open workbook
        Sheet1.Cells(1, 2).Value = Date  'save the date of first time
    End If
End Sub

or just save the date of first time you open workbook

Private Sub Workbook_Open()
    If IsEmpty(Sheet1.Cells(1, 1)) Then
        Sheet1.Cells(1, 1).Value = Date 'save date of first time open
    End If
    'call your macro
End Sub

 

[footoo]

Private Sub Workbook_Open()
If [ISREF('Hiddensheet'!A1)] Then
    If Date > Sheets("HiddenSheet").[A1] Then
        MsgBox "This Trial version has now expired and the Workbook will close."
        ActiveWorkbook.Close SaveChanges:=False
    Else: MsgBox "This is a trial version and will expire on " & Sheets("HiddenSheet").[A1]
    End If
Else
    Worksheets.Add.Name = "HiddenSheet"
    Sheets("HiddenSheet").[A1] = Date + 10
    Sheets("HiddenSheet").Visible = False
    MsgBox "This is a trial version and will expire on " & Sheets("HiddenSheet").[A1]
End If
End Sub

NOTE :
Users can work around this by :
• Disabling macros, or
• Disabling events, or
• Deleting the HiddenSheet, or
• Manually changing the date on the HiddenSheet.

 

[bptaw]

Thanks for all these responses, but I am still new at VBA not quite clear on how to implement any of these options. Can you please help me better understand the options you are suggesting and how they can fit in my login module?
I do understand that Workbooks are ultimately not secure and whatever measures I take will only work with the less adventurous.

 

[footoo]

Place the code in post #4 in the ThisWorkbook module, save the file, close it, and then open it.

 

[bptaw]

OK, got that working, but I have come across a curious situation. I have been experimenting with changing the number of days but for some reason it does not work. In addition I cannot find the HiddenSheet in the list in the VBE and the expiry date is fixed. Any explanation?

 

[bptaw]

I have since fixed the issue by changing the name of HiddenSheet and it now appears in the VBE. But I am still curious as to how it was previously completely hidden.

 

[footoo]

OK, got that working, but I have come across a curious situation. I have been experimenting with changing the number of days but for some reason it does not work. In addition I cannot find the HiddenSheet in the list in the VBE and the expiry date is fixed. Any explanation?

Re changing number of days :
• Change the code before pasting to a new workbook, or
• Delete the HiddenSheet, change the days in the code, and re-open, or
• Manually change the date in A1 on HiddenSheet.

Have no explanation as to why HiddenSheet did not appear in the VBE - perhaps you were checking before it was created.

 

[bptaw]

Thanks everyone for all your help. I think I am on top of this now and have also been able to get all bar the entry sheet to be deleted when the trial expires. Unless some hacks the VBA before expiry the sheets are gone anyway (I think).