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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
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






