Get date using InputBox in VBA macro gives "Run-time error '6' - Overflow"

Copper Contributor

I am trying to read in a start date for a macro in Office 365 Excel for Mac v 16.41 on OS X 10.15.7 and am getting an message "Run-time error '6' - Overflow" after clicking OK in the InputBox.  The macro has worked fine the past couple of years, but now returns this error.  I have not run it for a couple of months, so something in an Excel or OS X update must have changed behavior.  Here are a few lines that are enough to demonstrate the error.

 

Sub GetDateFromDialog()

  Dim StartDate As Date

  StartDate = InputBox("What is the Start Date?", "Start Date")

  ActiveSheet.Range("B3").Value = StartDate

end sub

 

It fails with the error before reaching the ActiveSheet.Range() command.

 

How do I get this working again?

 

2 Replies

@LandyA 

Try

 

StartDate = CDate(InputBox("What is the Start Date?", "Start Date"))

 

or

 

StartDate = DateValue(InputBox("What is the Start Date?", "Start Date"))

@Hans Vogelaar 

 

The version with DateValue() worked.  Not sure what has changed, but you got my macros working again.  Thanks for the help.

 

-LandyA