Create a data entry form for Excel

Hello,

I would like to create a data entry form in Excel so that by clicking button named "Create BKA", it would a open a data entry pop-up window as shown in the attached Excel file. After entering this required data in pop window, it should be copied in the next available free row such that each entry should appear under the respective column title as mentioned in the data entry form.

I would really appreciate the help of expert in this forum to solve this problem as I am still learning the macros.

Thank you in advance!

Is column C (ITEMGRUPPE_NAME) the value from column A along with the value entered in the "Description" text box?

BSB

Yes, if the user enters a description that column can be populated automatically from the userform.

Leave it with me and I'll post something back shortly.

BSB

Give the attached a try.
Code could be written to be more efficient, but I've written it in a very basic way that means you should be able to amend it yourself.

I'll leave you to make the form look pretty.

BSB

I've followed the steps you outline above, and pressed the button and all works fine for me.
See attached with the results entered. I've also added a button at the top of the sheet to open the form.

Your screen grab attachment doesn't work so I can't see which line of code you're getting the error on.

BSB

Originally Posted by BadlySpelledBuoy

I've followed the steps you outline above, and pressed the button and all works fine for me.
See attached with the results entered. I've also added a button at the top of the sheet to open the form.

Your screen grab attachment doesn't work so I can't see which line of code you're getting the error on.

BSB

Good Morning @BadlySpelledBuoy,

thank you first of all for your amazing support. I tried this new excel and it is definitely working when I select one Modellgruppe in data entry pop-up but when I select two or more number of "Modelgruppe" at the same time in data entry form then it is still not working and it is just showing choosen Sachgebiet "AUSG08" aswriiten value and everything else is blank (Please refer to snip "2. After Execution").

So there are two things missing in our data entry form:

1. It should work when mutliple "Modelgrupppe" is selected in Data Entry Form. Eg. Please refer to attached Snip "1. Before Execution" where I have selected four Model "A7, A8, A9, A10" and now this each model must appear in separate row as shown in snip "This is how it should have worked"

2. I forgot to mention one thing earlier that if during Data Entry, if Sachgebiet = "AUSG08" one ModelGruppe = "A4" is selected and if is "PRN = 1L0, 1LR, 1LQ" then after execution three row must appear with same Sachgebiet, same Itemgruppe Nummer, same Modelgruppe which is "A4" in this case but in each row PRN will be unique. So First row will have all the details same expect PRN which will be "1L0" and in second row PRN will be "1LR" and in third row PRN will be "1LQ". So in total 3 rows with same detail but with different PRN in each row.

Similarly it should work when multiple "Modelgruppe" is selected in data entry form. If Model "A7,A8,A9,A10" are selected at the same time and PNR is filled with "1L0, 1LR, 1LQ" then it should generate in total (4 model x 3 PRN = 12 rows) 12 new rows. So A7 will be with PRN "1L0, 1LR, 1LQ", then A8 with PRN 1L0, 1LR, 1LQ", A9 with PNR "1L0, 1LR, 1LQ" and A10 with "1L0, 1LR, 1LQ" in each row respectively.

Sorry for the rework, i hope i am able to explain the To Dos clearly.

Thank you once again and wish you a nice day!Attachment 879844Attachment 879845Attachment 879846

Originally Posted by Sintek

Hope I'm not overstepping...lending from BSB...
I prefer sticking to default control names...

Enter PRN as 1L0,1LR,1LQ ... No spaces

Unfortunately the data entry form is not working at all. I entered all the details and clicked "data transfer" but nothing is written in the row.

Also how I can program, so that new "Itemgruppe Nummer" starts in continuity of last "Itemgruppe nummer" which is already there in example. For example if in the excel last "Itemgruppe Nummer = I00000262065" then new entry must start from "I00000262066"

Thank you once again!

Originally Posted by vk1995

Good Morning @BadlySpelledBuoy,

thank you first of all for your amazing support. I tried this new excel and it is definitely working when I select one Modellgruppe in data entry pop-up but when I select two or more number of "Modelgruppe" at the same time in data entry form then it is still not working and it is just showing choosen Sachgebiet "AUSG08" aswriiten value and everything else is blank (Please refer to snip "2. After Execution").

So there are two things missing in our data entry form:

1. It should work when mutliple "Modelgrupppe" is selected in Data Entry Form. Eg. Please refer to attached Snip "1. Before Execution" where I have selected four Model "A7, A8, A9, A10" and now this each model must appear in separate row as shown in snip "This is how it should have worked"

2. I forgot to mention one thing earlier that if during Data Entry, if Sachgebiet = "AUSG08" one ModelGruppe = "A4" is selected and if is "PRN = 1L0, 1LR, 1LQ" then after execution three row must appear with same Sachgebiet, same Itemgruppe Nummer, same Modelgruppe which is "A4" in this case but in each row PRN will be unique. So First row will have all the details same expect PRN which will be "1L0" and in second row PRN will be "1LR" and in third row PRN will be "1LQ". So in total 3 rows with same detail but with different PRN in each row.

Similarly it should work when multiple "Modelgruppe" is selected in data entry form. If Model "A7,A8,A9,A10" are selected at the same time and PNR is filled with "1L0, 1LR, 1LQ" then it should generate in total (4 model x 3 PRN = 12 rows) 12 new rows. So A7 will be with PRN "1L0, 1LR, 1LQ", then A8 with PRN 1L0, 1LR, 1LQ", A9 with PNR "1L0, 1LR, 1LQ" and A10 with "1L0, 1LR, 1LQ" in each row respectively.

Sorry for the rework, i hope i am able to explain the To Dos clearly.

Thank you once again and wish you a nice day!Attachment 879844Attachment 879845Attachment 879846

Hi @BadlySpelledBuoy, would you please look into these again?

Thank you!

Originally Posted by Sintek

I entered this...as per...


Attachment 879863

And this is the result...

Attachment 879864



BSB's code does that

Remember what I said about PRN

would you please this excel file which gives you this result, then i can check again. Thank you.

Originally Posted by Sintek

File in post 9

Thank you very much, now it's working fine. Just one more question: how can i start the new entry with Itemgruppe Nummer = "I00000262066" as I want to add this macro in existing file where last Itemgruppe nummer is already "I00000262065".

Thank you once again.

@Sintex - Thanks for taking over on this. I've not had a chance to reply as been busy with the day job.

I'm still not sure why there was an issue with the original file. Worked fine for me.
It's always difficult to diagnose the issue when we're not being shown what the problem is. All attachments of screen grabs didn't work when trying to open, so I was left clueless to the problem.

BSB

Originally Posted by Sintek

@ BSB...Your file worked as it should...
OP wanted an additional requirement...
Duplicate PNR numbers seperated by comma to be entered all individually with all other data as seperate entries...

Hmmm... well posts #6 and #8 seem to suggest it wasn't working when multiple values were selected.
Perhaps I misunderstood. It's been a long week! :D

BSB

Hi, I have one more question to this existing macro file. I want to create one more Text named as "Restriktion Nr" in this macro which I tried to add in Form but it is not copying the data after entering submitting the details in it. Maybe code for this additional "Textbox14" and "Label17" is missing. Could you please here?
Just for knowledge, could you please educate me how to delete or add the text box in the user form here. I would really appreciate your help.

I have attached snip showing new textbox "Restriktion Nr" but it is not active. Thank you the help in advance!

@sintek thank you for the quick feedback. Let me try to explain with help of few snips and attached Excel File.

Snip 1: User will click the "Show Form" button to enter the data in "BKA" Sheet. After entering the data, user click on "Daten Übernehmen (Transfer Data)" button in User Form to get this data in the excel.
In the current version, if I write "Reifen" in Description then along with Reifen entry of Sachgebiet is automatically attached to it in the front and in excel we see "AUSG01 Reifen" instead of "Reifen". Now here I do not want to attach Sachgebiet to the description and want to see only the value which is entered in the Description field i.e. "Reifen".

Snip 2: Based on the value entered in Field "Gueltig_AB" and "Gueltig_BIS", we want to copy the "MAXKAPA" (1500) and "NORMKAPA" (1000) value in Sheet named "BKA_MaxKapa" and "BKA_NomKapa" respectively.

Snip 3: Now look for the "Gueltig_AB" and "Gueltig_BIS" value in row 4 in "BKA_MaxKapa" Sheet. For 202515 entered in Gueltig_AB Field is found in cell AF4 and 202653 is found in cell DR4. Once this range is found from AF4 to DR4, copy MaxKapa value (1500) in this range in the newly created row 5 as shown in the snip. Here Row 4 will always be same and can be used as a reference.

Snip 4: Simialrly look for the "Gueltig_AB" and "Gueltig_BIS" value in row 4 in "BKA_NomKapa" Sheet. For 202515 entered in Gueltig_AB Field is found in cell AF4 and 202653 is found in cell DR4. Once this range is found from AF4 to DR4, copy NomKapa value (1000) in this range in the newly created row 5 as shown in the snip. Here Row 4 will always be same and can be used as a reference.

I hope, I was able to explain now bit clearly. Please feel free to ask if there is any question.
Thank you very much once again!

I forgot one more point to mention here that if I enter "202510" as Gueltig_AB and "999999" (six times 9) as Gueltig_BIS in the UserForm then copy the entered MaxKapa and NomKapa value from column AA as "202510" is found in CC4 till the end of time horizon in "BKA_MaxKapa" and "BKA_NomKapa" Sheet Respectively. In this file last calender week is "202653" in BKA_MaxKapa & BKA_NomKapa Sheet, so in this case it should copy values till 202653. If in future if I extend this calender week in row 4 for year 2027 then it should look for the last calender week in row4 and copy "MaxKapa" and "NomKapa" values in respective sheets till the end. So Gueltig_AB can be changed based on requirement but if there is 999999 mentioned in Gueltig_BIS then copy until the end of the time horizon in newly created rows with help of UserForm.

Originally Posted by Sintek

My apologies...This above makes no sense...
Are you wanting all the sheets...BKA | BKA_MaxKapa & BKA_Nokapa to be updated at the same time...
You keep on mentioning row 4...Is it always going to be row 4...or the row that the newly data was just placed in...
I suggest...Explaining this again showing the required input into the sheets...

I have tried to explain here more clearly, I hope it helps now to understand my use case much better. I appreciate your support and active feedback.

Originally Posted by Sintek

Geez...And the plot thickens...

Please Login or Register  to view this content.

I know, its getting bit complicated but it would be great if you can assist me with this

Should i replace this code with old one? Will then it will work?

Thanks once again!

Originally Posted by Sintek

See attached...

This is super! I appreciate your quick help. Really grateful for your work and thanks once again.

I wish you a nice day

Originally Posted by Sintek

Happy to have assisted...

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Tx for rep +

Sorry for the trouble but earlier I have worked on this file and have this new attached version. Can we somehow integrate this new MaxKapa and NomKapa value copy logic to this file. Also in the version also, if I write "Reifen" in Description (Beschreibung) then along with Reifen entry of Sachgebiet is automatically attached to it in the front and in excel we see "AUSG01 Reifen" instead of "Reifen". Now here I do not want to attach Sachgebiet to the description and want to see only the value which is entered in the Description field i.e. "Reifen".

I tried to integrate myself but somehow the MaxKapa and NormalKApa values are not copied in the respective sheets based on YearWeek mentioned in Gueltig_AB and Gueltig_BIS. Would you please help me to integrate our new solution in the attached excel file?

Thanks once again!

Originally Posted by Sintek

No...
After all the work I just did...you go and delete textboxes & change all the textbox numbers and incorporate more comboboxes...
Obviously the previous supplied code won't work...

I understand your frustration and do not want to cause any rework and inconvenience to you. It's just that I had an actual file to which these changes are also required.

Nevertheless, I am very thankful for your help and quick response.Learned quite a lot from your work.

Hey, I made some changes and it worked. It would not be possible without you.
I really appreciate your help and thanks once again.
Have a nice weekend!

Originally Posted by Sintek

Glad you got it sorted...Happy Coding...

Hey, I am again sorry for the trouble. I don't want to cause any inconvenience. Working with data is apparently not so easy.
I wish you a nice weekend!