Need VBA code to prevent printing IF

bootdat

New Member
Joined
Nov 22, 2018
Messages
44
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
CDEFGH
16Item #QtyDescriptionUnit PriceDiscountLine Total
1711Fried ring donuts (12 Pieces)30GH₵30.00
1821Baked glazed donuts40GH₵40.00
1931Chinchin (Olonka)60GH₵60.00
20416 Pieces Cupcake55GH₵55.00
21512 Pieces Cupcake110 
226124 Pieces Cupcake215GH₵215.00
23716 Pieces Cupcake (More Toppings)60GH₵60.00
248112 Pieces Cupcake (More Toppings)120GH₵120.00
259124 Pieces Cupcake (More Toppings)230GH₵230.00
26101Custom BIBLE Cake250GH₵250.00
27111Sparkling Candle10GH₵10.00
28121birthday topper10GH₵10.00
2913Promo cake150 
30141PAPER PRINT20GH₵20.00
311513 DAYS WHIPPING CREAM CLASS600GH₵600.00
321611 MONTH BEGINNERS CLASS1200GH₵1,200.00
331712 WEEKS INTERMEDIATE CLASS1000GH₵1,000.00
341812 TIER WEDDING CAKE 6 and 10700GH₵700.00
35   
36   
37   
38   
Invoice
Cell Formulas
RangeFormula
C17:C38C17=IF(B17="","", IF(B17>0,SUBTOTAL(103,$B$17:B17),""))
F17:F38F17=IF(E17="","",VLOOKUP(E17,itemlist,2,FALSE))
H17:H38H17=IF(SUM(D17)>0,SUM((D17*F17)-G17),"")
Named Ranges
NameRefers ToCells
itemlist=Table2[[#All],[Item Name]:[Price]]F17:F38
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
2,147
Try this macro, to be copied within the vba module "ThisWorkbook":
VBA Code:
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
VBA Code:
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
 
Solution

bootdat

New Member
Joined
Nov 22, 2018
Messages
44
Try this macro, to be copied within the vba module "ThisWorkbook":
VBA Code:
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
VBA Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,177
Messages
5,640,620
Members
417,158
Latest member
jimmy1986

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