Mandatory fields, and lock rows when complete

Dear Excel Community,
I am working on a small person project using Excel VBA.
I got this Idea from a youtube profile. the video is called Using Invoice Template to Automate Invoice Creation and Printing with VBA by Dinesh Kumar Takyar

I have tried but failed to acquire what I wanted hence have come here for assistance.

I have an Excel workbook which is a receipt (named receipt) while another workbook is the data entry (named Testing). (I have them attached)

Basically what happens is that in the "Testing document, I enter data and when I Click Print, the data will go to the Receipt workbook, Save the receipt as defined in VBA and take it to the Printer. once this action has taken place, a DONE will be written at the end of the Row in the Testing workbook

I want to now achieve 2 things

1: I would like all the all the fields in the Testing workbook to be mandatory that will then allow me to print
2: I would like that when the row has its last Column "DONE" that it gets locked so no one can edit it late.

I hope to get the assistance in that. and appreciate the help I get.

Below are are current VBA codes i have used

Moderator's note: Please take the time to review our rules. There aren't many, and they are all important. Rule #2 requires code tags. I have added them for you this time because you are a new member. --6StringJazzer

Please Login or Register  to view this content.

With this modification, you'll be able to Print only completed receipts and lock those that are DONE.

To lock cells, you must protect the sheet. You can use a password or none.
By default, all cells are locked so you need to select all the cells in the sheet and go to the Format cells menu to change the protection. Unclick the Locked square.
Then protect your sheet.
Then run your macro.

Please Login or Register  to view this content.

In your receipt, you are now limited to 2 items, you have to use more columns in Test so you can have place more then 2 items.
I hope this will help you.

There you are.
The sheet is protected without a password.
All the cells are UNLOCKED but the ones marked DONE which were LOCKED by the macro.

The workbook has been modified to skip uncomplete orders and to protect (LOCK) all the rows with completed orders.

Pierre

Dear Pierre,

I am so thankful for this assistance. this is what I exactly wanted to achieve.

however 1 question/ altercation

I see that if the row has missing entries then it wont print.

however, not all will need to be filled take example the column of "Item 2" "unit price 2" "Quantity 2"

I put those because if a person has 2 items then can add the details in the above named columns

but if the person has only 1 item then i cannot print leaving the above named columns empty.

hence could it be possible to have only some columns as mandatory and not all?

Thank you

with this modification, the mandatory fields are columns 1, 2, 3, 4, 5, 6, 8 and 10.

Please Login or Register  to view this content.

As you mentioned that you tested this on a new workbook, don't forget to UNLOCK all cells as per default they are all LOCKED. That probably explain your issue.

In the Review menu, when you click on Protect sheet, it will ask you for a password. You'll be asked to enter it twice.
If the menu is saying "UnProtect sheet", that's because the sheet is already protected (as the one I modified).
You enter your password there and you'll be able to modify protected cells.

if you mean protecting and unprotecting worksheets, yes, it can be done with VBA.

Dear Pierre

As I continue to Develop this Mini-Project, I came across Searchable dropdown Lists from youtube

Create a searchable drop down list in Excel By Neil Firth

Create a searchable drop down list in Excel Part 2 by Neil Firth

Now, I have applied this to the workbook

however, when I close the workbook and reopen it, the Functionality of this based on the videos fails to work

I have used 2 sheets for this.

Could you have a look at it and possible assist or have a solution to it?

I have attached the workbook

it will be a pleasure to look into this but it can't be done on this thread.
As per the rules, you must create a new thread because this is a new topic.