Excel / macro

Question

QUESTION: I have the following very simple macro to submit a form -- works fine, but I want to run a test on cell K3. If the value of K3 is 0, would like to display a message in A45, "Payment method to be selected prior to sending form" and exit the macro. If K3 is greater than 0, then the email would be sent.

Sub Submit() ' ' Submit Macro ' Macro recorded 2/03/2010 by name

    Receipt = True 'False
s1$ = ""
s2$ = "Form"
Application.Dialogs(xlDialogSendMail).Show arg1:=s1$, arg2:=s2$, arg3:=Receipt

End Sub

Note, I found the above macro on your website, which is absolutely fantastic.

Thank you for your help

Pauline

ANSWER: I guess you could just have a simple formula in cell A45 and leave the macro unchanged:

=IF(K3=0,"Payment method to be selected prior to sending form","")

---------- FOLLOW-UP ----------

QUESTION: Have that currently, but I want to stop the form being emailed if they haven't completed a certain section of the form, which is controlled by option buttons and the test (via the macro) would not allow the form to be emailed until a selection has been made, but also to display why it's not being emailed.

Plus, thank you for the very quick reply

Pauline

Answer

Change your macro to:

Sub Submit() ' ' Submit Macro ' Macro recorded 2/03/2010 by name

   If Len(CStr(Range("K3").Value)) = 0 Then
       'K3 is empty, show message and exit
       MsgBox "Payment method to be selected prior to sending form", vbOKOnly + vbInformation
       Exit Sub
   ElseIf Range("K3").Value = 0 Then
       'K3 contains a zero, show message and exit
       MsgBox "Payment method to be selected prior to sending form", vbOKOnly + vbInformation
       Exit Sub
   Else
       Receipt = True    'False
       s1$ = ""
       s2$ = "Form"
       Application.Dialogs(xlDialogSendMail).Show arg1:=s1$, arg2:=s2$, arg3:=Receipt
   End If

End Sub