Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 04-03-2022, 03:14 AM
CuriousG CuriousG is offline Error handling input to a Date variable Windows 10 Error handling input to a Date variable Office 2016
Novice
Error handling input to a Date variable
 
Join Date: Jan 2021
Posts: 20
CuriousG is on a distinguished road
Default 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.
Reply With Quote
  #2  
Old 04-04-2022, 07:13 AM
Debaser's Avatar
Debaser Debaser is offline Error handling input to a Date variable Windows 7 64bit Error handling input to a Date variable Office 2010 32bit
Competent Performer
 
Join Date: Oct 2015
Location: UK
Posts: 221
Debaser will become famous soon enough
Default

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
You could also put the validation in a loop (with a counter so it's not infinite) so that it prompts to try again if an invalid date is entered.
Reply With Quote
  #3  
Old 04-05-2022, 08:51 AM
CuriousG CuriousG is offline Error handling input to a Date variable Windows 10 Error handling input to a Date variable Office 2016
Novice
Error handling input to a Date variable
 
Join Date: Jan 2021
Posts: 20
CuriousG is on a distinguished road
Default

That works perfectly, thank you so much.

And kind regards

Graham
Reply With Quote
Reply

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
Error handling input to a Date variable 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
Error handling input to a Date variable Run-time error '91': Object variable or With block variable not set tinfanide Excel Programming 2 06-10-2012 10:17 AM
Error handling input to a Date variable Saveas error handling jillapass Word VBA 7 03-30-2012 03:24 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 12:05 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft