Set date for 2 days later

[kakiebos]

Hi All,

I have a UserForm where the user must enter all the public holidays for the year. In South Africa we have a Public Holiday for the Easter Weekend. The Friday and the Monday is Public holidays. The lazy me, I mean that is why we use Makros, want to automatically add the date for the Monday Holiday into the respective TextBox when the user entered the date for Easter Friday.

The code I have so far is not adding the days.

Any advice please.

Private Sub pphGoodFriday_AfterUpdate()
    
    Me.pphFamDay.Value = Me.pphGoodFriday.Value + 3


End Sub

The date format is "dd MMMM YYYY" for example 22 April 2019. I want the pphFamDay value then to be 25 April 2019.

 

[shknbk2]

You can try something like this. Note that I added a re-formatting of pphGoodFriday as well in case that date was entered using a different format (e.g., like 22/8/2019).

Private Sub pphGoodFriday_AfterUpdate()
    Dim d As Date
    On Error Resume Next
    d = CDate(Me.pphGoodFriday.Value)
    If d = 0 Then
        MsgBox "Please enter a valid date for Good Friday."
    Else
        Me.pphGoodFriday.Value = Format(CStr(d), "dd MMMM YYYY")
        Me.pphFamDay.Value = Format(CStr(d + 3), "dd MMMM YYYY")
    End If
End Sub

 

[kakiebos]

You can try something like this. Note that I added a re-formatting of pphGoodFriday as well in case that date was entered using a different format (e.g., like 22/8/2019).

Private Sub pphGoodFriday_AfterUpdate()
    Dim d As Date
    On Error Resume Next
    d = CDate(Me.pphGoodFriday.Value)
    If d = 0 Then
        MsgBox "Please enter a valid date for Good Friday."
    Else
        Me.pphGoodFriday.Value = Format(CStr(d), "dd MMMM YYYY")
        Me.pphFamDay.Value = Format(CStr(d + 3), "dd MMMM YYYY")
    End If
End Sub

Thanks. This works perfectly.

May I ask you a favour. Im new to the coding world and have much to learn. Can you explain to me what CDate in your code is. I presume it has something to do with the date. I would also like a explanation on the CStr.

 

[shknbk2]

The CDate and CStr are type converstions. What they do is explicitly convert one type into the specified type. Both of these uses in this code is not really needed. The conversions happen automatically in a typical scenario. That is, when the code sees that it needs to put a date into a string value or vice-versa, it converts the data automatically. However, while they not absolutely needed, I put them in just to be more explicit in what was going on.

The code starts off with declaring a Date variable 'd'. However, the textbox "pphGoodFriday" is a string format. Using the conversion CDate (or not, see above), the string value in the textbox can be converted into a date . . . maybe!

If the string is a typical date type of text, the conversion is easy. For example, the strings "3/10/19" or "3 October 2019" are easily convertible to a Date (which, by the way, uses a number as its base). The value of d in this case (for 3/10/2019) is 43741.

However, if the string is not convertible into a date (like 'apples'), then the code on the line "d = CDate(Me.pphGoodFriday.Value)" fails. If there is no error handling, the code will actually stop on this line (and turn yellow) and require you to tell it what to do. For this reason, I included "On Error Resume Next", which tells the code to keep going even if there is an error. The result of this is that if there is an error with the textbox value not being a date string, d can't be assigned a new value, and the value for d never changes from '0', which is what it is assigned when it was declared.

Then, if d is still '0', we know that the conversion failed, and there must be something wrong with the text that it couldn't be converted into a date...something the code couldn't decipher as a date type of information.

If d is not '0', then we know that the conversion worked and that the date 2 days in advance can be determined.

 

[kakiebos]

Thank you so much for your explanation. This helps me a lot in understanding what is happening in the code. It is quite difficult to teach yourself vba. I only started about 6 months ago.

Once again,

Thank you.