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

%3CLINGO-SUB%20id%3D%22lingo-sub-1981377%22%20slang%3D%22en-US%22%3EGet%20date%20using%20InputBox%20in%20VBA%20macro%20gives%20%22Run-time%20error%20'6'%20-%20Overflow%22%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1981377%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20trying%20to%20read%20in%20a%20start%20date%20for%20a%20macro%20in%20Office%20365%20Excel%20for%20Mac%20v%2016.41%20on%20OS%20X%2010.15.7%20and%20am%20getting%20an%20message%20%22Run-time%20error%20'6'%20-%20Overflow%22%20after%20clicking%20OK%20in%20the%20InputBox.%20%26nbsp%3BThe%20macro%20has%20worked%20fine%20the%20past%20couple%20of%20years%2C%20but%20now%20returns%20this%20error.%20%26nbsp%3BI%20have%20not%20run%20it%20for%20a%20couple%20of%20months%2C%20so%20something%20in%20an%20Excel%20or%20OS%20X%20update%20must%20have%20changed%20behavior.%20%26nbsp%3BHere%20are%20a%20few%20lines%20that%20are%20enough%20to%20demonstrate%20the%20error.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESub%20GetDateFromDialog()%3C%2FP%3E%3CP%3E%26nbsp%3B%20Dim%20StartDate%20As%20Date%3C%2FP%3E%3CP%3E%26nbsp%3B%20StartDate%20%3D%20InputBox(%22What%20is%20the%20Start%20Date%3F%22%2C%20%22Start%20Date%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%20ActiveSheet.Range(%22B3%22).Value%20%3D%20StartDate%3C%2FP%3E%3CP%3Eend%20sub%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20fails%20with%20the%20error%20before%20reaching%20the%20ActiveSheet.Range()%20command.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20do%20I%20get%20this%20working%20again%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1981377%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1981544%22%20slang%3D%22en-US%22%3ERe%3A%20Get%20date%20using%20InputBox%20in%20VBA%20macro%20gives%20%22Run-time%20error%20'6'%20-%20Overflow%22%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1981544%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F899422%22%20target%3D%22_blank%22%3E%40LandyA%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETry%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EStartDate%20%3D%20CDate(InputBox(%22What%20is%20the%20Start%20Date%3F%22%2C%20%22Start%20Date%22))%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Eor%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EStartDate%20%3D%20DateValue(InputBox(%22What%20is%20the%20Start%20Date%3F%22%2C%20%22Start%20Date%22))%3C%2FP%3E%3C%2FLINGO-BODY%3E
New 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