Entering Dates by typing the month number only

[drgkt]

Hi

In a cell formatted as date (month-year) is it possible to:

i.e enter "12":

FORMULA BAR shows: Current Year-December-01 (since day not specified).
The CELL shows: Dec-Current year (2 digit is fine).

Thanks

[Richard Buttrey]

Hi

You could only do that in the same cell with a Sheet Change macro which personally I think would be OTT

Why not just enter the month number in a cell, say A1 and alongside in say B1 use the formula

=DATE(YEAR(TODAY()),A1,1)

and Custom format B2 to "mmm yy"

[drgkt]

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...?

[kev_]

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

Private Sub Worksheet_Change(ByVal Target As Range)
    If Selection.Count > 1 Then Exit Sub
    Dim myMonth As Integer
    
    'ranges to be converted from "number" to "Month-year"
    Set myrange = Union(Range("B2:B20"), Range("E2:E20"), Range("F2:F20"))

    If Not Intersect(Target, myrange) Is Nothing Then
        On Error Resume Next
        myMonth = Target.Value
        If Err.Number = 13 Then Err.Clear: Exit Sub
        If myMonth > 12 Or myMonth < 1 Then Exit Sub
        
        
        Application.EnableEvents = False 'allows VBA to write a new value to the cell
        Select Case Target.Column
            Case 2 'Column B (= 2nd column)is current year
            Target.Value = MonthName(myMonth, True) & "-" & Year(Date)
            Case 5 'Column E (= 5th column)is next year
            Target.Value = MonthName(myMonth, True) & "-" & Year(Date) + 1
            Case 6 'Column F (= 6th column)is prior year
            Target.Value = MonthName(myMonth, True) & "-" & Year(Date) - 1
        End Select
        Application.EnableEvents = True

    End If

End Sub

[thinhvd]

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

[drgkt]

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

This is what I have...

[thinhvd]

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.

[drgkt]

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...)

[kev_]

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

Private Sub Worksheet_Change(ByVal Target As Range)
    If Selection.Count > 1 Then Exit Sub
    If Target.Row < 2 Then Exit Sub
    Dim myMonth As Integer
    
    'range to be converted from "number" to "Month-year"
    Set myrange = Columns("A:A")

    If Not Intersect(Target, myrange) Is Nothing Then
        On Error Resume Next
        myMonth = Target.Value
        If Err.Number = 13 Then Err.Clear: Exit Sub
        If myMonth > 12 Or myMonth < 1 Then Exit Sub
        
        Application.EnableEvents = False 'allows VBA to write a new value to the cell
            Target.Value = MonthName(myMonth, True) & "-" & Year(Date)
        Application.EnableEvents = True

    End If

End Sub

[drgkt]

Thanks!

I guess NO REMs in VBA?

[kev_]

You are welcome

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

Rem My wise words
' My wise words

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

[drgkt]

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?

[kev_]

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

Private Sub CommandButton1_Click()
  Application.EnableEvents = Not Application.EnableEvents

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

If Range("A1") = "OFF" Then Exit Sub

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