macro to force use to enter valid month and year

[howard]

I have the following macro below that forces the user to input a Valid month and year in cell A3


When running the macro, the month and year is not inserted

It would be appreciated if someone could kindly assist me


I have posted a similar question on link below

Incorporate Data Validation in Macro to enter Date

I have the following code below , which allow the user to enter a date in A3 on sheet "WML" I have set up data validation to only accept dates from Aug 2020 to Dec 2099 However, when using the Macro to input the Data, it accepts any data The data validation to be incorporated into...

www.excelforum.com

 Sub DateInput()
Dim strDate As String
Dim myDate As String

Sheets("WML").Select
If Range("A3").Value > Now() Then
Exit Sub
Else
Range("A3").Select

  strDate = InputBox("enter the accounting Month and Year eg Aug 2020", "mm/yyyyy")
If IsDate(strDate) Then

strDate = Format(CDate(strDate), "mm/yyyy")
MsgBox strDate
Else
MsgBox "Wrong date format"
End If


  End If

End Sub [code]

 

[Norie]

Where should the date be 'inserted'?

 

[howard]

My Apologies. In cell A3 on Sheet "WML"

 

[drsarao]

Try this:

Sub DateInput()
Dim strDate As String
Dim myDate As String

Sheets("WML").Select
If Range("A3").Value > Now() Then
    Exit Sub
Else
    'Range("A3").Select
    strDate = InputBox("enter the accounting Month and Year eg Aug 2020", "mm/yyyyy")
    If IsDate(strDate) Then
    Range("A3") = Format(CDate(strDate), "'mm/yyyy") 'input to cell here
    'MsgBox strDate
    Else
        MsgBox "Wrong date format"
    End If
End If
End Sub

Note the leading single apostrophe in "'mm/yyyy". This is required to prevent Excel from auto converting it to a date 01/mm/yyyy.
Remove the apostrophe if that is what (date) you want.

 

[howard]

thanks for the help. Your code works perfectly

 

[drsarao]

Glad to help!