How do I create an error message?

rabarnes

New Member
Joined
Sep 7, 2006
Messages
26
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
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
Rowan

What exactly fo you mean by a 'box'?

A cell?

A textbox?
 

rabarnes

New Member
Joined
Sep 7, 2006
Messages
26
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

Legend
Joined
May 15, 2003
Messages
29,536
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

New Member
Joined
Sep 7, 2006
Messages
26
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

Legend
Joined
May 15, 2003
Messages
29,536
Just adjust your logic as in your question.

I.E.

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

Smitty
 

Forum statistics

Threads
1,137,337
Messages
5,680,900
Members
419,937
Latest member
Talic

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