Input Output

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

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

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

Tag Archives: Input Output

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