Forum Discussion
Automation of Date
There is date appearing as 02/28/2021 in cell B6 in the attached excel sheet. I want that when the "Previous Month" button is selected, the date in cell B6 should change to 01/31/2021. Similarly when button "Next Month" is selected, the date in cell B6 should change to 03/31/2021.
I don't how this can be achieved but it is of utmost important to achieve this. Any similar idea/s is/are also welcomed.
Thanks.
I did the following:
- I right-clicked the Previous Month button and selected Assign Macro... from the context menu.
- I entered PreviousMonth in the Macro name box (a macro name cannot contain spaces), then clicked New.
- This took me to the Visual Basic Editor, with the first and last lines already created. It looked like this:
Sub PrevMonth() End Sub- I typed the code into the line in between. It takes the value of cell B6, and used the EOMONTH function to store the last day of the previous month in B6:
Sub PrevMonth() Range("B6").Value = Application.EoMonth(Range("B6").Value, -1) End Sub- I switched back to Excel, then did basically the same for the Next Month button, but with a different line of code, of course.
- Finally, I pressed F12 from the workbook to display the Save As dialog.
- I selected Excel Macro-enabled Workbook (*.xlsm) from the Save as type dropdown, then clicked Save.
5 Replies
This can be done with a couple of macros. See the attached version - now a .xlsm workbook.
Make sure that you allow macros when you you open the workbook.
P.S. The value in cell B6 was a text value, I converted it to a 'real' date.
HansVogelaar Thanks a lot for the solution. yes, it works exactly as I wanted.
But it would be great if you can specify each step by step as I need to have multiple similar macros to be enabled in other workbooks too.
I did the following:
- I right-clicked the Previous Month button and selected Assign Macro... from the context menu.
- I entered PreviousMonth in the Macro name box (a macro name cannot contain spaces), then clicked New.
- This took me to the Visual Basic Editor, with the first and last lines already created. It looked like this:
Sub PrevMonth() End Sub- I typed the code into the line in between. It takes the value of cell B6, and used the EOMONTH function to store the last day of the previous month in B6:
Sub PrevMonth() Range("B6").Value = Application.EoMonth(Range("B6").Value, -1) End Sub- I switched back to Excel, then did basically the same for the Next Month button, but with a different line of code, of course.
- Finally, I pressed F12 from the workbook to display the Save As dialog.
- I selected Excel Macro-enabled Workbook (*.xlsm) from the Save as type dropdown, then clicked Save.
