Excel VBA UserForm | Add Data | Reset | Exit | Search | Update | Delete |
HTML-код
- Опубликовано: 21 июл 2024
- Namaste everyone. Thanks for your love and support.
In this video we will learn to make employee details dynamic form using Excel VBA.
Queries solved
1) userform add button
2) userform reset button
3) userform exit button
4) userform search button
5) userform update button
6) userform delete button
Download file link:
drive.google.com/file/d/1FVfU...
If you need any help, you can DM on my Instagram
/ sagar.banjade.5
For more such videos subscribe to my channel: / @sagar.banjade.5
#erpedia #excelvba #userform #step_by_step_tutorial
Thank You! Thank You! Faced with an unimaginable deadline, and at least 5 years since I last looked at VBA, I was able to follow your entire tutorial and re-produce the whole project and modify it to fit our needs. You made me look like a rock star! Thank you!!
you are legend brother. easy and straight to the point.
its a very good form to understand the beviour of VB. Time for understanding it, is vital. Thanks
A big thank you from bottom of my heart. Learned and enjoyed the video. Sir.
You are indeed a great teacher, the approach used to explain every bit of the video is superb thanks
First, thanks for the lesson, it helped me a lot.
Second, just a small thing, when you set the variable "lr" (Last Row), you writed "le", it work at the same.
Excellent work, Sir. Thanks from Sri Lanka.
Thank you for this video. Well explained and it gave me a lot of ideas. You did a great job.
I wants to express my profound appreciation to this super tutorial. Thank you Sir!
Excellent video and training style, easy to understand
time table :
@3:42 create the data result sheet in the output excel file
@6:32 creating the form
@16:38 putting option in the drop down list
@18:37 programing the save button
" coding the collection of text boxes input and export the data to excel output sheet and the table viewer in the form "
@22:50 the code for the programing the excel output sheet name and the error message box to warn the user there is a fault in the input
@24:05 adding the data to excel output sheet
@28:21 emptying the text boxes after the input is done
@32:29 programing the refresh data " the list box that show what data we put in the excel output sheet "
@37:37 programing the reset button
@38:07 programing the exit button
@40:12 programing the search button
@46:34 programing the update button
@49:55 programming the delete entry button
@52:21 programing the list box to show the output
@55:55 programing the button to show the form
Well done. It helped me to create my data entry form. Thanks
Thank you indeed for you generosity and patience in sharing this valuable knowledge, Sir. - From Burma
Excellent, Thank you so much.
Love your tutorials.
Made me a workbook with films and series I have seen. But...
When I search for a film/series I need to be exact in searchbar. I need to set uppercase and lowercase depending where I put it in the title.
I want to do the search with just lowercase. Is it possible to do that?
very nice tutorial.Thank you
sir, In the beginning, you did not state how you created the"MACRO" button to open up the data information form. It also would have been nice if I click to open this project it opens automatically the input form. is it possible to write a short script for the save option?
This was an excellent tutorial.
Appreciate😉
Thanks so much. i feel like a pro now
It's very useful to me thank you so much
Wow! Great Video
Awesome tutorial, crystal clear. You are truly a legend on the subject! With the delete control, the code is a bit longer, why not using the clear method as Me.Clear?? Just thinking louder! Before clearing it would be great to get a confirmation (MsgBox "Are sure you want to clear the record?", vbYesNo), If yes then proceed. At the end, as you run the macro to show the form and it overlaps with the data sheet, again, it would be great to get the data sheet hidden.
The best.
All in one
Thank you very much for your video
tqvm...this is very helpful...i have tried and it is working....just one thing, the search function only works when I entered the Emp.ID....other than that, the search function will not work...am I right?let say if I want to search by name...anyway..this video is great
nice information and it helped alot. Thank You.
When i double click in the listbox the info appears above except the date is a jumbled set if numbers and does not stay in format. How do I fix that?
thank you so much. this video helped me a lot.
Thank you very much for your video, but if you want to add more testboxes to the Userform, is there a way?
Nice dude and thanks
Thank you for your efforts ..
I faced a problem with saving data into a new row ..
Once I save data, it will go to replace the previous one and so on ..
How to solve it?
Thankyou so much for brilliant userform. But pls let me know how do I search data from multiple sheets. Regards
Thank you so much! this is amazing!
I have a small question, how can you prevent duplications? for example if you want to make sure that all employee IDs are unique, and in case you have entered a duplicated number, a message box appears telling you that and the rest of text boxes are cleared.
Please help.
Informative.
thank you mi pana!!! for posting
Sir. It was a well presented video. Thank you. Would u pls advise when I update my data, all check box information was changed from 1 to TRUE. But I want the result is shown in "1" instead of "TRUE". Thanks.
I can teach you this i am expect on this
Great and Easy
This is great job, thank you very much for the good work. I created my form and is working perfectly, please help me on how to add "Do you want to delete the data" vbYesNo before deleting. I watched many of your videos and I tried the code but it's not working. please help.
Great job
Excellent, thank you
Hi Er.Pedia,
may you can help on this how can coding for the duplicate value 1 record is already done but when i'm click on the Edit and after edit i will save again then it will capture duplicate value ? can you advice on this ?
Hi, I must say it's very impressive video it helped a lot to create. I have issue after creating everything I shared the excel with multiple people and at a time entry from many people will failing my excel entry data sheet. Please provide any solution for this
Very good Tutorial.. just what I need.. BUT, I'd liek the form to update a TABLE, rather than a simple Data array. what changes do I need to do to the Code
Simon
For that we need to make the table dynamic
Welcome Thank you very much Excellent and clear explanation I have a question I hope you can help me.
I have a problem that I could not solve. The problem is as follows (duplicate data): I have created a database in VBA. And I worked in it a button to search, a button to save, a button to query, a button to edit, a button to open a file, and a button to exit. The problem is that the data gets duplicated from the edit button. As for the data save button, I used code to not duplicate the data and it worked and no longer accepts any duplicates. The recurrence occurs from the modification button when I query the data of an employee who already exists (i.e. whose data has been previously entered), and I modify his data with the same data of another employee who is also present in the same database. It saves the modification and migrates the data to the sheet or database, the data duplication occurs and this is the problem I'm having.
I used the no-repeat code I used for the save button But it didn't work properly Please help me
Love u lot sir❤❤❤❤❤
This was an excellent tutorial. I was able to create my user form without any issues. Thank you very much! However, what if someone saves the Employee ID twice? You now have multiple lines with different information. How can you prevent this from happening?
We can avoid saving duplicate by applying validation. I have mention about duplicate in this video
ruclips.net/video/0ogv43DSzdo/видео.html
@@sagar.banjade.5 Thank you!
Great job, but how we can filter listbox as we type in a search box, pls if you can help me in the same user form in this video , thank you in advance
its great and i wuold be very pleased if i get file.
Tried the code....it's simple and working...
It requires modification for warning alert or duplicate data entry....
If you hit save ...it enters infinite entry to the database.
Also, search function in the list box not working...
Watch this video to learn more and I have mention about duplicate
ruclips.net/video/0ogv43DSzdo/видео.html
For search data by multiple ways
ruclips.net/video/CH9rtus7HLY/видео.html&ab_channel=Er.PediaEr.Pedia
Thanks for teaching. Can you teach how to create none duplicate ID in this form ?
Watch this video I have mention about duplicate
ruclips.net/video/0ogv43DSzdo/видео.html
Thank you for all the tutorials. They are educative.
I wish you please help me:
I have created a database in excel. And i have also created the userform.
The database that i created has sections all in one worksheet. The sections are: (1). staff information, (2). Students information and (3). Parents information.
I used the combo box to list the three sections.
So, i want to write a vba code to save staff information in the staff section if i select staff, and save students information under the student section if i select students.
I wrote this code below, but it's not working properly.
Dim sh as worksheets
Set sh=Thisworkbook.sheets("sheet name")
Dim lr as long
lr= sh.cells(rows.count,2).end(xlup). offset (1,0).row
for x=5 to 11
Because we only need 7 staff and the empty row starts from 5 to 11.
And then i said:
If sh.cells(x,2).value=staff then
sh.cells(x,2).value=txtname.value
So on...
For students:
For x= 14 to 100
If sh.cells(x,2).value=students then
sh.cells(x,2).value=txtname.value
And so on
When i use this code even though it saves the information under the required section, but the information will occupy the entire section.
Please help me out sir.
All of what i did here, i learned most from your tutorials. I always download your tutorial.
Great Tutoring. With the delete function, once the delete has been executed its done. Just wondering if a a message can be created to ask if you are sire you want to delete with a Yes or No BEFORE its deleted ??
thank you very much sir
Hi, great video. Is there a way to input the data by default as "Unknown" in the fields where there's no data?? Thanks in advance.
yeah, we have to use enter and exit event. To know detail about it you can watch my Student Registration Userform video.
ruclips.net/video/0ogv43DSzdo/видео.html
You can watch this video from 36:00
Thank you sir 💯
A very informative video. Thanks for this sir.
Will you please make a form for sales invoice which is very imported and the fact is that even I am not able to find it in RUclips for almost 7 months.
If you could please make a video for sales Invoice form it will be so kind of you.
Thanks
Thanks for the positive review. I will try to make that video.
Your tutorial is awesome. But I have a problem. In combo box of department, if I enter a value which is not available in the drop down list, I need a message that this item does not exist in database or row source and do you want to add it to database. If I say yes and add the item in the database, it should appear in the drop down list. And the message box should disappear and save the data. How can it be done? Thanks for your help.
Yes i added the validation…. Y
Thank you so much
Can you add an entry field, so after each new line is entered it counts up. This makes searching for information easier if there are similar dates with different information.
yes, we can easily add that too. I will soon make the video
@@sagar.banjade.5 it would also be helpful to have a filter as you text option on the search field or dependent combobox
Awsome video….how to send search data through outlook kindly advise
I would like a video on using Date field in the VBA form?
Thank you.
Thanks for your excellent explanation.... Can you help me..specific date wise data entry. If i select 12-08-2023 date but today date is 18-09-2023.. In that time the data will save this specific day. (12-08-2023).
Teşekkürler Türkiye den Selamlar
hi I tried your tutorial and everything works except the update part that is wrong, yet I wrote it identical the values do not update, could you have a solution?
When i convert the range to table, and delete a record with reference no. 1 and add the same record again then it doesnt show in the excel sheet and also it doesnt show in the listbox, but when i apply any advance filter then same data shows up, how to fix it? the data must be shown without applying advance filter every time.
Thanks for the great video. How do you trap duplicate employee ID when adding new user ?
Great work 👍🌹
Thank you
@@sagar.banjade.5 plz send me the practice file to my below email :
Tawfeeq_alsrori@yahoo.com
Plz send me the practice file
@@tawfeeqalsrori9092 Do check yr mail
plz send me the file suraj57sam@gmail.com
how do I change the coding for the search function to search data from any field?
Sir, could you please create a video for creating a large and separate VBA form at once? This training provides a large data entry format, but after running it, the full format doesn't show due to the inability to expand the zoom. Could you demonstrate how to zoom in and out to fill the black space?
how come when you click the (32:17) listbox and delete the code, it shows the refresh data? thank you
excellent
Awesome video. Anyway I could get a practice sheet?
For that I need yr gmail id
I Like this
Nice
Quick questions of adding filters:
1) if someone wants to arrange the excel sheet based on dates or any filter. How to code it ?
2) if we want to add filters in list box for visual how to do that..
very good toturial.. can i get copy
Sir, I tried your form and made an error somewhere, I can only see the Userform in draft mode?????? how do I fix this? would you be able to assist
Super
Good day! Upon Checking for the Search cmdbttn, using the same format, only the the ID can be use in searching bttn, other than ID like name and gender cannot be use for searching bttn. How to properly search using other detalis like gender, emaill add an so on? thank you in advance
In this line If Sheets("Worksheet").Cells(Y, 1).Value = txtsearch.Text Then after the Y, 1 change the value corresponding to the column ie Y, 2. One other trick is right up the top of your coding sheet, type option compare text This disables case sensitivity when searching.
Sir, Many thanks for uploading such a video. Sir, I am facing problem in the statement as lr=Sheets("Worksheet").Range("A"&Rows.Count).End(x1Up).Row
and also I couldn't not catch you how you have brought Regresh() in the code.
Please help me by let me know the exact procedure to solve the matter. Thanks once again.
I'm also encountering an error on this part. How did you resolved it?
@@louiejayparas5349 just put (xlUp)...previously I typed "1" instead of "l" (small letter 'l'). Thanks.
instead of lr put le. cause you declared le not lr.
i have a Problem if i press on Save it will save in ecxcel but on the first raw, then wenn i but new data in vba and press on Save it will delet the old won and replaced it with the new won? it will notput automatikli in the new raw now it just replaced the old with the new won. What can i do to chnage this?
Hello brother, can you put this project in a link so that I can download it?
Thanks
I want a code like this but to color the row that has a condition with a nother color when update data in update command button
Your tutorial is nice, but I am unable to get Cells option in command With sh and next row .Cells. Please guide me
👍👍👍☺️☺️☺️
🥰😍
If I have 2 of the same employed id, how can I show the 2. as findnext example pls or what ?
ID must be unique in order to search
Hi this is great. Can I please get the latest file that you have for this. Thanks , much appreciated
I am really sorry for the late reply brother. I was busy few months. Still, if you need the file dm yr email ID on my Instagram
Sir, please upload a segment of code for preventing duplication of the code while adding a new record to the database.
I have another urnest request to you for uploading another segment of code for searching record by entering a partial code i.e. "10.." for "101", "102" "103" & "20.." for "201","202","203" etc. Thanks. Lot's of love & respect to you.
hope this may help you
If Application.WorksheetFunction.CountIf(sheet.Range("B:B"), Me.textbox.Value) > 0 Then
MsgBox "This ID already exist please enter new ID", vbCritical
Exit Sub
End If
This will be input in wha settings?
@@hoselleotajale1293 where to add this code?
Hello. what is the code if i want to search not just in the employee ID? Like for example i didn't knoe the Employee ID but i know the name. Please help thank you
How to retrieve data without searching. Say, i double click the data from tge listbox - is this possible?
Hello sir plz tell me how to set color in male or female word in combobox and list box
Dear Sir,
Trying to run your "Employee Details Dynamic form" which looks great, however an error showed up.
It said:
"Error 2147352571 (80020005)
Cannot set up the characteristic (reference) ColumnWidths. Type doesn't match"
Looking in the code where the Error showed up, this line came up highlighted:
.ColumnWidths = "80,140,70,130,100,150,80,80"
Hope the can tell why and what to do.
Thank you in advance
why i click save it not drop the data after column name ? i do as the same yours
I have a doubt. When I write code for the Save button, there is a run time error"9" appearing. Please help me to troubleshoot this. I am a beginner in Excel VBA
Without viewing code I can't say anything.
You can watch my next video. Its easy and simple
ruclips.net/video/0ogv43DSzdo/видео.html&ab_channel=Er.PediaEr.Pedia
If you still get problems, message me on Instagram.
Hi. I have copied this exactly as in the video. I always get an error at last row line. Is there more to the code that I am missing? Can you send the full code? This is exactly what I need. Thanks
Would you please drop yr email?
Wao many thanks did you sharing the download please, the link its break
Print and export to pdf
Code in this form please tell me sir
Hi, is it possible to have a copy of the file please