VBA - Passcode based on current date

[Lee Rabbit]

Hi All,

I am trying to set a six digit passcode that is entered via a userform when workbook opens. However, I need this passcode to change based on the current date.

Example:

If today's date is less than 30/06/2020 then passcode is equal to P1 value
If today's date is between 1/07/2020 and 31/07/2020 then passcode is equal to P2 value

InputBox is called Pword
Form is called InputPass

Private Sub InputButton_Click()

    Dim toDAY As Date

    Dim D1 As Date
    Dim D2 As Date
    
    Dim P1 As String
    Dim P2 As String
    
    Set D1 = 30 / 6 / 2020
    Set D2 = 31 / 7 / 2020
    
    Set P1 = "123456"
    Set P2 = "654321"

    
    If toDAY < D1 Then Pword = P1
    If P1 = True Then Unload InputPass
    Else
    MsgBox " PASSCODE INCORRECT, PLEASE TRY AGAIN " _
        , vbCritical, "ERROR MESSAGE"
    
    'Here I need the dates to be greater than 30/06/2020 but less than 31/07/2020 and be equal to P2

    End If
    End If
    
    
End Sub

Thought I would try and see if I could do it for the current month to test if it works..... BUT..... it doesn't as I get an Compile error: Object required on the line Set D1 = 30/6/2020

I am stuck now so any help is gratefully received.
Thanks in advance.

Regards,
Lee

 

[Joe4]

One way that should work, regardless of your regional settings or which version of Excel you are using is:

   Set D1 = DateSerial(2020,6,30)
   Set D2 = DateSerial(2020,7,31)

 

[Lee Rabbit]

One way that should work, regardless of your regional settings or which version of Excel you are using is:

   Set D1 = DateSerial(2020,6,30)
   Set D2 = DateSerial(2020,7,31)

Thanks Joe4 for the help, however, I am still getting the Object Required error on the same line.

 

[Joe4]

Thanks Joe4 for the help, however, I am still getting the Object Required error on the same line.

Doh!
I forgot to remove the word "Set". You only use that when setting objects (like ranges), not with setting variables.
So just get rid of the words "Set".