How to use VBA code to obtain date from cell, then calculate 3 months later
Category: [Excel] Demo Available
How to use VBA code to obtain date from cell, then calculate 3 months later
Requirement:
The user has a userForm that has a text Box where the user places a date (e.g. 20-10-2017) and would like a script to place a 1 month, 3 months and 6 months dates in to 3 different cells.
In other words: enter 20-10-2017 and 3 different textboxes give me different results: 20-11-2017 (1 month), 20-01-17 (3 months) and 20-05-2017 (6 months).
The textboxes are called:
StartDate: (I enter the date here)
Reminder 1: (automatic date placed)
Reminder 3: (automatic date placed)
Reminder 6: (automatic date placed)
Solution:
Code:
Private Sub CommandButton1_Click()
Dim i As Integer, Mnths, dt As Date
dt = StartDate.Value
Mnths = Array(1, 3, 6) '// These are the number of months to add.
For i = 0 To 2
Me.Controls("Reminder" & Mnths(i)) = DateAdd("m", Mnths(i), dt)
Next
End Sub
I never use Excel's built-in Date Picker (especialy now as it is unavailable in Excel 2016 64Bit), there are plenty of better alternatives available as Add-ins, such as Ron de Bruin's, see here: https://www.rondebruin.nl/win/addins/datepicker.htm
If you are using Excel's built-in date Picker then you MAY be able to do away with the command button and have the 3 text boxes fill automatically when the date in the Date Picker changes by using
Code:
Private Sub StartDate_Change()
Dim i As Integer, Mnths, dt As Date
dt = StartDate.Value
Mnths = Array(1, 3, 6) '// These are the number of months to add.
For i = 0 To 2
Me.Controls("Reminder" & Mnths(i)) = DateAdd("m", Mnths(i), dt)
Next
End Sub
Obtained from the OzGrid Help Forum.
Solution provided by KjBox.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How use VBA to find tab with date |
| How to get a specific date when you enter any other dates for the week |
| How to Compute a Date Difference in an IF statement |
| How to use the DATEDIFF Function (VBA) |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.