Run a VBA Code before printing in excel

**Tonibco** · 06-08-2022, 10:54 AM

Hi all,

I would like to make users mandatory to fill-up some specific fields of an excel Form. The form consists within an excel file including different fields where the user must fill-in some of them as mandatory.
Since the document is a template document (excel), I'd like to avoid be printed in case one of the mandatory fields are not filled-in.

Form.jpg

In order to perform that task, I am running a VBA using 'Beforeprint' declaration. However, I have no clue how to develop the rest of the code function. Would you mind please provide help? Thank you in advance.

Please Login or Register  to view this content.

**cytop** · 06-08-2022, 11:22 AM

A recent, similar, thread ...https://www.excelforum.com/excel-pro...ml#post5675374

**jolivanes** · 06-08-2022, 12:23 PM

If you change 2 cells to be consistent with the others (*Tracking info required (Yes/No) & *Ship today (Yes/No))

Please Login or Register  to view this content.

**jolivanes** · 06-09-2022, 12:33 AM

If you want it a little fancier.

Please Login or Register  to view this content.

Attachment 783351

**Tonibco** · 06-09-2022, 09:28 AM

Thank you for your fast reply. However I am completely new in coding and I have many doubts.

Please Login or Register  to view this content.

//Does this function add the BeforePrinter task?

Please Login or Register  to view this content.

// This line means to check the entire Column 'A'? I need to check column 'B' instead, if
// that's the case.

Please Login or Register  to view this content.

// Do you mean...personalize the comment?

I do not manage to run it properly. The file still prints even though the B column cells are empty.

Thank you in advance

**jolivanes** · 06-09-2022, 10:46 AM

Attach a sanitized copy of your workbook.

**jolivanes** · 06-10-2022, 12:06 AM

I made a representative workbook, I think, while waiting on a sanitized copy .
Try it out and let us know.

**Tonibco** · 06-10-2022, 03:56 AM

Woow! That previous example is so great! It is exactly what I expected. I only need the table Border lines when printing.

Hereby a copy of my file.
Attachment 783471

Thank you very much!

**jolivanes** · 06-10-2022, 11:10 AM

To attach a workbook, you have to use "Go Advanced" at the bottom right.
The yellow marker at the top explains it.

Do you want to add borders with code or manually set them?

**jolivanes** · 06-10-2022, 11:40 AM

BTW, if you put this in the ThisWorkbook Module, you don't need a button on the sheet.
You can use your normal print button on the toolbar as you did previously.

Please Login or Register  to view this content.

**Tonibco** · 06-13-2022, 09:25 AM

Hi Jolivanes,

Thank you very much for your last update. It works now as expected. The Template will be only printed out when the mandatory fields are filled out.

May I have some small idea what the code exactly does line by line, please? Would you mind to provide a short comment after the lines? For example I am aware that Chr(42 belongs to the Ascii table *) but not what the rest of the code does.

I am very curious to get a more detailed idea.

Thank you in advance.

**jolivanes** · 06-13-2022, 03:05 PM

Based on the code in Post #10.
Line #1:
Is invoked when printer button is clicked.
Line #2:
Declaring variables as needed.
Line #3:
Loop through each cell from the first cell in Column A to the last used cell in Column A
One should have a header row and start from the 2nd cell.
Line #4:
Check if the left most character in the cell is an asterisk (Chr 42) and if the cell beside it to the right in empty.
Line #5:
cels is an array that holds data.
Check if it has anything in it.
Line #6:
If Line #5 is True (array is empty), it will enter the needing attention string, put in a couple linefeeds and the address of the cell that needs attention.
Line #7:
If Line #5 is False, it will do what Line #8 says.
Line #8:
Add a linefeed to the holding array (cels) and add the address of the cell to the right of the cell that is being checked for the asterisk.
Line #9:
End of the middle block of If.... Then statements.
Line #10:
End of the outside block of If.... Then statements.
Line #11:
Go to the next cell in Column A and go through Line #4 to Line #10 again. This is repeated until all cells in Column A and B have been checked.
Line #12:
Checks if the holding array has data in it.
Line #13:
If Line 12 is True, it will show the data in the holding array (cels)
Line #14:
Cancels the execution of events.
Line #15:
End of the block of If.... Then statements.
Line #16:
That is it for today folks. End of the show.

**Tonibco** · 06-15-2022, 04:11 PM

hahhaha Last line made me laugh in a 'for' loop manner.

Line #16:
That is it for today folks. End of the show.

Thank you so much for your help. Awesome!

**jolivanes** · 06-16-2022, 12:40 AM

Good to hear that all is well in Twente.
Good Luck

**Tonibco** · 10-26-2022, 03:46 AM

Dear Colleagues,

I have actually a problem when I upload the excel file into my company cloud. Somehow the 'Developer' functionality does not take effect. Does it occur due to the lack of the 'Developer' functionality in the cloud (excel in the cloud)?
My expectation would be to be able to use that funcionality in the cloud as well, not only when someone download the file in his/her own computer. Do I have to use any script in order to solve it? Would you mind please give me a hand?

Thank you very much in advance.

Toni

Run a VBA Code before printing in excel

LinkBack

Thread Tools

Rate This Thread

Display

Run a VBA Code before printing in excel

Re: Run a VBA Code before printing in excel

Re: Run a VBA Code before printing in excel

Re: Run a VBA Code before printing in excel

Re: Run a VBA Code before printing in excel

Re: Run a VBA Code before printing in excel

Re: Run a VBA Code before printing in excel

Re: Run a VBA Code before printing in excel

Re: Run a VBA Code before printing in excel

Re: Run a VBA Code before printing in excel

Re: Run a VBA Code before printing in excel

Re: Run a VBA Code before printing in excel

Re: Run a VBA Code before printing in excel

Re: Run a VBA Code before printing in excel

Re: Run a VBA Code before printing in excel

Thread Information

Users Browsing this Thread

Similar Threads

Code Printing list of hyperlinks in excel file

Excel 2007 : Data Cut Off When Printing - Print Preview vs. Actual Printing

Cancel printing VBA code doesn't works on excel 2010

Excel Printing - Printing new input data (only) on the same sheet

Printing a Word doc from Excel VBA code

[SOLVED] Printing a Pivot Table from code - Excel 2003

Printing a Pivot Table from code - Excel 2003

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions