msgbox (vbokcancel)

[RichieP]

In a vbokcancel msgbox, how do I get the cancel button to stop running a sub (i presume exitsub) and the ok button to run a macro. This is what I have so far . . . .

MsgBox "This will bring back details by MCC between the dates " & Forms!Frmmain!txtDate1 & " and " & Forms!Frmmain!txtDate2 & ".", vbOKCancel, "Sorted by date only."
If vbokCancel = 1 Then Exit Sub Else
If vbOKcancel = 2 Then DoCmd.RunMacro "Mcr_Frmmain to by MCC"
Exit Sub
End If

I read somewhere that the return values are: 1=OK 2=Cancel. Is this right? Any one have an idea?

Thanks.

 

[RichieP]

sorry swap the 1 and 2 around, it should be:

If vbokCancel = 2 Then Exit Sub Else
If vbOKcancel = 1 Then DoCmd.RunMacro "Mcr_Frmmain to by MCC"

Still doesn't work though!

 

[Mile-O]

2 ways:

i) The direct method

If MsgBox("This will bring back details by MCC between the dates " & Forms!Frmmain!txtDate1 & " and " & Forms!Frmmain!txtDate2 & ".", vbOKCancel, "Sorted by date only.") = vbOk Then
    Exit Sub
Else
    DoCmd.RunMacro "Mcr_Frmmain to by MCC"
    Exit Sub
End If

ii) The indirect method

Dim varResponse As Variant

varResponse = MsgBox("This will bring back details by MCC between the dates " & Forms!Frmmain!txtDate1 & " and " & Forms!Frmmain!txtDate2 & ".", vbOKCancel, "Sorted by date only.") 

If varResponse = vbOk Then
    Exit Sub
Else
    DoCmd.RunMacro "Mcr_Frmmain to by MCC"
    Exit Sub
End If

 

[RichieP]

The direct method worked a treat! Thanks.
And thank's for the speedy (3 minute) reply, must be a record!

 

[Mile-O]

Na, one minute must be!

 

[Oldsoftboss]

The reason for the quick response from Mile-O-Pile is that he has dedicated himself to this forum. We passed 500 posts at about the same time a couple of months ago. Now look at the difference
Dave

 

[Hayley Baxter]

Guess you wont be getting that bonus you were telling us about then Mile - You missed your deadline..shame

 

[Mile-O]

Guess you wont be getting that bonus you were telling us about then Mile

Spent it...

 

[Hayley Baxter]

How did you find the time to spend it?

 

[Mile-O]