User form for input in list

[igormigor]

Hi all,

I need help with a complicated macro for input data in a list. Maybe is not so complicated, but first time I’m doing this type of macros, and I’m stuck in place.

Currently I have list in which the data are inputted directly. Instead inputting the data this way, I need when the button “Fill new data“ is pressed, template to appear in which the below described data will be filled.
After all data is filled, with pressing of the button “Submit data”, the data from the template to be populated in the first free row in the file.
In case some data is not filled, a popup box to appear asking the given data to be filled.
 
The required data are:

Product code – Is selected in cell B3 (from list “Product_code”). After pressing the button “Fill new data”, the content of cell B3 is copied in the selected cell in column A.
Input code – This value is the next available number of the product which is selected in cell B3 (the formula is “COUNTIFS (A9:A24, B3) +1”). After pressing the button “Fill new data”, the content of cell C3 is copied in the selected cell in column B.
Data #1 – This value is coming from preretirement list (the values are Yes and No)
Data #2 – This value is coming from preretirement list from sheet “Pickup_list “(the values are Yes and No)
Data #3 – This value is coming from preretirement list from sheet “Pickup_list “
Date from – start date, with preretirement format (dd mmm yyyy)
Date to – start date, with preretirement format (dd mmm yyyy)
Comment – free text filled by the person who is populating the data

The person who is inputting the data should fill all cells in the form except the “Input code” cell. This should be automatic calculation.

In the attached file is the draft file I’ve prepared.


Thank you in advance for your help.

Igor

Macro test file.xlsb

[gmr4evr1]

I have made a few changes to what you had. Take a look and see if I am on the right track for what you want. The data does not get transferred to the sheet yet as I wanted to see if I was doing what you wanted so far.
igormigor-user-form-for-input-in-list-macro-test-file.xlsb

[gmr4evr1]

Removed this post because the code I provided was flawed. I have corrected it and posted the new workbook with the updated code in the next post.

[gmr4evr1]

Igor,
I noticed some flaws with the code in my last post. I have modified it and believe that I have it figured out now. A message will popup if one of the fields is blank, then the cursor will move to the blank field, then the code will stop to allow the user to fill in the missing field.
I hope this is to your liking...

igormigor-user-form-for-input-in-list-macro-test-file V3.xlsb

[igormigor]

Gmr4ever1 that great!!!!!!! Thank you for your help on this.
Its definitely in the direction I wanted to go.

I just have several comments for adjustment.

For the "Product code", is it possible to be able to be chosen the product code from the sheet (in cell B2), but also to be able to choose the product code from the UserFomr.

The "Input code" should be the formula "COUNTIFS (A9:A24, B3) +1". It counts how many products from certain type are there in the file, and it generates the next available number. It will be great if the next available number is visible in the formula in cell C1, but the next available number calculation to be based on formula included in the macro.

And at the end, after I submit the data, the UserForm to be closed automatically.

Thank you.
Igor

[gmr4evr1]

Igor, I believe I have addressed the comments you had. Please see attached and look the revised form over and test it to verify that it is correct. I changed a couple of additional items as well.

1) The formula you were using

Please Login or Register  to view this content.

was counting the numbers and adding 1 to it which was actually causing it to be 1 higher than it should have been. Example, if there was 1 Product 1 and you added another, the formula would show 3 product 1 instead of 2.

2) I changed the tab order so that the user could start at the top of the form and then tab down through the rest of the form. I find this makes it easier on the user.

user-form-for-input-in-list-igormigor-user-form-for-input-in-list-macro-test-file-v4.xlsb

Please let me know if there are any other changes that need to be done...or if I have changed something I shouldn't' have.