I am wanting to create a demo for a VBA module I have created. The module has many different Private functions that are called by 2 Public functions (the public functions are used in Excel). I want to distribute the code as an Excel Add-in (.xlam) file with password protection. Is there a way to determine whether the Add-in is being used for the FIRST time? I.e. In an Excel Workbook the user clicks on Developer -> Excel Add-ins -> Ticks "my Add-in" then an input box will ask for the password, but then only again once a specified date has passed (i.e. the first trial has ended)?
I only have a working solution for the Workbook itself, but I am struggling to create a similar method within the actual module code so that it is not Workbook specific. Here is the Sub for the ThisWoorkbook Object in VBA:
Private Sub Workbook_Open()
Dim i, trials, passCnt As Integer
trials = 3 'Number of trials
passCnt = 4 'Number of times to enter password
ReDim AllPassWords(1 To trials) As String
AllPassWords(1) = "123"
AllPassWords(2) = "456"
AllPassWords(3) = "789"
ReDim ExpDate(1 To trials) As Date 'We pre-define the expiry dates and passwords
ExpDate(1) = CStr(DateSerial(2023, 1, 30))
ExpDate(2) = CStr(DateSerial(2023, 2, 30))
ExpDate(3) = CStr(DateSerial(2023, 3, 30))
Dim PassWord As String
If CDate(Now) < ExpDate(1) Then 'If the 1st trial has not expired we do the following
For i = 1 To passCnt ' chances to enter password
'Enter password before we can use the worksheet
PassWord = InputBox("Trial 1 has started. Please input password.")
If PassWord = AllPassWords(1) Then
Exit For
ElseIf i < passCnt Then
MsgBox "Incorrect password. " & passCnt - i & " attempts remaining."
ElseIf i = passCnt Then
MsgBox "Password limit reached. Closing workbook"
ThisWorkbook.Close
End If
Next i
MsgBox ("You have " & ExpDate(1) - CDate(Now) & " days left")
ElseIf CDate(Now) < ExpDate(2) And CDate(Now) > ExpDate(1) Then
It is easy to check the Current Date within my Private or Public functions that form the Add-in, but I don't want a message box to pop up every time the user opens a Workbook, ONLY when the user activates the Add-in. Is this possible?