{"id":106,"date":"2018-11-05T18:15:32","date_gmt":"2018-11-05T18:15:32","guid":{"rendered":"http:\/\/www.vbastring.com\/blog\/?p=106"},"modified":"2020-11-23T22:25:50","modified_gmt":"2020-11-23T22:25:50","slug":"vbastring-tip-6-input-box-in-excel-vba-to-add-a-sequence","status":"publish","type":"post","link":"https:\/\/vbastring.com\/blog\/2018\/11\/05\/vbastring-tip-6-input-box-in-excel-vba-to-add-a-sequence\/","title":{"rendered":"VBAString Tip 6: Input Box In Excel VBA To Add A Sequence"},"content":{"rendered":"

In this tip will use VBA To add a sequence of months to a worksheet with the input box<\/p>\n

First add the following code to a button called “Button1″‘s click event: <\/p>\n

\r\nSub Button1_Click()\r\n   \r\n    Dim dteDate As Date\r\n    Dim dteNewDate As Date\r\n    Dim intColumn As Integer\r\n    \r\n    Dim intAnswer As Integer\r\n    \r\n    dteDate = InputBox(\"Enter start date\", \"Start Date\", Date)\r\n    \r\n    intAnswer = MsgBox(\"Is the start date '\" & dteDate & \"' you entered correct?\", vbQuestion + vbYesNo, \"Please Verify\")\r\n    \r\n    Select Case intAnswer\r\n        Case vbYes\r\n            'for the first date\r\n            dteNewDate = DateAdd(\"m\", 0, dteDate)\r\n            Cells(1, 1) = dteNewDate\r\n            \r\n            'for the 2nd to 10\r\n            For intColumn = 1 To 9\r\n                dteNewDate = DateAdd(\"m\", intColumn, dteDate)\r\n                Cells(1, intColumn + 1) = dteNewDate\r\n            Next\r\n        Case vbNo\r\n            MsgBox \"Try Operation Again\", vbExclamation, \"Cancel\"\r\n            \r\n            Exit Sub\r\n    End Select\r\n    \r\nEnd Sub\r\n<\/pre>\n
In the first image, I am using the VBA inputbox to accept a date entry:<\/p>\n
\"excel_input_box_sequence_1\"<\/a><\/p>\n
In the following image I am asking the user to confirm their date entry using the VBA msgbox :<\/p>\n
\"excel_input_box_sequence_2\"<\/a><\/p>\n
If the selection is positive, I am using the DateAdd function, to keep on adding a month according to my loop count.<\/p>\n
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:<\/p>\n
\"excel_input_box_sequence_3\"<\/a><\/p>\n
Watch it in action:<\/p>\n