Run a VBA Code before printing in excel

[dal3gacy]

Hi to all. I am using a VBA code to hide all the lines that have 0 value in column 2. The trick is to run this VBA code when someone wants to print the worksheet. Would like to do this automatically so the code runs before worksheet is being printed instead of running VBA code manually and then print, this way no one could mistake of printing before running the VBA code. The VBA code i am using is as follows:
Sub zero()
BeginRow = 7
EndRow = 274
ChkCol = 2

For RowCnt = BeginRow To EndRow
If Cells(RowCnt, ChkCol).Value <> 0 Then
Cells(RowCnt, ChkCol).EntireRow.Hidden = False
Else
Cells(RowCnt, ChkCol).EntireRow.Hidden = True
End If
Next RowCnt
End Sub

Does someone know a solution ?
Thal you

 

[Stiuart_W]

Yes

Put your code in the Workbook "BeforePrint" event

Private Sub Workbook_BeforePrint(Cancel As Boolean)
    BeginRow = 7
    EndRow = 274
    ChkCol = 2
    For RowCnt = BeginRow To EndRow
        If Cells(RowCnt, ChkCol).Value <> 0 Then
            Cells(RowCnt, ChkCol).EntireRow.Hidden = False
        Else
            Cells(RowCnt, ChkCol).EntireRow.Hidden = True
        End If
    Next RowCnt
End Sub

 

[dal3gacy]

Thank you very much, it works lika a charm. I was adding this code in the macro itself. This is why it didnt work. But by opening project and adding the code there it works. I am a VBA noob.

 

[dal3gacy]

Thank you, it works like a charm. I was adding this code in the vba itself. But after opening wit Alt+F11 and the workbook it works. Thank you again.

 

[dal3gacy]

Question 2 : Can i add another macro or vba code so that toghether with the pevious one, before printing to check certain values in 3 cells and print additional worksheets too. Something like:

Public Sub PrintReport()
Sheets("DE PRINTAT").Select ' <<<<---Page where data is
If Range("G7").Value = 1 Then
Sheet("P-uri Fx").PrintOut


If Range("H7").Value = 1 Then
Sheet("P-uri Rx").PrintOut


If Range("I7").Value = 1 Then
Sheet("P-uri Elemente").PrintOut




End If
End If
End If



'Add the other sheets as needed
End Sub




Private Sub Workbook_BeforePrint(Cancel As Boolean)
BeginRow = 7
EndRow = 274
ChkCol = 2
For RowCnt = BeginRow To EndRow
If Cells(RowCnt, ChkCol).Value <> 0 Then
Cells(RowCnt, ChkCol).EntireRow.Hidden = False
Else
Cells(RowCnt, ChkCol).EntireRow.Hidden = True
End If
Next RowCnt
End Sub


I know the second VBA does what i want, first one is a pain.

 

[Stiuart_W]

Firstly. The command should be Sheets("P-uri Elemente").PrintOut

You should note that EVERY time you encounter that line the code in the

Private Sub Workbook_BeforePrint(Cancel As Boolean)
   
End Sub

Will run

Not really knowing what your code is doing it's hard to really advise the best way to proceed but I'm sure you can do what you want.

 

[barren]

The before print with restricted print area can be a little confusing when run on windows. The print preview does not trigger the before print sub, but when it goes to the actual print it will be the triggered. On a MAC because the print preview is triggered differently it happens immediately even with a printbto PDF. Drove me crazy on Windows trying to figure out why it wasn’t working, then i found out you just had to do an actual print and everything was fine.