Set date for 2 days later

kakiebos

Board Regular
Joined
Jun 10, 2018
Messages
59
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.

Code:
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.
 
Last edited:

shknbk2

Board Regular
Joined
Mar 5, 2016
Messages
235
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).
Code:
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

Board Regular
Joined
Jun 10, 2018
Messages
59
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).
Code:
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

Board Regular
Joined
Mar 5, 2016
Messages
235
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

Board Regular
Joined
Jun 10, 2018
Messages
59
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.
 

Forum statistics

Threads
1,082,305
Messages
5,364,401
Members
400,800
Latest member
germanpbv

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top