Trouble using isdate and checking if cancel pressed.

DragonRyda

New Member
Joined
Sep 19, 2016
Messages
7
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.
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Trebor76

Well-known Member
Joined
Jul 23, 2007
Messages
4,735
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:

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
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

New Member
Joined
Sep 19, 2016
Messages
7
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:

Code:
  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

Well-known Member
Joined
Jul 23, 2007
Messages
4,735
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,975
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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...
Code:
 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
 
Last edited:

DragonRyda

New Member
Joined
Sep 19, 2016
Messages
7
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

New Member
Joined
Sep 19, 2016
Messages
7
Thank you all :) I worked out:
BD = DateValue(BD)

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

Watch MrExcel Video

Forum statistics

Threads
1,129,580
Messages
5,637,211
Members
416,961
Latest member
sigrid6940

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top