Need VBA code to prevent printing IF

[bootdat]

How can i let my macro not run and give a message if a user forgets to put in quantity beside a row that is filled with an item ?

POSv2.xlsm
	C	D	E	F	G	H
16	Item #	Qty	Description	Unit Price	Discount	Line Total
17	1	1	Fried ring donuts (12 Pieces)	30		GH₵30.00
18	2	1	Baked glazed donuts	40		GH₵40.00
19	3	1	Chinchin (Olonka)	60		GH₵60.00
20	4	1	6 Pieces Cupcake	55		GH₵55.00
21	5		12 Pieces Cupcake	110
22	6	1	24 Pieces Cupcake	215		GH₵215.00
23	7	1	6 Pieces Cupcake (More Toppings)	60		GH₵60.00
24	8	1	12 Pieces Cupcake (More Toppings)	120		GH₵120.00
25	9	1	24 Pieces Cupcake (More Toppings)	230		GH₵230.00
26	10	1	Custom BIBLE Cake	250		GH₵250.00
27	11	1	Sparkling Candle	10		GH₵10.00
28	12	1	birthday topper	10		GH₵10.00
29	13		Promo cake	150
30	14	1	PAPER PRINT	20		GH₵20.00
31	15	1	3 DAYS WHIPPING CREAM CLASS	600		GH₵600.00
32	16	1	1 MONTH BEGINNERS CLASS	1200		GH₵1,200.00
33	17	1	2 WEEKS INTERMEDIATE CLASS	1000		GH₵1,000.00
34	18	1	2 TIER WEDDING CAKE 6 and 10	700		GH₵700.00
35
36
37
38
Invoice

Cell Formulas
Range	Formula
C17:C38	C17	=IF(B17="","", IF(B17>0,SUBTOTAL(103,$B$17:B17),""))
F17:F38	F17	=IF(E17="","",VLOOKUP(E17,itemlist,2,FALSE))
H17:H38	H17	=IF(SUM(D17)>0,SUM((D17*F17)-G17),"")

Named Ranges
Name	Refers To	Cells
itemlist	=Table2[[#All],[Item Name]:[Price]]	F17:F38

 

[Anthony47]

Try this macro, to be copied within the vba module "ThisWorkbook":

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim myC As Range
'
    For Each myC In Sheets("Sheet1").Range("D17:D40")         '<<<<<
        If myC.Value = "" And myC.Offset(0, 1).Value <> "" Or _
           myC.Value <> 0 And myC.Offset(0, 1).Value = "" Then
            MsgBox ("Please fill Description & Quantity")
            myC.Select
            Cancel = True
            Exit Sub
        End If
    Next myC
End Sub

It will check that any description have a quantity and viceversa (each qty corresponds to a description)
Check the range set in the line marked with <<<<, it should reflect the height of the table to be printed; I dont know if you have additional information at the bottom of the table, so I didn't try to find the last used line in columns D & E

Note that if what you have to print is a structured Table that it would not be a problem to identify the lenght of the columns, because vba can address any of them as ListColumns.DataBodyRange. I mean that if you use a structured table, and it is named Table1, and Item # is column 1, then the macro to use would be

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim myC As Range
'
    For Each myC In Sheets("Sheet1").ListObjects("Table1").ListColumns(2).DataBodyRange   '<<<<
        If myC.Value = "" And myC.Offset(0, 1).Value <> "" Or _
           myC.Value <> 0 And myC.Offset(0, 1).Value = "" Then
            MsgBox ("Please fill Description & Quantity")
            myC.Select
            Cancel = True
            Exit Sub
        End If
    Next myC
End Sub

The line marked <<< will automatically addrress the whole second column (Qty) whichever is its height.

Bye

 

[bootdat]

Try this macro, to be copied within the vba module "ThisWorkbook":

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim myC As Range
'
    For Each myC In Sheets("Sheet1").Range("D17:D40")         '<<<<<
        If myC.Value = "" And myC.Offset(0, 1).Value <> "" Or _
           myC.Value <> 0 And myC.Offset(0, 1).Value = "" Then
            MsgBox ("Please fill Description & Quantity")
            myC.Select
            Cancel = True
            Exit Sub
        End If
    Next myC
End Sub

It will check that any description have a quantity and viceversa (each qty corresponds to a description)
Check the range set in the line marked with <<<<, it should reflect the height of the table to be printed; I dont know if you have additional information at the bottom of the table, so I didn't try to find the last used line in columns D & E

Note that if what you have to print is a structured Table that it would not be a problem to identify the lenght of the columns, because vba can address any of them as ListColumns.DataBodyRange. I mean that if you use a structured table, and it is named Table1, and Item # is column 1, then the macro to use would be

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim myC As Range
'
    For Each myC In Sheets("Sheet1").ListObjects("Table1").ListColumns(2).DataBodyRange   '<<<<
        If myC.Value = "" And myC.Offset(0, 1).Value <> "" Or _
           myC.Value <> 0 And myC.Offset(0, 1).Value = "" Then
            MsgBox ("Please fill Description & Quantity")
            myC.Select
            Cancel = True
            Exit Sub
        End If
    Next myC
End Sub

The line marked <<< will automatically addrress the whole second column (Qty) whichever is its height.

Bye

Oh yes !!

my table is a structured table so i used the second option and it worked perfectly. just as i wanted it.
i even like the fact that it check that any description have a quantity and viceversa
Thank you very much.