EASILY Make an Automated Data Entry Form in Excel
Вставка
- Опубліковано 12 чер 2024
- Make an automated data entry form in Excel.
🔥 Take our VBA & Macros Course: www.careerprinciples.com/cour...
🆓 DOWNLOAD Free Excel file for this video: careerprinciples.myflodesk.co...
In this tutorial you'll learn how to make an automated data entry form in Excel where you can add values, checkboxes, dropdowns, and even popups depending on your answer. Once a user clicks submit, the data automatically gets collected in a separate worksheet with a database. First we'll go over how to format the Excel file to look like a form, then we'll go over how to create the results sheet, followed by how to automate the whole process, and finally how to protect the Excel sheet so other users don't break it.
LEARN:
🔥Power BI for Business Analytics: www.careerprinciples.com/cour...
📈 The Complete Finance & Valuation Course: www.careerprinciples.com/cour...
👉 Excel for Business & Finance Course: www.careerprinciples.com/cour...
🚀 All our courses: www.careerprinciples.com/courses
SOCIALS:
📸 Instagram - careerprinc...
🤳 TikTok - / career_principles
🧑💻 LinkedIn - / careerprinciples
▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬
Chapters:
0:00 - Format Data Entry Form
5:42 - Store Submitted Results
8:18 - Automate Data Entry Form
12:44 - Protect the Form
🔥 Take our VBA & Macros Course: www.careerprinciples.com/courses/vba-macros-for-business-automation
great presentation - easy to follow and helpful!
This is my first time watching your video and i found you do amazing while watching. Clear, direct, detailed and concised.
Thank you Kenji I enjoyed this video as I was working with you , You're the best keep up the good work. all the way from South Africa
Thanks Kenji, you make my work life easier with this. Well explained and have change a lot of my data entry .
Thanks, you are a lifesaver !!
Thank you for simplifying this process
Kenji - you are a genius. Thank you!!!!
Thanks Kenji much appreciated as always
Kenji, just to let you know that You are the Best. Outstanding video
Thanks so much 😁
THANK YOU
Its easy to understand with your good explanation
Your post are very simple and concise to understand
Thank you sir. Your instructions are very easy to follow. I was able to automate my library book stocks. I currently in planning of using excel to automate a library lending system.
The automation section was great! I never would have thought about that! Thank you!
This is good when you have 15 or less questions
What about if you have 350 questions data entery of 500 respondents 😅.
Sadly, i wish my professor use Google form.
This was a very useful video. Thank you for creating it.
thank you for this tutorial.
This is so helpful!!! thank you so much for uploading this!!! subscribed! 🙏
nice video. Couple things you should change, in the protection tab, don't allow selection of protected cells, this way the cursor stays only in the cells the are unprotected, so it works more like a user form where you can't select the titles. you also need to unprotect the refers to cell for the Option buttons. The option buttons are trying to change a value in a protected cells. this is why you get the error message.
Interesting lession. Thanks
Sir please make such kind of videos on Power BI
But you are making excellent videos on excek ❤❤
Very helpful and easy to follow. Kudos!
'm literally watching you while having lunch. Thanks for the entertainment/learning video.
Nice! Hope lunch is a good one haha
Easy to understand, thank you ❤
Thanks a lot for this amazing tutorial. Never knew Excel was so powerful .
Glad it was helpful!
excelent explanation, thanks Kenji
Thank you for the video!
I would like to suggest a slight alteration to the process.
8:05 - You could omit the need to transpose (and by proxy, the "Raw" sheet) by moving the refence cells (C21:D30) to the top of the Data tab as a single row instead of as a column on the input sheet.
Then, you could paste directly to a table on the same page using your process.
Yup, ikr?
Sir your videos are very useful. Please make videos on Power bi
Very informative. I have a few ideas for work that I will try this with.
Very good, useful! Thank you.
Great video. I wonder if the submitted data could feed a dinamic table as well.
guy you did so well, i love the video it's understandable
Thank you for this excellent video
Great video, you make learning looks easy.
Well explained and thanks for sharing!
what a fantastic presentation. Thank you
Thanks you so much, it was really clear and valuable.
Thanks Kenji for this work. I would like to know if I can do the same macro for "update" as "submit" to update the data.
👏🏻👏🏻👏🏻 wow awesome explanation and good very easy to understand for new Learner's
Thank you Kenji... It is very good Video. Its save lots of Time.
Thank you, I have a lot to learn here..
Nice interpretation
thank you so much for everything 🙂
Simple and great 👍
Amazing way of teaching
Thank you for your comment :)
Great!
We always learn something new with you.
But here is an other way to generate a form more easily by creating the data array first then select a cell in the array and in the search console, Search for the function "form ". Excel will show a you a form based on the informations in the first row.
However this video allows us to learn more functions.
My methode is for lazy persons 😂.
Nice video! Is there a way to have a message if some fields have already been inputted previously to not create a duplicate?
Great Video. Ensure the cell link for the two radio buttons is unlocked before you lock the sheet. It should work after that.
Even I had the same thought
very well noted
SUPER BROO NOW I DONE THIS AUTOMATION WORKS REALLY GOOD THAN U 😍😍
Hi Kenji, thank you for all your excel tutorials.
Can I trouble you to consider showing your viewers how to do a dashboard that tracks monthly dividends?
Hi Kenji, I'm always watching your videos! Thank you for this tutorial. By the way, could I request an inventory management system in Excel? It will be used for a hotel and casino business. Thanks!
Very great and inspiring! Thànk a lot❤
Brill Vid, learnt a lot
Hi Kenji thanks for the video. I wanted to add that a solution to correcting the form buttons is by right clicking and going to format control, you will see an option tab for protection. Once you uncheck the "locked" option, it will allow the user to select the button even when the sheet is in a protected state.
Mine doesn't work
thats fire! thanks!
Great content, can we do the same with Office Script
Very nice and useful.
I am doing acca now on what skills I need to build my career previously I had Excel knowledge
perfect video
I have a question
in the "form" you have the item "Staff ID"
how is it possible to make the "Staff ID" value so that I don't have to enter it manually, but it would always be new with a new record
example:
first staff value 1
second staff value 2
etc...
something like n+1 always on a new record
When hiding information that is off the end of the intended user work area it's generally more secure and less prone to data corruption to hide the rows and columns rather than match the colour.
yeah right, but as long as the sheet is protected with the cells locked, making only your intended work area active, then your data remains intact...
Wow really great!!
Hi Kenji, in the macro where the data gets copied while the previous data move down, instead of this how can i do it so that new data gets added to the immediate row and continue to add to the rows below.
Very interesting 👌
Awesome 🤩
Hi, great step by step explanation. However, as I tried to run the macro program in my excel (MacBook) it says "The macro may not be available in this workbook or all macros may be disabled". I tried to save in xls format.
You only need to unprotect the cell where you link the option buttons, in this case E27. After you do that it will work
Thanks
Sir could please make a video for school payments automation per student?
Loved it 💕💕💕💕
Thank you!
what if i want to change the look of the responses page of the excel sheet? i want it to look like a decently looking document that i can print that has headers and footers and a company logo for example. Can i specify what cell is it going to insert the data inputted from the form?
interesting video. just why to format 1mln rows for such small form?
Hi Kenji, why the second time that I type a serise of data, it can follow the previous one and will not be replaced? Please let me know. Thanks.
how will we send out the form? without the data and other sheets. I think it is not possible. but nice learning vid
Hey Kenji, Great video! Got lots of answers here. Do you have any ideas on this: We have trainers that need to tell us where in the world they will be training. From date - To date, Country, and Time Zone. I will want to plot that on Excel. I think this form idea of yours is going to be great - so my question: How can I use the 2 dates - from and to - to populate themselves in a grid so that we can see all the dates in between as well??
Thanks Kenji, great video to follow! How would we stop the 2 other tabs (Data and Raw) from being viewable as there could be sensible information on there?
Hide the sheets. Right lick on each sheet(Data and Raw) , you will see the option to Hide.
To unhide them, right click on the Form or any unhidden sheet: it will show you all hidden sheets and you will be able to unhide them from there
You have to go to format settings on the checkboxes and unlocks them, and enable auto filters when you protect the sheet
Sir, please make about stock market❤❤
Thanks!
Thank you for your support!
Great
Hello , If I want to edit my previous entry after clicking the submit button, how can I do it?
when click on evening check box it is showing true and false but in your videos it is showing as 2,1 numbers ... also tell me how to check evening and morning at a time when i am checking another box it is not auto uncheck..... i am unchecking it manually...
Please tell me how should i correct it...
Hi kenij sir, i'm rimon from bangladesh and learn excel video to your channel,You are very interisting now i want to your help. i want an best data analyst please suggest your opinion
Is there a way to choose cells shift up ؟
thanks for that but how can i refound it after that
What causes my micro to shake, sometimes freeze until I switch off my computer? Thank you
can i do all this on website excel? I don't have inbuilt application.
Can you run this in excel web?
Great work and well explained
I have one question though..how can we intigrate a condition on the submit button so that they cannot click on it unless they complete the whole entries or at least the essential ones which we specify in advance?
You could but you would have to edit the macro, and essentially write the VBA code to it. I think in this tutorial he was steering away from showing any VBA code that might scare a lot of people off.
However, if you wanted to edit the VBA, you could add a simple IF statement that would exit the sub immediately if not all fields were filled in. It would look something like this:
If ThisWorkbook.Worksheets(Form).Cells(4, "C") = "" Then Exit Sub
If ThisWorkbook.Worksheets(Form).Cells(6, "C") = "" Then Exit Sub
These would be put at the very top of the subroutine and be repeated for each cell. Basically this just checks the cell and if it equals "" which is two double quotes which means empty, then it exits the subroutine without doing any of the other steps. Notice I used the name "Form" for the sheet because that is what the video creator named his worksheet, and You can see that you list the row before the column, and normally Column is a number and "C" would be 3, but you can also place the letter in quotes and I did that so you could easily see that the row comes first separated by a comma and a space then the column. it could also look like this:
If ThisWorkbook.Worksheets(Form).Cells(4, 3) = "" Then Exit Sub
If ThisWorkbook.Worksheets(Form).Cells(6, 3) = "" Then Exit Sub
Simply add this for each cell you want to make sure has info and viola! done
If you wanted to go a tiny bit further and let the user know they have to fill out all fields, then you could change those lines to this:
If ThisWorkbook.Worksheets(Form).Cells(4, "C") = "" Then GoTo MessageUser:
If ThisWorkbook.Worksheets(Form).Cells(6, "C") = "" Then GoTo MessageUser:
And then at the very bottom of the subroutine put this:
Exit Sub
MessageUser:
MsgBox("All fields must be completed.")
Exit Sub
Can this work in iPads?
I added a date field to track date of entry and it's not reflecting correctly in the Data Tab. Any guidance?
How can i make the RAW data sorted automatically?
Great thank you lot Mr Kenji
Hello, I have a problem. After submiting the form for the 4th time, I got an error report and I couldn't deburg it. Please advice on what to do.
You are my love Kenji
Bravo
Not sure if anyone answered - but to protect the check boxes:
i grouped them
then selected the group and dbl clicked
Format shape window comes up on the right of the page.
select the third set of settings - select the "locked" option
finally protect the sheet as Kenji did.
now the options can be selected /changed
but they cannot be select for editing
The reason the Option radial buttons don't work, is because the cells that are linked to the radial buttons are also locked. By default, VBA and Form Controls can't change values (and formulas) of protected cells, although this can be overwritten if you protect a workbook or worksheet using VBA. In this case, a simple solution without manual VBA coding is to make sure that the the referenced cell-link is unprotected. You can do that by either directly editing the properties of the cell, or better still, use cell styles.
I think you're right.
Cool
How about the data and Raw sheets? Why dont you protect it as well. Tried protecting all sheets but a pop up error message appears. Meaning, you cant protect Data and Raw sheets, which is vulnerable to being erased or deleted.
Espectacular @KenjiExplains !!
Good video, but I think in this situation is better to use MS Form or PowerApps. It is not so complicated created this there and users do not have access to stored data. And it is more "mistake-proof". You can protect everything in excel, but in MS form or PowerApps you no need to do it, because there is nothing around. But this is only my opinion, I think that everybody prefer some way. 🙂