How do I create an error message?

[rabarnes]

Hi

I have created an order form which my sales team use to print out internal production orders. The workbook comprises of an input sheet which the sales team fill in, and an order printout which is on a seperate sheet. My issue is that the sales team may miss one of the boxes on the form and print the sheet out not knowing they have missed it. I would like the option of an error message when the "print" button is pressed to instruct the saleperson that they have missed an essential box, for example, "delivery date".

This is my code at present:

Private Sub CommandButton2_Click()

Sheets("Order").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Sheets("Input Sheet").Select
Range("C5").Select

End Sub

Can anyone help?
Apologies if this is fairly simple, I'm fairly new to VB.

Thanks

Rowan

 

[Norie]

Rowan

What exactly fo you mean by a 'box'?

A cell?

A textbox?

 

[rabarnes]

a cell!

Apologies like i said i'm new to all this!

I mean a cell. There are various cells which need to be filled in i.e. delivery date, delivery address, customer contact name, quote number etc.

Some cells are essential and therefore must be filled in, some are optional.

I need an error message should one of the essential boxes be missed.

Thanks

Rowan

 

[Smitty]

Something like this maybe:

<font face=Tahoma><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> CommandButton2_Click()
    <SPAN style="color:#00007F">Dim</SPAN> c <SPAN style="color:#00007F">As</SPAN> Range
        <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> Range("A1:A5")
            <SPAN style="color:#00007F">If</SPAN> c = "" <SPAN style="color:#00007F">Then</SPAN>
                MsgBox "Cell " & c.Address & " must be filled out!", vbOKOnly + vbCritical, "Missing Entry"
                c.Select
                <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
        <SPAN style="color:#00007F">Next</SPAN> c
                
    Sheets("Order").PrintOut Copies:=1, Collate:=<SPAN style="color:#00007F">True</SPAN>
    
    Sheets("Input Sheet").Range("C5").Select

<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

Change the range to suit.

HTH,

Smitty

 

[rabarnes]

more complex...?

Thanks Smitty

This is perfect.

I require a slight 'enhancement' to this code though: how do I control how critical a cell is based upon the value of another cell?

For example, if the value in cell A1=0, then cell C5 does not need to be filled in and therefore my error msg box does not appear. However if cell A1=1, then cell C5 is critical and must be filled out, and if left blank my error msg box will appear.

Hope this makes sense?!

Cheers

Rowan

 

[Smitty]

Just adjust your logic as in your question.

I.E.

If Range("A1").Value = 1 Then
  MsgBox "Cell C5 must be filled out!"
  Range("C5").Activate
  Exit Sub
End If

Smitty