• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Control Form

Pierre

Member
Hello,

I'm working on a file to control the products which are prepared in a parcel. In the excel file I will have one sheet with all preparation data, and second sheet will be with formulas to have only information about one specific parcel.

The way I see it, the person in charge of control will launch control form, scan the box number in the form. This number will go to B5 in "Control" sheet to retrieve data. Then the controller will take one reference, scan it, info will appear in the form, he will input quantity in the box. If quantity planned = quantity prepared, an information OK/NOK will be written.

If possible I'd like the form to detect when all products are checked to have an information on the form, which will then propose to make control of new box.

I created the form, but i'm a bit stuck from there.

If someone could help me go forward, it would be appreciated!
 

Attachments

  • Control_parcel.xlsm
    26.5 KB · Views: 4
Pierre

Which worksheets have data or a blank at the start
then Can you please walk us through how this should be used step by step
to the final output
 
Hello Hui,

The sheet Parcel_date will have the data, the sheet "Control" will be blank

If I go step by step
1) Click on button "launch control"
2) Input of Box number in the form. This box number should be then input automatically to cell B5 of sheet "Control".
3) Input of first bar code. The information "Reference", "Product Name", "Quantity planned" should filled automatically. These info were retrieved thanks to the formulas in sheet "Control".
3)Input of quantity per box. If quantity planned=quantity in box, there should be OK or green light under OK/NOK, if not it should be NOK or red light (or anything similar)
4)Repeat steps 3 and 4 until there is no more reference to check.
5)If possible, once all references are checked, something should appear to say that all references were check. There will be maximum 20 references per box
6)When user click "new control", everything should be cleared (form and cell B5) and be ready for new input of box number

Thanks in advance!
 
Please see my comments and other questions below in Blue

2) Input of Box number in the form. This box number should be then input automatically to cell B5 of sheet "Control". Does Box Number come from the Parcel_Data worksheet? Do you want a drop down or list to select it from ?

3) Input of first bar code. The information "Reference", "Product Name", "Quantity planned" should filled automatically. These info were retrieved thanks to the formulas in sheet "Control". Does Bar Code come from the Parcel_Data worksheet? Do you want a drop down or list to select it from ?

Where do "Reference", "Product Name", "Quantity planned" come from ?


If the data is being transferred from the VBA Userform to the control worksheet,
Do you really need to use a UserForm in VBA, as that could all be done on the Control Worksheet?

After you press the NewControl button you want the Userform and Control Worksheet cleared, what happens to all the old data ?
 
Here are some clarifications

2) Input of Box number in the form. This box number should be then input automatically to cell B5 of sheet "Control". Does Box Number come from the Parcel_Data worksheet? Do you want a drop down or list to select it from ? The Box Number will be entered directly by the person in charge of the control. No need for list or drop down.

3) Input of first bar code. The information "Reference", "Product Name", "Quantity planned" should filled automatically. These info were retrieved thanks to the formulas in sheet "Control". Does Bar Code come from the Parcel_Data worksheet? Do you want a drop down or list to select it from ? The data can be retrieved from the "Control" worksheet. After the box number in entered in the firm it should be put in cell B5 pf "Control" worksheet. The formulas in columns C,D,E,F,G,H will retrieve all information on the specific box.

Where do "Reference", "Product Name", "Quantity planned" come from ?
When the controller then enter manually the first bar code for the first product, these information can be taken from the worsheet "Control"


If the data is being transferred from the VBA Userform to the control worksheet,
Do you really need to use a UserForm in VBA, as that could all be done on the Control Worksheet? I'm open to other solution than Userform. I was just thinking that it might be easier to manage and understand from the person in charge of the control. The main thing that I want is that the controller will entered the barcode of a product and he can see quickly what product is it, what quantity was planned and then when he entered the quantity he sees in the box, if it is the correct quantity or not.


After you press the NewControl button you want the Userform and Control Worksheet cleared, what happens to all the old data ? The Userform and the cell B5 of Control worksheet should be cleared.
 
Back
Top