1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

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

Control Form

Discussion in 'VBA Macros' started by Pierre, Feb 13, 2018.

  1. Pierre

    Pierre Member

    Messages:
    65
    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!

    Attached Files:

  2. Hui

    Hui Excel Ninja Staff Member

    Messages:
    11,066
    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
  3. Pierre

    Pierre Member

    Messages:
    65
    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!
  4. Hui

    Hui Excel Ninja Staff Member

    Messages:
    11,066
    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 ?
  5. Pierre

    Pierre Member

    Messages:
    65
    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.

Share This Page