Excel VBA Video Training/ EXCEL DASHBOARD REPORTS

Ozgrid, Experts in Microsoft Excel Spreadsheets

Add/Subtract Dates In Excel VBA

 

Got any Excel Questions? Free Excel Help

EXCEL VBA: Create A Date Adder

Here's how to create relatively easy Date Adder by using a UserForm, 3 TextBox Controls, 5 Label Controls, 1 ComboBox Control and 1 CommandButton Control.

 DOWNLOAD DATEADDER

  1. TextBox1 = Start Date
  2. TextBox2 = Positive/Negative Amount
  3. ComboBox1 = Period. I.e Days, Weeks, Months, Year
  4. CommandButton1 = Add/Take\
  5. Label1 = Result

CODE

Dim strPeriod As String


Private Sub ComboBox1_Change()
    If ComboBox1.ListIndex = -1 Then
        MsgBox "Invalid period", vbCritical
        ComboBox1.SetFocus
        Exit Sub
    End If
    
    With ComboBox1
        If .ListIndex = 0 Then strPeriod = "D"
        If .ListIndex = 1 Then strPeriod = "WW"
        If .ListIndex = 2 Then strPeriod = "M"
        If .ListIndex = 3 Then strPeriod = "YYYY"
    End With

End Sub


Private Sub CommandButton1_Click()

   If Not IsDate(TextBox1) Then
        MsgBox "Non valid date", vbCritical
        TextBox1 = vbNullString
        TextBox1.SetFocus
        Exit Sub
    End If
    
    If Not IsNumeric(TextBox2) Then
        MsgBox "Invalid amount", vbCritical
        TextBox2 = vbNullString
        TextBox2.SetFocus
        Exit Sub
    End If
    
    If ComboBox1.ListIndex = -1 Then
        MsgBox "Invalid period", vbCritical
        ComboBox1.SetFocus
        Exit Sub
    End If
    
    Label1 = Format(DateAdd(strPeriod, TextBox2, TextBox1), "dddd dd mmm yyyy")
End Sub


Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox1 = vbNullString Then Exit Sub
    If Not IsDate(TextBox1) Then
        MsgBox "Non valid date", vbCritical
        TextBox1 = vbNullString
        Cancel = True
    End If
End Sub



Private Sub UserForm_Initialize()
    ComboBox1.List = Split("Day,Week,Month,Year", ",")
End Sub

Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over $64.00. ALL purchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to [email protected] 31 days after purchase date.



Instant Download and Money Back Guarantee on Most Software

Excel VBA Video Training/ EXCEL DASHBOARD REPORTS

Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!

Microsoft and Microsoft Excel are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft

Some of our more popular products are below...
Convert Excel Spreadsheets To Webpages | Trading In Excel | Construction Estimators | Finance Templates & Add-ins Bundle | Code-VBA | Smart-VBA | Print-VBA | Excel Data Manipulation & Analysis | Convert MS Office Applications To...... | Analyzer Excel | Downloader Excel | MSSQL Migration Toolkit | Monte Carlo Add-in | Excel Costing Templates