macro to force use to enter valid month and year

howard

Well-known Member
Joined
Jun 26, 2006
Messages
5,701
Office Version
  1. 2019
Platform
  1. Windows
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





Code:
 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]
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,233
Office Version
  1. 365
Platform
  1. Windows
Where should the date be 'inserted'?
 

howard

Well-known Member
Joined
Jun 26, 2006
Messages
5,701
Office Version
  1. 2019
Platform
  1. Windows
My Apologies. In cell A3 on Sheet "WML"
 

drsarao

Well-known Member
Joined
Sep 9, 2009
Messages
1,143
Office Version
  1. 2007
Platform
  1. Windows
Try this:

VBA Code:
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

Well-known Member
Joined
Jun 26, 2006
Messages
5,701
Office Version
  1. 2019
Platform
  1. Windows
thanks for the help. Your code works perfectly
 

Watch MrExcel Video

Forum statistics

Threads
1,123,422
Messages
5,601,570
Members
414,458
Latest member
mekhallet salah

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top