Fully Automated Data Entry User Form in Excel - Step By Step Tutorial
Vložit
- čas přidán 15. 11. 2019
- Simple, Dynamic and Fully Automated Form in Excel - Step by Step Tutorial
In this video, we will learn how to create a fully automated Data Entry Form in Excel with List Box Control as a database. Here, we will utilise Text Box, Option Button, Combo box, List Box, Command Button and Frame to develop this automated form.
If you don't know the VBA or Macro then there is no need to worry. This tutorial is a Step by Step guide and with the help of this tutorial, you will be able to develop a fully automated data entry form and learn several VBA tips and coding.
#DataEntryForm #TheDataLabs
Please follow the below link to download the Excel file used in this video.
thedatalabs.org/fully-automat...
If you are interested in Advanced Automated Employee Activities Tracker with Login, User Profile, Task List, Dashboard developed in Excel, VBA and MS Access then please watch the below video.
• Fully Automated Employ...
Please visit our website for learn more about Excel, Power BI, VBA and Visualization.
www.thedatalabs.org
Please contact us on info@thedatalabs.org
Thanks for watching!
Please like, share, comment and subscribe.
Have a great day!
Please watch the Complete Tutorials (Part 1, 2, 3,4 and 5) of creating Fully Automated Data Entry Form in Excel.
Part 1 (UI Design, Transfer and Reset) - czcams.com/video/P53T6oxgUVA/video.html
Part 2 (Edit and Delete) - czcams.com/video/K1d4JpuLkqE/video.html
Part 3 (Search and Filter) - czcams.com/video/BdEMj4NNXAE/video.html
Part 4 (Validate and Print) -czcams.com/video/0bFOKpaSIBg/video.html
Part 5 (Full Screen, Zoom and Dynamic Drop-down) - czcams.com/video/f7W3HRLQYAs/video.html
Thanks a million!
I saw this complete video did not see about data duplication check
Hi, I am using now the same program you made in your tutorial... and I am thankful to have this kind of vba program through your initiative. It is very nice and I learned many things in your tutorial from Part 1 to Part 5. Now. I would like to ask you a favor if you could add vba codes for having three (3) option buttons to add in the "Gender" portion,
like adding one more option button. Thank you.
#thedatalabs i have watched all the tutorial completely omg you're amazing and i start using this tutorial for work its awesome thanks a million..i like, subscribe and share.
Hello freind it is relly very usful video can you please add if we want to upload attach and add link
Thanks
This is really very simple yet effective userform which one may use to elaborate/customize ones own userform as per the requirements. Integration of the Grid control is really an added bonus. Great work buddy!
Please add image in this database
I recently discovered your tutorials on CZcams and I am truly impressed by the simplicity and accuracy of all of your videos. The presenter is very knowledgeable, humble and has an eye to catch and debug errors. Your videos have helped me greatly!
I was hoping that you could help me with a very small question, and will try to explain the problem statement as accurately as possible..
In your user form, you are taking input from the record and storing in the database in each row. I have to create a database where there are default 4-6 rows for each entry with a YES/NO.
Example of how the input form would look like (with radio buttons for Y/N in Capability section) -
---------------------------------------------------------
Widget Supplier Name : ABC (freeform)
--------
Capability 1 | Capability 2 | Capability 3
Widget 1 : Y | N | Y
Widget 2 : Y | Y | Y
Widget 3 : Y | N | N
Widget 4 : Y | N | Y
--------------------------------------------------------
Expected result in Database should look like this -
Widget Supplier Name | Widget Name | Capability 1 | Capability 2 | Capability 3 |
ABC | Widget 1 | Y | N | Y |
ABC | Widget 2 | Y | Y | Y |
ABC | Widget 3 | Y | N | N |
ABC | Widget 4 | Y | N | Y |
Can you please help me with this? How should the code be modified?
Dear, your video is an amazing tutorial.
My last programming was 15 years ago and I needed to create a full Entry Form for my friend for his work. Just with watching your video, I remember something from my old programming knowledge also and managed to create a wonderful form and automated excel file with all required details. Appreciated your time to post this video.
One of the best VBA form tutorials i have come across. Thank you
Amazing tutorial, I learn more in your first 10 minutes that I learned in the last 3 months, thank you ,
Awesome! I never worked on VBA but after watching this tutorial I developed a fully functional Form. Thanks a lot!
Thanks for watching and your appreciation! Glad to know that this is useful for you. Thanks!
Perfect tutorial. Nothing better than learning the principles in simple form. Thanks a lot. Great work.
Fantastic training tutorial !!!
Very simple and easy to follow along, clear instructions! The video setup, music, etc is also well made!
I have now subscribed and I look forward to see other instructional videos.
Thank you and God bless you and your family!
Thanks for your appreciation!
@@thedatalabs sir I have face a small problem that when I have fill all details and click the submit button then all fields are empty but which are create database list box are not show data
I feel one error in my software that when I update the tabIndex number are 10 then show error
It is also show -1
What's this solution
Okay, first of all, excellent and thorough video/explanation. Now something personal. I am an American with an Indian spritual teacher who is Bengali (what is now Bengladesh), and as part of his dialog, he would always inject a little "...right" as he spoke. He was very dear to me. He passed away in 2007. I am sure you realize that you do this little vocal habit also. I am guessing you are Bengali, but regardless, just listening has given me so much joy! Thank you.
WOWOW! This course was presented so well! BRILLIANT! Even someone Iike me at the amateur level can follow this lesson. Most importantly, after studying all parts in the links below, the results are a sophisticated design which includes macros VBA. It is IMMEDIATELY APPLICABLE FOR PRACTICABLE USE.
Thanks for your appreciation!
Finally, someone who shows me exactly what I need in simple terms.
Perfect video I learnt a lot in 35 minutes. And I loved that background music perfectly tuned without distracting you!
Wow! Thank you so very much! Your tutorial is so easy to follow. I was able to create my first VBA Form ever! I was able to also debug all the errors and get the form working! This is my first attempt at writing a code. Thanks very much!
Hi Data Lab's
Am receiving an error "could not set the RowSource property. Invalid property value." When I click Debug it tells me error is encountered under Sub Show_Form. How can I correctly that?
Your help with be greatly appreciated.
@@solangegorogo5962 make sure your tab is called also Database. It worked for me
Really well explained, thank you for your time and effort.
Excellent tutorial! I was able to follow in real-time and easily adjust things to fit my requirements. Thanks so much!
Fantastic, thank you so much. I'm taking an VBA full course right now and, although, I just two days into the course I wanted to practice and learn a bit of code. This was the icing on the cake - thank you! and yes, I subscribed and did the bell thing.
Take care - Pedro
Thanks!
This tutorial has greatly been helpful to me and I really want to learn more about user forms and controls
Thanks for your feedback! Please visit our channel playlist. We have uploaded several tutorials on UserForm and data entry form. Thanks!
You never fail to amaze me.
Hi, is it okay to email you ? I Need to ask you something, thank you
I have to share, my complete appreciation for this. I have never EVER done this type of thing before, and now I have a working form!! SUPER EXCITED!! THANK YOU!!!
right from begining to end i learned something new and amazed with ur work. thanx buddy
Thank you for this info. Sir, could you make a video about checkboxes? Since it is very commonly used in forms.
I like these tutorials, they certainly gave me ideas for how I could improve my own userforms.
However, one minor issue/suggestion:
At 26:20 you should really consider making the ID dynamic, since you are using this ID in your next tutorials, and deleting one entry will break the ID sequence, causing subsequent misbehaviour when editing, adding or deleting additional entries. I am by no means a VBA expert, and there are probably more elegant solutions to this, but a simple fix would for example be:
.Cells(iRow, 1) = "=ROW()-1"
EDIT: Sorry, I see you already addressed this in part 4, but since dynamic ID/serial number is required for proper functionality in part 2 (Edit/Delete-buttons), it should have been included in part 2 of these tutorials, or even from the beginning (part 1).
may i ask if where does "Value: came from? i tried to copy the code but can't compile and it said method or data not found
Wow..you are a very patient teacher/tutor. Your explanations are so easy to understand. I'm learning quiet a lot from your presentations although I am starting to use Excel. Its so exciting and I love learning from you. Thanks so much all the way here from Papua New Guinea.
Great Presentation, my Tracking Call Database is up and running!! Thank you so much!
Greetings from Mexico, all your videos are really amazing. Thank you for sharing your knowledge in such a simple and friendly way.
Thanks for kind words and appreciation!
I hope you could do inventory management system using VBA. Sales, Purchase, Inventory using FIFO, Profit, etc. Thank you.
Great video! Maybe in your next few videos, you could explain a little more on the coding part. I've been looking all week for a simple video on how to do forms and this is the best I've come across so far 👍👍
Thanks for your feedback!
The tutorial was very informative and made simple by you
Please add more tabs and commands to make the database more refined
Do any of your videos provide coded examples of combo box objects with pull down list values derived directly from a worksheet table? It would greatly reduce the need for VBA skill level support if I didn't have to update VBA code every time a new LOV entry was required by a user. Ideally, as LOV entries are entered into a worksheet table, the form's combo box drop down LOV's should change in synch with the worksheet table. Is this possible? Do you have working examples of this code to share?
I need this too
Great step by step tutorial! you're very patient to explain each step. It's so clear. Regards from Mexico!
Thanks for your feedback!
Actually, this video is so clear, thank u very much .and this is GOAT " Grate Of All Time"
Muito bom. Adorei. Muito obrigada
Thats what I'm looking for
Thanks a lot for this wonderful tutorial. Can you show me how to avoid editing data within the database of this sheet? Thanks
Thanks for your appreciation! Please protect the sheet with password so that no can can edit data from database sheet. Unprotect the sheet at the time of transferring data and again protect with VBA code.
Thank you Mr Dilip, for a very detailed and explanatory video on creating database.
The best so far, for me.
In my field of work (Logistics) I need to place this form online for customers to fill it in.
And I need to find out from you how to generate a Customer ID when they fill the form.
They'll use it for tracking their order with us.
Thanks for your appreciation! You can use serial number as a Customer ID.
If your requirement is generic and applicable in logistics then we can post a tutorial on that so that other can also get benefits. Please share the detailed requirements on our email id. Thanks!
Best tutorial I have ever seen, so direct and hands-on approach anyone can be familiar with VBA form. Thanks for the generous sharing!
How can VBA form be used by multi users and data updated into excel form? Or is it limited to one user where the VBA is installed?
Do you know how can I insert only the "Date" and not the "Date + Time" as you've done using the function "Now"?
Please use Today() instead of Now() function. Thanks!
@@thedatalabs Woow, I'm so impressed with this channel, I can't believe it... you answered so quick, thank you, you've saved me.
@@thedatalabs I want to suggest you , can you show this - Next ,Previous, Change, Print
Hi, I am using now the same program you made in your tutorial... and I am thankful to have this kind of vba program through your initiative. It is very nice and I learned many things in your tutorial from Part 1 to Part 5. Now. I would like to ask you a favor if you could add vba codes for having three (3) option buttons to add in the "Gender" portion,
like adding one more option button for undecided. Thank you.
Were you able to get an update on this for 3 options buttons?
Love the way of simplicity yet clear information on excel vba with basic example.
Thank you for patiently showing the step-by-step vba coding. I was always afraid to use coding as I did not understand it at all.
Well done
Hello. Unfortunatelly the "Submitted on" does not work for me. I have "#value" in that cell. Not sure what I inserted incorrectly.
EDIT: Got it. My mistake.
In writing the codes, kindly explain first the logic and sequence of your approach and from there you breakdown your sequence then you translate to writing codes that will make you more a good teacher.
Thanks for your feedback!
Thank you very much. I developed an excel sheet with the help of your tutorial for my work.
Great tutorial! Very easy to follow. Thank you for posting it!
Thanks from pakistan.
I copy past you work on excel 2019.
Result on second form is not same. It shows only first line or first recording. Question is, how can i fix it?
Most welcome brother! Please download the Excel file from the same link. It will help you in understanding the code and issue. Let us know if you face any issues. Thanks!
you can replace the following code in Submit ()
iRow = [Counta(datasheet!A:A)] + 1
into
iRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row 'Find the last non-blank cell in column A
@@georgelaghmjian4367 hello im getting an error with ws its telling me "Variable not defined" im new to this is there some chance you could help please
@@stevedelsy3998 after sub write
Dim ws as worksheets
Set ws = sheets("datasheet")
Hi, when i call the reset function to the command button, it gives error " Invalid use of property"
can you please help
I got the same error. Any help would be great
Well done bro i have created my own software with your help for the first time
Thankyou for this video
This is cool. I've been learning python for the past couple years. This would make a nice addition to the toolbox. Coding in excel as well.
Hi this is brilliant! However when i click the launch form at the beginning it stops working and frmForm.Show highlighted with yellow. How to solve this one? Thanks
having the same problem
yes same did u solve it?
same problem too, did you solve it? @TheDataLabs please help on this
sorted iRow = [Counta(Database!A:A)] + 1 close bracket after A:A was missing. Now my form will not save aaargh
@@natashammarara7268 Can you please elaborate on that? I have changed it to "iRow = [Counta(Database!A:A)] + 1" but it still wont work?!
Whenever i set the lstDatabase Tab index to 10, it automatically get reset to 0 ! Why is this happening ?
Same thing here! Found any fix for this in the mean time?
@@pascal.visser No mate
@@Shashikant-kq1el Too bad. But I saw in the movie exactly at 15:30 that he fills in '10' as value behind 'TabIndex' but that it immediately changes back to '0' as soon as he clicks out of the screen. You have to watch very close because it all happens very fast. So I think that it isn't a problem because the form works at the end!
Thank you for replying anyways! :D Have a nice day!
I was able to come up with my very first automated data entry form by following this video! Thank you so much... Very clear to follow through
I finally can create a form in Excel!
Thank you! Such a thorough tutorial even someone who has little computer skill can follow.
Thank you very for the tutorial. But when i wanted to run i had this message "Run time error '13' , Type Mismatch". And also FrmForm.Show was in yellow colour .Please help me.
Thank you
Replace line with iRow = [Counta(Database!A:A)] + 1
Jacob, replace it with what?
Insert close bracket after A:A
thanks this helped
@@JacobWinton26 where to insert this. Im having same error
I am having an issue getting my form to display, when I try and open the form I get an error message number13 when I click Debug it highlights "frmForm.Show". Do you know how I am able to fix this, please?
This subprocedure is the procedure that should launch the form.
I hope your able to help me, I have been trying for two hours to work out what's happening.
Thank you for providing such an informative video.
I am getting the same error
Did you solve this error sir? How?
i have the same error too
I am facing the same issue. Pls help @theDataLabs
Same error here
Absolulety amazing tutorial thank you very much exactly what I needed
I wonder...can multiple user using that form in the same time??
I really want to see the 555 faces who disliked the tutorial, are they from another planet??
.. Btw superb work brother 🥰😊🥰
Perhaps Americans like myself who find Hindi English extremely difficult to understand. They speak too fast and words blend into each other. Sometimes the words sound like a foreign language. This is why I will click out because it is not fun to learn like this.
@@sandrab2422 imagine being non-english and non-hindi person. I have to understand and then translate this on my head to my native language.
@@andrusaren8348 definitely the wrong channel for you me and countless others who can’t understand this fast spoken incomprehensible English. He keeps getting clicks and making money off content that isn’t suitable for all. He should tag his content “Hindi English”
Thank you so much for sharing, much appreciate it!
You're the best there is. Thank you.
Someone told me that it's the best time to buy because there will be a huge pump
Someone told me that it's the best time to buy because there will be a huge pump soon little purchases is so surprising
I saw all this coming I had to switch to Crypto trading and I'm really enjoying the fact I made the right choice because of my weekly earnings
I make good income too from bitcoin trading and its been my most reliable source of income at the moment. With profits I've made so far I don't think i can get broke or lack anything.
How do you do it please
Because I've tried severally but seems not working
This tutorial is awesome. Thank you very much for this educative video.
I have never used VB with Excel but only with Access Database.
Now, I have found this tutorial to be an excellent tool to help my colleagues with data entry.
Glad to know you found it useful. Thanks!
Thank you soooo much!!! This was easy to follow and understand. It's like I learnt VBA in two days. Brilliant!
Excelente!!, se ve fácil con este paso a paso, gracias!
Great video, really simple to follow. I used this to create my own client entry file. Can you show me how to add an "add attachement" button ?
Best tutorial I’ve ever seen...no 🧢
Thank you very much.
You teaching in detail everything.
I know VB 6 very well but I didn't know the VBA procedure. Now I can work with this thanks a lot
Thank you for share the amazing work you create! I am not experienced in this but i followwd your steps
and build my first user form! I am so happy thanks ;)
Hi vanessa sellon please can you help me i washed and followed the amazing video but was unable to compile my code it pops an error message that reads thus, "Compiler error, Method or Data member not found" and highlights .Value = for .txt.value = "" it highlights this section and give the error message
excellent. I have seen so many videos to learn something but did not get exactly what I want. You are the person who has given full code for download.
Thank you very much for this video. I followed your steps carefully. I'm so happy I did it! I used it to encode the items we are getting from our cleaning supplies at home to keep track. very useful.
Damn, I don't even need this at the mment, but I'll be asking people if they need something like this. What an amazing tutorial.
Straight to the point!
Proper coverage of topic which was point to point . Only would request to also include brief explanations of what you are using throughout,, eg., How does "With" works, What is "With" used in vba code, etc. to get idea instantly about them
Your video is helping me take my Excel reports to the next level! Thank you so much! I'll be checking out your other videos as well.
Great video and was really helpful. Thank you.
I do have one question; I have a total of 13 columns in my database, but I can only see a total of 9 displayed in the bottom of the Automated form (2nd frame). Do you know how I can correct this. I used your code as it is.
Thanks for the great Tutorial. One question is that when hit the 'Save' button, the database window in the Data entry form only shows 4 columns instead of 9. Wondering if you know which part of code or set-up might go wrong to cause this problem. Thanks
Tx for this very detailed and informative video. In short I'm wanting to get my monthly expense budget and income which I recorded on excel using a few sheets with 1st a data input sheet and 2nd a combined sheet using vlookup's and if_commands to compile a one-pager and 3rd a sheet with lookup's to use as as unique dropdowns in my data input sheet and 4th a pivot from my data input sheet to calculate etc. My issue here is that doing it this way it is very manual and with time a lot of data so IN ALL I tried this automated data entry form as per your tutorial without any coding experience and to my surprise my first input page worked 100%, thank you. Still a long way to get all functions I need but I have subscribed and will do all your tutorials and sure I will find answers for all my requirements.
😍😍I like how he explain direct to point and actual teaching. On point. Please keep making videos😊
Thanks for your feedback!
This was very informative. I would like to thank you for posting it
Thank you very much! This has been really useful. Only issue, I added two date text boxes and once I save it it turns into the general format( 45678) I tried a different things to no avail, is there something I need to add/ change in the coding. Thanks so much for your reply.
Great tutorial ! I was wondering can you add more than a database into a user form? To put it in anothrer way, can you assign the labels from the same userform to two different worksheets/database?
Thanks for your feedback! Hope you will find the below tutorial helpful. Let me know if you defferent query. Thanks!
czcams.com/video/ehrGvarWqiY/video.html
Thank you very much for this man, new subscriber here, keep up the good work!
thank you, I learned a lot from this video.
this is so satisfying imma try this once i get home 😁😁
I appreciate your effort. salute from East Africa
Hello. I enjoy your VBA tutorial but have a question regarding the command buttons and making them functional. I have made numerous attempts to get the line under the "S" in Save and the "R" in Reset but to no avail. I put in "cmdSave" in the (Name) space, the lower case "s" in the Accelerator space, and label the Caption as "Save". Still, I cannot get the line under the "S" in the button. Any thoughts? Thank you.
Gud afternoon sir
The tutorial was very informative and made simple by you
Please add more tabs and commands to make the database more refined(Like Delete ,Search, Multple Search)
Thanks for your feedback! Please watch the complete series of this tutorial. You can find the link in description box and Pinned comment.
man that was really usefull thanks sharing the spreading to knowledge to go on.
Hi TheDataLabs,
Thank you for this video series. I have watched and worked with all 5 of them and are extremely useful for a beginner like me grappling with Excel VBA and User forms. Based on your suggestions, I have developed a user form with 29 fields.. Now the the form contains many Date and time fields. I am having trouble with 2 issues:
1) How to incorporate correct formatting of date (dd-mm-yyy) and time (hh:mm) with respect to codes in Part 1 of your video. I am using office 365.
2) Ho to fix/validate the format of the employee ID entered to EMP-0001, EMP-0002, EMP-0003 etc, the numbers part restricted between 0001-3500?
I have used the following stand alone codes for one of the date and time fields and I have written these in the frmForm (Not Module1). Is is there a better way to incorporate formatted date and time in your codes in Part 1? I have been struggling with this. A response will be much appreciated. Thanks!
'Formatting date
Private Sub txtDate1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If txtDate1.Text "" Then
If IsDate(txtDate1.Text) Then
txtDate1.Text = Format(txtDate1.Text, "dd-mmm-yyyy")
Else
MsgBox "Please enter a valid date!"
Cancel = True
End If
End If
End Sub
'Formatting time
Private Sub txtTime1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If Not Me.txtTime1 Like "??:??" Then
MsgBox "Please use format 'hh:mm"
Cancel = True
Exit Sub
End If
txtTimeSamp = Application.WorksheetFunction.Text(Me.txtTime1, "hh:mm am/pm")
End Sub
Like this I have other text boxes , txtDate2 and txtTime2, txtDate3 and txtTime3, etc
thank you very much i learn a lot of things there !!!
Thank you so much! You are extremely talented teacher
Good sharing
Thank you very much for such a nice tutorial. I'm actually new in learning creation of database.
Can I have a drill tutorials from use of signs and meaning of them so that I can understand well.
thanks for your video, that was really helpful and easy to follow, yes please make a video to show how to Edit and Delete Data, and also how to add data to a combobox from a worksheet list instead of adding through code.
Super helpful....had to play around with the cmb box to get it work.....this example when reopened doesn't show the selections unless I pressed reset. I had to put them above the "show form" line in the last sub prog
a big thumbs up to this also did hit subscribe 👍🏼
Thanks in a million. Great content. Awesome imagination. Grade: A++💥
Thank you for your helpful and effective tutorial with this automated Data Entry Form in Excel!
Wow! Thank you so much. Never imagined I could do it with the help of your tutorial.