Trouble using isdate and checking if cancel pressed.

[DragonRyda]

Hello all,
I am a definite newby to all this, so please be gentle with me! I haven't done programming really since back on the Commodore 64. Some of this seems intuitive, some just does my head in.

I have the following code:


Sub GetDate()


'set colours for pass/fail validations
ok = 42
notok = 46
'
' Input box ensuring advanced find is open and obtaining batch date


On Error Resume Next
BD = InputBox("Please ensure you have already opened the Advanced Find, found under 'Claim Items'. Then, please enter the batch date you are working on in 'dd/mm'yyyy'format.")
If IsDate(BD) = True Then Macro1
Else: If BD = "" Then Exit Sub
Else: If IsDate(BD) = False Then
MsgBox ("Please enter the date as dd/mm/yyyy")

Call GetDate
End If
Exit Sub
End Sub

What i am trying to achieve is to get the batch date from the user. If it's a date, go to Macro 1. If cancel was entered, then stop programme. If something other than date was entered, tell them to enter date correctly, and restart. I feel like I must be close. I have tried numerous versions, but no success. I have DIM BD as Date. Any help would be gratefully appreciated.

 

[Trebor76]

Hi DragonRyda,

Welcome to MrExcel!!

Though you say you've declared BD as variable I don't see it?? In any case this should do what you need:

Sub GetDate()

 'set colours for pass/fail validations
 ok = 42
 notok = 46
 '
 ' Input box ensuring advanced find is open and obtaining batch date

 'On Error Resume Next
GetDate:
    BD = InputBox("Please ensure you have already opened the Advanced Find, found under 'Claim Items'. Then, please enter the batch date you are working on in 'dd/mm/yyyy' format.")
    If BD = "" Then
        Exit Sub
    ElseIf IsDate(BD) = True Then
        Macro1
    ElseIf IsDate(BD) = False Then
        MsgBox "Please enter the date as dd/mm/yyyy"
        GoTo GetDate
    End If
    
End Sub

Regards,

Robert

 

[DragonRyda]

Wow, thanks heaps Robert. That is getting me so close I can smell it! I will need to study it and get my head around it. No point in just copy/paste, or I'll never leave this site.

I have definitely done something wrong with my variable though. Your code works sublimely. Later on, I use the date as, well, a date.


I get to this bit:

  If BD - ActiveSheet.Range("f" & a).Value > 30 Then
       ActiveSheet.Range("o" & a) = "No"
       ActiveSheet.Range("o" & a).Interior.ColorIndex = notok
       ActiveSheet.Range("q" & a) = "Yes"
    Else
       ActiveSheet.Range("o" & a) = "Yes"
       ActiveSheet.Range("o" & a).Interior.ColorIndex = ok
    End If

This checks to see if BD is more than 30 days from the end of a period (date in F column).

I promise I am not trying to get others to do my programming for me, by the way. I don't know if that's an issue here. I want to learn, not just get it done.

thanks for all help.

 

[Trebor76]

Your code works sublimely

I'm not sure if you're still needing help or not but thanks for the feedback and you're welcome

Regards,

Robert

 

[Rick Rothstein]

Here is a generalized structure you can use for the InputBox that will allow you to react to the user clicking the Cancel button or clicking Enter without filling in anything as well as when the user enters actual data...

 Dim Answer As String
 '....
 '....
 Answer = InputBox("Tell me something")
 If StrPtr(Answer) = 0 Then
   MsgBox "The user clicked Cancel, so we will exit the subroutine now."
   Exit Sub
 ElseIf Len(Answer) = 0 Then
   MsgBox "The user clicked OK without entering anything in the InputBox!"
 Else
   MsgBox "The user entered the following..." & vbLf & vbLf & Answer
 End If

 

[DragonRyda]

Just a tiny bit more help if possible please.

I have BD as a date. I then try to use it in a calculation, but I need to work out how to change if from date format back into a single number. I have the feeling that this is both ridiculously easy and obvious, but am not seeing the wood for the trees. If i declare BD to be a number, it fails the first date bit. If i declare it as a date, it passes the first bit, but fails the basic arithmetic later.

 

[DragonRyda]

Thank you all I worked out:
BD = DateValue(BD)

So easy. I shall kick myself on the way out. Cheers all