How to: Create a simple Userform in Excel

My first tutorial showing how to create a simple user form using excel. Thanks for watching.
The code needed for the "add details" button:
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")
'find first empty row in database
iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
'check for a Name number
If Trim(Me.textbox_name.Value) = "" Then
Me.textbox_Name.SetFocus
MsgBox "Please complete the form"
Exit Sub
End If
'copy the data to the database
ws.Cells(iRow, 1).Value = Me.textbox_name.Value
ws.Cells(iRow, 2).Value = Me.textbox_surname.Value
ws.Cells(iRow, 3).Value = Me.textbox_age.Value
ws.Cells(iRow, 4).Value = Me.textbox_gender.Value
MsgBox "Data added", vbOKOnly + vbInformation, "Data Added"
'clear the data
Me.textbox_name.Value = ""
Me.textbox_surname.Value = ""
Me.textbox_age.Value = ""
Me.textbox_gender.Value = ""
Me.textbox_name.SetFocus

[@MoonbowDew]

Thank you, Dude, Thanks a lot!!! I haven't been sleeping all night looking for tutorial in VBA programming. Your example is exact what I need, you saved my life! I'm not gonna drop out!!!

[@roseperlora1378]

+Scootalovich help me with my codes scootalovich ...

[@vinceallistergonzales2955]

back in and recently got soft soft again, it felt strange and i had previously just taught myself the software. Finding your videos is helping

[@t0chique67]

after browsing through so many channels. Yours is by far the best. The explaining thod is so great and detailed even complex stuff is

[@Malikahmad950]

Awsome-My Problem Solved. I have Created a small accounting Software using this thing. Amazing. God Bless You man.

[@tigistg.fantaye7636]

This is an AMAZING way of entering data. Love Excel!!!

[@maddywilliams8065]

The best video I have ever watched thank you my dude

[@nishantmichaelchander9449]

Excellent video. Clearly explained all the basics to get started on soft soft. Thanks

[@shankarganeshacharya4272]

Thanks SP Tutorials. Videos says a lot.

[@jacquelinehyde6851]

This is very wonderful, and informative.

[@bauer1969olivish]

Thank you very much for sharing with us .Many thanks

[@SPTutorials1]

Ah thanks for pointing that out! and no problem seems like you got it under control anyway

Oh my god so good explained thank you!!!!

[@joedez454]

Thanks for the video!

[@muhammedkareem321]

Hi Komal Khalid, u fixed the issue... thnx a ton.. ;)

[@Johnmoments]

ur vid helps a lot. thanks^^

[@The-Instructor]

Can I use this File afterward in an IPad ?

[@VAULTERMAGAZINE]

I have the button on worksheet one and the database on two. I would like the text to be entered starting on C13. How do I make the data start in that cell C13?

[@haroldgaldones6238]

What ribbon is it at 5:04? mine is not yet visible. im pretty much learning from scratch here.

[@SolarSpecDesign]

Thanks for the tutorial. Had to remove the underscore and then everything worked perfectly. Can I easily change the code for the 'Add Details' button so that the form overwrites the same cells? E.G. I have a template that I need to be able to update data, not create new entries. Thanks.

[@ricardaskr]

Thank you, thank you very much ;) Nice

[@karima_MK]

Other question: how would be the code for a scroll box. My miskake (previous question) could be this. Thank you

[@SPTutorials1]

ive never seen that error when i was doing these userforms... I tried looking around for a solution on google but i cant find anything. You could try re-entering the code and trying again? Other than that im not really sure... I'll keep looking but if you find out let me know!

[@softsuhn]

thank you tNice tutorials is really helpful and an incredibly done tutorial for beginners

[@zuhaibmzk]

Hats off to this tutorial . thanks a lot. But I have a Question, I want to Insert the data within a Table. Tell me the codes to do so and especially adding corresponding empty rows in the table according the requirement. thanks again

[@arrozyadifalaqi9292]

I'm using the trial version of soft to learn the basics. How do I make a cool keyboard lody that isn't just a basic softing soft?

[@remcovaneijzeren6990]

Works perfectly. But how can i make it work that the program search for a empty row. from collum A to K. So i can putt my buttons at the side of the sheet

[@lee215]

How do I activate the button "Add Details" on the spreadsheet? The mouse icon doesn't let me push the button it keeps trying to edit it.

[@invaluablenz]

I replace the faulty line with and it finaly worked - iRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

[@markblade8482]

did you solve the problem syntax error iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _ SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1

[@sunnyandstevehunt2083]

iRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row Works : ) , thanks heaps Note: when you have pasted in the code make sure that there are no extra spaces

[@markblade8482]

thanks Steve H

[@khanmalik3690]

553939

[@bauer1969olivish]

Thanks Mine worked too.Many Thanks

[@SPTutorials1]

Thanks for helping out.. i couldn't find anything on it haha. Hope you helped him

[@ScottUlmer]

Great video, the only issue is could you either turn off or turn down the clicks? Also you can think about making a new one since the forms options are different in 2010.

[@shillaavancena3291]

tnx for the video :D

[@khalidmohamed6705]

thanks for the help but i have problem when ever i try to save data it works but the first cell in my booksheet it show a symbol hash ######## how i can solve the problem

[@SPTutorials1]

Thanks a lot! :)

[@gopinathramarao4494]

only click sound are heard. Request to add the audio of the steps in progress Gopi

[@gaylawyatt7129]

When you are add the code you do this: View Code and the copy/past from the description. I cannot find the description icon (or whatever the icon is on the task bar you click to present actual code). How do I find the description?

[@_AbuIbrahim]

works!! thanx

[@tajeracampbell5207]

Do you know what code to use for resetting or submitting. I'm building a system for a salon and I've been having so much trouble with it. I followed your instruction but my buttons are submit and reset and I don't know the code for that. does the same code work???

[@harshjadiya6680]

can u give me a PDF file of code or word file of code I can't copy and paste it

[@jitma777]

From where did you copy and paste the code from the description? Please help

[@nkowk]

Excellent. I used it. Please continue to share. I give you my greatest encouragement. Keep it up.. !!! :o)

[@ssdusd]

Thank you, so what about if I have dropdown list for example Gender Male Female how can I make dropdown list in the userform?

[@karima_MK]

Hi, Thank you for your video. Now I have one question. I used your code and modity it to adapt my own User form but I have an issue. A message pop up saying "can"t find project on Library". The first sentence is highlighted "Private Sub Cmdbutton_Add_Click()" And curser is placed on the word "Trim" in the sentence: " If Trim(Me.textbox_Name.Value) = "" Then" I give you code, if one of you here have any idea where should be the problem. Thank you !!!! Basically my form has about 18 values with scroll boxes and text boxes. One botton add and on leave, very much like your in the video but just mores entries. ================================================================== Private Sub Cmdbutton_Add_Click() Dim iRow As Long Dim ws As Worksheet Set ws = Worksheets("Contact") 'find first empty row in database iRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row 'check for a Name number If Trim(Me.textbox_Name.Value) = "" Then Me.textbox_Name.SetFocus MsgBox "Please complete the form" Exit Sub End If 'copy the data to the database ws.Cells(iRow, 1).Value = Me.textbox_Status.Value ws.Cells(iRow, 2).Value = Me.TextBox_lastupdate.Value ws.Cells(iRow, 3).Value = Me.textbox_Company.Value ws.Cells(iRow, 4).Value = Me.textbox_Activity.Value ws.Cells(iRow, 5).Value = Me.textbox_Position.Value ws.Cells(iRow, 6).Value = Me.TextBox_Category.Value ws.Cells(iRow, 7).Value = Me.textbox_Topic.Value ws.Cells(iRow, 8).Value = Me.textbox_Code.Value ws.Cells(iRow, 9).Value = Me.textbox_language.Value ws.Cells(iRow, 10).Value = Me.textbox_Title.Value ws.Cells(iRow, 11).Value = Me.textbox_Name.Value ws.Cells(iRow, 12).Value = Me.textbox_Surname.Value ws.Cells(iRow, 13).Value = Me.textbox_Address.Value ws.Cells(iRow, 14).Value = Me.textbox_Suite.Value ws.Cells(iRow, 15).Value = Me.textbox_City.Value ws.Cells(iRow, 16).Value = Me.textbox_Postalcode.Value ws.Cells(iRow, 17).Value = Me.textbox_Province.Value ws.Cells(iRow, 18).Value = Me.textbox_Country.Value ws.Cells(iRow, 19).Value = Me.textbox_Phone1.Value ws.Cells(iRow, 20).Value = Me.textbox_Phone2.Value ws.Cells(iRow, 21).Value = Me.textbox_Email1.Value ws.Cells(iRow, 22).Value = Me.textbox_Email2.Value ws.Cells(iRow, 23).Value = Me.textbox_Fax.Value ws.Cells(iRow, 24).Value = Me.textbox_Website.Value ws.Cells(iRow, 25).Value = Me.textbox_Facebook.Value ws.Cells(iRow, 26).Value = Me.textbox_Twitter.Value ws.Cells(iRow, 27).Value = Me.textbox_Blog.Value ws.Cells(iRow, 28).Value = Me.TextBox_Comments.Value MsgBox "Data added", vbOKOnly + vbInformation, "Data Added" 'clear the data Me.textbox_Status.Value = "" Me.TextBox_lastupdate.Value = "" Me.textbox_Company.Value = "" Me.textbox_Activity.Value = "" Me.textbox_Position.Value = "" Me.TextBox_Category.Value = "" Me.textbox_Topic.Value = "" Me.textbox_Code.Value = "" Me.textbox_language.Value = "" Me.textbox_Title.Value = "" Me.textbox_Name.Value = "" Me.textbox_Surname.Value = "" Me.textbox_Address.Value = "" Me.textbox_Suite.Value = "" Me.textbox_City.Value = "" Me.textbox_Postalcode.Value = "" Me.textbox_Province.Value = "" Me.textbox_Country.Value = "" Me.textbox_Phone1.Value = "" Me.textbox_Phone2.Value = "" Me.textbox_Email1.Value = "" Me.textbox_Email2.Value = "" Me.textbox_Fax.Value = "" Me.textbox_Website.Value = "" Me.textbox_Facebook.Value = "" Me.textbox_Twitter.Value = "" Me.textbox_Blog.Value = "" Me.TextBox_Comments.Value = "" Me.textbox_Name.SetFocus End Sub Private Sub Cmdbutton_Close_Click() Unload Me End Sub Private Sub ListBox1_Click() ComboBox1.List = Worksheets("Sheet1").Range("I1:I50").Value End Sub Private Sub ComboBox1_Change() End Sub Private Sub ComboBox_status_Change() End Sub Private Sub textbox_Company_Change() End Sub Private Sub Title_Click() End Sub Private Sub Topics_Click() End Sub Private Sub Topic_Click() End Sub

[@733DVH]

Thank You!!!!!!!!!!!!

[@andyt968]

good video,but what code do i need to add if i want more text boxes and label boxes. ie to enter more info in the database? thankyou in advance.

[@sarveshkoyande3583]

Thankyou!!

[@91svampebob]

Awesom-O :D

[@rashidimam343]

hi, i want to ask one thing regarding userform. i have created userform on my excel file, its working properly. i can add data easily. but after closing my excel file where i have entered my data and created the userform, when re-opening the same file for more data update, the userform is missing and showing some error, and not able to add more data with the help of that user form, i am very sure that i am not saving the file properly, So, Plz will you help me that how can i save that file correctly? and can update my data in future with same userform which i created.. thanks.....

[@vegasvato55]

When i create a user form in excel 2010 for my spread sheets, the next time i open the spread sheet application i can not find the user-form I had previously created in order to edit or modify the form??? I am saving it as a macro-enabled work book, so what am i doing wrong??? HELP!!!!!!

[@ltprbs_9952]

Legend says he's still not back..

[@sentient94]

When you say copy and paste code from the description, which description do you mean (4:34)? I got lost there!

[@peterkonecny3530]

Hey, How to edit the code if I want to enter just number in the age section ? I want the form tell me to put age in number format if I try to insert a word. Thanks

[@VonDrakenstorm]

Very useful video, but there is a typo in the code in the description. In the section where you look for the next empty row, there is a '_' before SearchDirection. Replace it by : iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1

[@pentium1636]

Thx. Very useful!

[@dal3553]

VonDrakenstorm Thank you sir...

[@N7eptune]

+Dallas Kimber Thank you it worked a treat. I replaced the two lines with your statement. The line-continuation character [space followed by the underscore character _ ] may have worked but I had another error anyway.

[@lmichelle9505]

If you are having some challenges with following along, just watch the video training first, then go back and watch again. This time, each time you see an instructional command box, pause and complete the task, then unpause the video until you see the next instructional command box

[@williamyoung6662]

Slowing it to half speed as well helps!

[@taraksood]

Hi, I cant see any code description to paste...got lost. Can u please help? And thanks for the easy version for us dummies!!Cheers

[@archerkhing19]

Thank you thank you thank you..

[@SPTutorials1]

Where you are putting "Irow=ws.cells" try putting a space before and after the = sign so it becomes Irow = ws.cells...?

[@SPTutorials1]

Hi, sorry i had the same problem when i was trying to get it to enter my data into a specific row... I couldnt find a way to set a range to enter the data into and i was quite new to excel and havent used it in a while. Someone more experienced may be able to help as ive googled it so many times and cant find the right way to do it

[@SPTutorials1]

There are some sites that teach you but you can pick up some of it the more you use certain codes and what they do. But i only know pretty basic stuff and i haven't used excel a lot since high school... I wouldnt say theres a trick or technique, just how you like to learn, the same as anything

[@laurenl9921]

I am getting a compile error on 'find first empty row in database iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _ SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1

[@SPTutorials1]

Hi, sorry for the late reply. KZbin hasnt been notifying me of any comments etc.. Underneath the video where the description is, if you click "show more" it will show the code. You can copy and paste it from there

[@adhuffman9]

The pause button at the top of the VB editor page is activated. You'll have to click the little green arrow next to the pause button in the code editing page before the code will run.

[@sambar9203]

what is code from the description? can you show details this code

[@syedibrahim4012]

Thank you.

[@preethifotos]

Thanks to YOU

[@THILLAIRAJA24]

Thanks a lot....

[@anhkhoavu6365]

If the data you input can not save to the sheet. You should change the sheet's name is sheet1 according to the code or you can change the sheet's name in the code provided.

[@ianrafisura8577]

i cant see the code,..help me please,,..im working on ms excel 2010.Thanks

[@shivaaher3280]

Hi, as you said in tutorial for add command button method is "Copy and paste code from the description"? Please shear the code for add the data? I am new for this thinks.

[@neilmiller384]

Hello, this is great and really works well! I have a question - is there any way to have the button on Sheet 1, and send the info to Worksheet 2? Thanks for any help anyone can offer. I can't really write code but I am ok with some troubleshooting/debugging.

[@magiadecine]

Neil Miller I had the same problem, what i do is i put the button on sheet 1 and write the following code in the button thisworkbook.sheets("The Sheet Where You Want The Data").select Your userform name.show This works, the only thing is the other sheet will be shown at some point. There are probably more ways, but i don't know any other.

[@mathijsderuiter5151]

It is because the _ stands for a new line. You should make sure that the area "....xlRows, _ SearchDirection...." is like this: "...xlRows, SearchDirection..." or like this: "...xlRows, _ SearchDirection...". I hope this helps you!

[@Computeraidedautomationcom]

please add delete button also (if possible edit buttons)

[@komalkhalid]

Also can you please tell us how to autosave this?

[@jayeetaguha6627]

Thanks bro

[@habiburrahmanmeraj1862]

An error message showing "Cant Execute the code" how can I solve this bro ?

[@doubleburst9630]

Yes . . . I agree with Krukhaug. What Description do I copy?????

[@SPTutorials1]

Sorry, im not sure what you mean by link it to table database?

[@candyman41able]

Being a beginner, trying to do this without voice is useless. Why no voice mate?

[@geraldvinuya9237]

good day sir, what is the code for column? bec i want to put a word in a column for example A1,A2,A3 etc. please kindly teach me . thanks and more power

[@mjfan19100]

where i can find the description??

[@MetalBeast666m]

Very good.. but i have a problem.. i can't insert a photo from my webcam, for every employee registration. Any suggestion?

[@farzinfarzi4236]

Thank you

[@dinjumathew]

I am using 2010,Unable to view Forms in toolbar for ticking

[@khemrajrana7322]

copy and paste the code from the description? but where is the description button? plz rply sir?

[@sarahwalsh3714]

Can anyone help me? I am a microsoft 2013 user and I am new to Excel Forms. I have followed all the instruction in this informative video and i am nearly there, however i am getting stuck at the last hurdle. At around 6.31 when i have checked the code for the "add details" command button, i close the screen and go back to the main database. when i click the add contact again and then further select "add contact" within my form i have an error message that takes me back the other formula page which says "syntax error". All the code looks correct but its highlighting the top section which says Private Sub cmdbutton_addcontact_Click() (to note my button says add contact rather than the video suggestion add details) What am i doing wrong?

[@Amigwyn75]

This title is misleading. This is not an instruction on how to create user forms ... it is how to copy/paste. This leaves a lot of steps out that are required for creating a userform for practical use, IMO.

[@YouriNoteborn]

Someone with a link from Microsoft Visual Basic? Thnx!

[@WeltonMadureira]

I am trying this video on my database and for some reason i can not get going. The form stops with the sign "Compile error - Syntaxe error", the line what starts with "Private sub..." gets Yellow and the line starts with "Irow=ws.cells...." gets blue, looks like is showing me where are the Syntax erro i just don't know how to fix.Please help!! Tks. Phil

[@prasadshete2639]

sir mere laptop per ye userform ban nahi raha hai mai kya karu ...??

[@nursyazwani4175]

Are you using ms excel 2010? because i'm stuck at the button selection on excel sheet just now. mine is 2007.

[@anonymoussepie4986]

How did you do that clicking sound? O.o

[@sunguhan20]

hmm how about if i not start the 1st row... what the code ... then how about if i search the 1st name or last name?

[@danbrad95]

same i also have an error on the first row, "iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _ SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1"

[@MrLimpsy]

my 'add detail ' button doesnt show up form that did earlier. when my curser hover it, it doesnt turns into a 'fingerpoint' cursor

[@rajapurplace3853]

Alt+F11 will be open

[@jangafr]

Hi all! Very good! But at 4:30 you say: "...code from description" Where is that description? I can't find that description. I don't have the icon your mouse points to. I use Excel 2007. Can you point me to that description? Thx in advance!

[@RooferRad]

Dim iRow As Long Dim ws As Worksheet Set ws = Worksheets("Sheet1") 'find first empty row in database iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _ SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1 'check for a Name number If Trim(Me.textbox_name.Value) = "" Then Me.textbox_Name.SetFocus MsgBox "Please complete the form" Exit Sub End If 'copy the data to the database ws.Cells(iRow, 1).Value = Me.textbox_name.Value ws.Cells(iRow, 2).Value = Me.textbox_surname.Value ws.Cells(iRow, 3).Value = Me.textbox_age.Value ws.Cells(iRow, 4).Value = Me.textbox_gender.Value MsgBox "Data added", vbOKOnly + vbInformation, "Data Added" 'clear the data Me.textbox_name.Value = "" Me.textbox_surname.Value = "" Me.textbox_age.Value = "" Me.textbox_gender.Value = "" Me.textbox_name.SetFocus

[@jobsindubai9889]

Daer Jan here is cod as belwo: Dim iRow As Long Dim ws As Worksheet Set ws = Worksheets("Sheet1") 'find first empty row in database iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _ SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1 'check for a Name number If Trim(Me.textbox_name.Value) = "" Then Me.textbox_Name.SetFocus MsgBox "Please complete the form" Exit Sub End If 'copy the data to the database ws.Cells(iRow, 1).Value = Me.textbox_name.Value ws.Cells(iRow, 2).Value = Me.textbox_surname.Value ws.Cells(iRow, 3).Value = Me.textbox_age.Value ws.Cells(iRow, 4).Value = Me.textbox_gender.Value MsgBox "Data added", vbOKOnly + vbInformation, "Data Added" 'clear the data Me.textbox_name.Value = "" Me.textbox_surname.Value = "" Me.textbox_age.Value = "" Me.textbox_gender.Value = "" Me.textbox_name.SetFocus

[@guid70]

Rad Roofer I copied and pasted it and it worked, but there is a slight problem. When i pressed Add Details in the Entry Form box, I am getting an error saying Compile error: Method or data member not found which prompts me to the line like this If Trim(Me.textbox_name.Value) = "" Then and i get a yellow arrow that highlighted the words Private Sub Cmdbutton_add_Click (). What am I doing wrong? I am using Windows 8.1 that include Office 2013 Private Sub Cmdbutton_add_Click() Dim iRow As Long Dim ws As Worksheet Set ws = Worksheets("Sheet1") 'find first empty row in database iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1 'check for a Name number If Trim(Me.textbox_name.Value) = "" Then Me.textbox_name.SetFocus MsgBox "Please complete the form" Exit Sub End If 'copy the data to the database ws.Cells(iRow, 1).Value = Me.textbox_name.Value ws.Cells(iRow, 2).Value = Me.textbox_surname.Value ws.Cells(iRow, 3).Value = Me.textbox_age.Value ws.Cells(iRow, 4).Value = Me.textbox_gender.Value MsgBox "Data added", vbOKOnly + vbInformation, "Data Added" 'clear the data Me.textbox_name.Value = "" Me.textbox_surname.Value = "" Me.textbox_age.Value = "" Me.textbox_gender.Value = "" Me.textbox_name.SetFocus End Sub Private Sub Cmdbutton_close_Click() Unload Me End Sub

[@RawatGagan]

Please see this kzbin.info/www/bejne/qou8n5KkhNeMp9E

[@SPTutorials1]

Then why are you watching videos on how to create something you think is worthless? Glad you enjoyed it :)

[@alejandrosejas]

Excellent video, but im getting an error in the line "iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1" please can someone help me?

[@amokiemichael1771]

nice tutorial.. me i have a code for leave ..exact total for all leave? plsss

[@Bramantyozk]

how about if i'll protect the sheet so no one can change the database but the form still can input the data?

[@magdieladames7233]

Zefanya Kusaka Bramantyo no you will have to unprotect and then protect the sheet within the macro

[@somenextdouche]

since there are only 2 gender, why not a combo box? and how would I apply that?

[@aallaljee8358]

"find empty in database this paragraph in " compailar error ati hai solution do yar for two line ke lia