#1
|
|||
|
|||
Error handling input to a Date variable
I have the following statements in my code:
DueDate = InputBox("Enter the date the payment is due in format dd/mm/yyyy") Do Until DateValue = DueDate Charge = Charge + 1 RunningTotal = RunningTotal + Charge DateValue = DateValue + 1 Loop It performs as expected when an appropriate value is assigned to the variable DueDate but fails under the following circumstances: 1. No value is entered in InputBox and OK is pressed 2. No value is entered in InputBox and Cancel is pressed 3. No value is entered in InputBox and the box is closed by clicking on the X In each of the above cases the value 00:00:00 is assigned to the variable DueDate. There is also some unexpected outcome if the value entered in the variable DueDate precedes that held in DateValue. Whilst I sort of understand (a little bit) what's going on here, I can't figure out how to construct the appropriate error handling statements to cater for these 4 fail scenarios. Grateful for any advice please. |
#2
|
||||
|
||||
I would handle the first three separately with a temp variable:
Code:
tempInput = InputBox("Enter the date the payment is due in format dd/mm/yyyy") If tempinput = "" then exit sub on error resume next DueDate = CDate(tempinput) on error goto 0 if duedate = 0 then msgbox "Invalid date entered - please try again" exit sub end if |
#3
|
|||
|
|||
That works perfectly, thank you so much.
And kind regards Graham |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Run Time Error '91': Object variable or With block variable not set using Catalogue Mailmerge | Berryblue | Mail Merge | 1 | 11-13-2014 05:36 PM |
Run-time error 91 object variable or with block variable not set | JUST ME | Word VBA | 4 | 03-25-2014 06:56 AM |
Error handling in formula - suppress 'Syntax Error' | Cosmo | Word | 7 | 02-24-2014 07:08 AM |
Run-time error '91': Object variable or With block variable not set | tinfanide | Excel Programming | 2 | 06-10-2012 10:17 AM |
Saveas error handling | jillapass | Word VBA | 7 | 03-30-2012 03:24 PM |