Entering Dates by typing the month number only

I was looking not to add another column...
How will the macro work,
enter number, execute macro, go to next cell enter number, execute macro...?

Try in attached file first and then adapt this to suit your requirements
To illustrate, 3 ranges are automatically changed from month number to "month-year"
B2:B20 =2017 , E2:E20 = 2018, F2:F20 = 2016


Place VBA in the relevant sheet module

Please Login or Register  to view this content.

Using Add-In A-Tools to view calendar (click to input date)
2017-01-03 13_10_09-Book1 - Excel.png
2017-01-03 13_10_32-Book1 - Excel.png

That's great Kev_!

Suppose the target range is the entire column A and the year is current year. How do I modify the code?

In VBA can things be "rem"ed out like in batch files? (Easier to modify the code by moving rems around instead of deleting and rewriting...)

Originally Posted by thinhvd

Using Add-In A-Tools to view calendar (click to input date)

This is what I have...

Try this
All entries in Column A (below row 1) amended from month number to "month-2017"

Please Login or Register  to view this content.

Thanks!

I guess NO REMs in VBA?

You are welcome

In vba, these 2 lines are the same. The apostrophe is a shortcut for Rem

Please Login or Register  to view this content.

If you are happy with your solution, please go to Thread Tools (top of thread) and mark the thread as solved.
thanks

One more question.
Is there a place in VBA Editor where I can store this as inactive and then drop it into a sheet when I need it?

There are a few ways to disable the macro.

One way
Add a button on the worksheet with this code behind it - it toggles events ON and OFF

Please Login or Register  to view this content.

Note that this enables/disables ALL event macros

Another way
(I would elect for this)
Add this as the first line of code in Private Sub Worksheet_Change

Please Login or Register  to view this content.

If OFF is entered in cell A1 (or your chosen cell) the macro exits without doing anything
You could have a choice of OFF and ON in the cell (using a dropdown)

Yet another way
(a bit of a "back of fag-packet" solution!)
Rename the macro with an X in front of the name
so "Private Sub Worksheet_Change.." , become "XPrivate Sub Worksheet_Change..."
and then it won't run

You must download Add-in A-Tools
Note: Close all office application before installing
Installation finish and open excel files. Any cells which is formated date can view as picture.