Message Box, vbYesNo question

[OleMiss2010]

One part of a sub I am writing is to ask if a date will be filled in. If it will be, then I want an input box to pop up that will allow the user to input the date. If they will not be using the date field I want the sub to clear any content that may be in that field and move on. Here is what I have:

[pre]

Dim DraftDate as Date

MsgBox "Do you wish to enter a draft date for this review?", vbYesNo
If MsgBox("Do you wish to enter a draft date for this review?") = vbYes Then
Do
DraftDate = Application.InputBox("What is the draft date for this review?")
Range("D7").Value = DraftDate
Loop While DraftDate = 0
End If
If MsgBox("Do you wish to enter a draft date for this review?") = vbNo Then
Range("D7").Select
Selection.ClearContents
End If

[/pre]

As long as the user selects "Yes" from the the message box it works perfectly. However, a "No" leads to two back-to-back vbokonly message boxes with the same message as the vbyesno message and the cells is not cleared.


Any advice?

 

[Mike86]

Maybe look into putting an "Else" followed by the No condition code just before the first "EndIF". You can then delete the second if statement.

 

[OleMiss2010]

Adding the else did get rid of one of the "Ok" boxes and also cleared the cells correctly. I have no idea what is causing the ok box that still pops up. That makes no sense.


Thanks for the help, at least it works properly (just with an extra box).

 

[Luke M]

Use a variable to store the response from the MsgBox, rather than asking the question twice:

[pre]

UserResponse = MsgBox("Do you wish to enter a draft date for this review?", vbYesNo)
If UserResponse = vbYes Then
Do
DraftDate = Application.InputBox("What is the draft date for this review?")
Range("D7").Value = DraftDate
Loop While DraftDate = 0
Else
Range("D7").ClearContents
End If

[/pre]