VBAString Tip 6: Input Box In Excel VBA To Add A Sequence

In this tip will use VBA To add a sequence of months to a worksheet with the input box

First add the following code to a button called “Button1″‘s click event:

Sub Button1_Click()
   
    Dim dteDate As Date
    Dim dteNewDate As Date
    Dim intColumn As Integer
    
    Dim intAnswer As Integer
    
    dteDate = InputBox("Enter start date", "Start Date", Date)
    
    intAnswer = MsgBox("Is the start date '" & dteDate & "' you entered correct?", vbQuestion + vbYesNo, "Please Verify")
    
    Select Case intAnswer
        Case vbYes
            'for the first date
            dteNewDate = DateAdd("m", 0, dteDate)
            Cells(1, 1) = dteNewDate
            
            'for the 2nd to 10
            For intColumn = 1 To 9
                dteNewDate = DateAdd("m", intColumn, dteDate)
                Cells(1, intColumn + 1) = dteNewDate
            Next
        Case vbNo
            MsgBox "Try Operation Again", vbExclamation, "Cancel"
            
            Exit Sub
    End Select
    
End Sub

In the first image, I am using the VBA inputbox to accept a date entry:

In the following image I am asking the user to confirm their date entry using the VBA msgbox :

If the selection is positive, I am using the DateAdd function, to keep on adding a month according to my loop count.

Also since I am writing to the columns of the worksheet in the first row, which are alpha characters instead of numeric (like the rows), I use “Cells()” which takes the column index instead of the alpha (A-XFD) name like “Range()”,as shown in the following image:

Watch it in action:

****************************************************

Let me know if you have any questions

[simple_contact_form]