Merry Christmas everyone. I hope God blesses you richly this upcoming year. Remember, you ARE a success!! Believe it, be it! Blessings my friends!! Dan
Amey, very good point. The reason one may loop through and use the .AddItem is merely to customize the combobox on the fly or filter it for whatever reason. Thanks for your comments, have a wonderful day!! Dan
If I may, I can only suggest that you can reference the list for combobox just by typing the name of a Named Range you've created into combobox properties, search for option called: RowSource. Thanks to that you can skip the bit of code responsible for building up the list of items for combobox while initializing UserForm. You'll get the same AutoComplete effect. I love your work by the way.
Absolutely fantastic point, Lukasz! Lots of pros for doing it that way, it is very fast and easy. The only thing you lose is the ability to pick and choose which columns and which records you want to include easily. But I do love using RowSource or ListFillRange. Thanks for the comment!! :)
Hi, I love your video! It helped me a lot, but I can't get the final step to work! Whenever I attempt to use the form I get Run-time error '1004: Application-defined or object-defined error.
You should be able to use the .SetFocus of any control. So at whatever point you want to set the focus to that combobox, just say something like this: cmbListItem.SetFocus And SHAZAM! You're done! Thanks James! Dan
Clear, right speed, simple example explanation, thanks! Although, in my example I run into a bug at the line where we search for the next empty row. "application-defined or object-defined error". I can't solve this...
Finally someone showing Autocomplete the way i wanted but i have a question how to use autocomplete with custom list in normal excel cell. Please do let me know.
Hi Dan, This was a world of help in a sea of issues I was coming across, thank you! With this however, I can not seem to get my date to auto-fill AND I can not seem to get my data to go to the next row, it continues to overwrite my last line. Please advise.
Many Thanks. The Video is very easy to understand and very helpful. My question is: Can I make a Userform with different list items which has different items as well? So once I enter list item name and I will get a chance to choose items which comes under list items entered in the first list box... Many thanks
Hello and thank you, i would like ask you: with combox1 It is possible make the a opposite as in this video so, choose the text in cells and full the combox?
Hi i need help in creating a data entry form with macros. when i enter reference number in part no start time should automatically come and when i click end it should end. can you help
Hi Dan, Great video but most of userform are entering the data 'horizontally' including in your videos the Title is in A1, B1 and C1, what if the data need to be entered 'vertically' for example Title are in A1, A2 and A3, how the VB statement when the Submit button is clicked ? Thanks in advance
This is great. Just what i've been looking for. Just one question: is there a way to make the search function in the combo box look for more than the first letter(s)? I'm building a list of a few thousand ingredients and would like someone to be able to type in, for example, "roma" instead of "tomato, roma", or if they typed in "grape" they would get "grape, red" "grape, green" "tomato, grape". This way they can search whatever is natural to them and not have to worry about specific nomenclature. Any help would be greatly appreciated. I've look all over for an answer to this. Thanks.
Great tutorial. I followed it and it works perfectly. Please bear with my question as I'm a novice working with userforms. How can I get the data entry form to show up on the worksheet upon opening the file without opening the VBA editor and hitting F5? I made a button that gets the userform up using userform1.show, but wanted to get around this by running the userform after opening. Thanks
Hello ! I have an Excel form with two text boxes. The first must display the result in figures of an operation; The second must display this same but in letters. I have the function of translating numbers into letters. But I do not know how to take me so that a click of a control button can simultaneously operate the operations. Thank you for helping me!
Hi, Thanks for the video. Just one question - having followed your instructions to the letter, for some reason the name of the dynamic range does NOT appear in the spreadsheet window that lists named ranges (near the top left), and is not (apparently) recognised as a named range. I've noticed that in the define name window, the "Workbooks" is ghosted out - is there some Excel setting that's preventing recognition of the named range? It does however, appear in the Name Manager listing.
Hi Dan, i have written the code but the data keeps on going to the same row after entering and submiting new data. and kindly provide the link where i can learn how to make the boxes disapear when i click the sbmit button. Great stuff
If this is a combobox on worksheet it may have different verbage from a userform combobox, but virtually the same. 1. Make sure there isn't a conflicting range listed in the combobox's ListFillRange or RowSource (depending on userform vs worksheet). 2. Excel sometimes gets fussy when you use evaluate brackets like this, other times it's fine. In that event, I try using the exact pin-pointed location, eg: For each art in Thisworkbook.sheets("Sheet1").range("ListaArticulos") etc. . . Dan
Hi Beau, if you had a second userform control, you can put it on say column 4 or 5. ex cells (x,4) = "Hello this is column 4" cells (x,5) = me.textbox2 'where x is the current row
First time doing VBA and managed to sort of do this. Have a problem with where you kept the list and where the data entry ended up (my data ended up on the bottom of my list - LOL). Looked like you were going to name the list and then didn't. I am quite used to naming lists on another sheet for references for dropdowns etc. I also need to work out how to save it and run it. The form reminds me of doing things in Lotus Approach. Perhaps I will look up some more Vlogs on VBA but any help would be appreciated. Thank you
Thank you! What would be this best way to write form data into a table? Instead of just the next available row like in this tutorial. I can't quite get it right.
Thanks Dan, love the entire series. It is very generous of you to share your knowledge. I have one request and that is going back to the very basics being creating a user form that simply has some text fields that will be placed into a specific sheets next row. You have indeed gone through quite a range of topics, and I did try to place together the code from a couple of your videos (which I might add worked for me), but when opening it on other computers just errors any code I try.
well, you can increment the row by using a for - next loop or you can simply increment your row counter variable by adding one to it. (x=x+1) each time. Let me know if I can help you further. If you have any code you're messing with, I can take a look. Thanks!! Dan
Hi Dan, I have made a simple spelling error in the naming of a TextBox. This is now working 100% thanks for the willingness to help. Great work, and have a supernatural year of 2015!
At 4:56 the user form "Me" appears, but nowhere in the video was the user form previously named "me". In fact, I can see in the corner of the display it is still called Userform1. Where did "Me" come from?
hi treav as always your tutorial is awesome and very helpful... hope you can upload a tutorial about making a userform that add data on the designated worksheet...
Hi Dan, Thanks for all of the help! I love the way you set the date to today's date. Any way to set it so it doesn't update the date but the input HAS to be a date. I don't want users typing in "Mar-3/2015" and my date formula's giving an error because of this. Thanks again!
I get an error on the "me.date = date" when the end user is using an older version of excel. I have 2010, my users have 2007. How can I change the userform initialise script to late binding?
Great tutorial ! but got a little problem. Mine has a run-time error '438' 'Object doesn't support this property or method'. how could I fix it? thanks
good video, i'm trying to make a form that the data i put in, will clear after 15 or 30 days its it possible?? for example I put x on 3/14/2014 x will delete on 4/14/2014.
Suppose instead of a single selection drop down list, I wanted to use a multi-select list just in case the user needs to make more than one choice (e.g. signing out inventory). How would this be done? Also, is there a way to populate a dynamic list with a userform? Thank you!
Thanks for the video, its just what I was looking for. I managed to get it going up to the very last step. When I press the submit button to enter the data I get a run time 424 error. Other than the destination sheet being "Sheet2" all else is just as in your example. What would be causing the error?
Timothy Humphery OK i know what it is. When using the dot-End (.End) method to find the end of a row or column in one direction or the other, use either xlUp, xlDown,xlToLeft, or xlToRight. Make sure it's XL, not X1. Use the letter 'el' (L) not the numeral one (1). So, instead of this: nr = ssheet.Cells(Rows.Count, 1).End(x1Up).Row + 1 TRY THIS: nr = ssheet.Cells(Rows.Count, 1).End(xlUp).Row + 1 Thanks, Dan
hey i have made a similar kind of project but this function of entering the data into the next empty row is not working on it can u plz help in this regard.
Hi Dan, really good and informative instructions, made a small project I am working on so much easier for data input.Are you able to advise if this will work by converting the Excel worksheet into a Table and if so what is the code. When I tried it placed the new records at then very end of the blank records and not the first row of the table.Thanks if you can offer assistance. SteveDim ssheet As WorksheetSet ssheet = ThisWorkbook.Sheets("EAU_Data")nr = ssheet.Cells(Rows.Count, 1).End(xlUp).Row + 1
Hi there, How do I create a macro to pull information from outlook emails and insert them into excel. We have a website that customers enter their email address, name, surname, company name and admins name. All that info gets sent to my inbox with the relevant Form Data. All emails are the same except the information they input differ. I want to pull that information and insert it into excel in the following columns Company name I name I surname I email address I administrator I want all new data to be inserted in a new row in the same excel workbook. How do I go about using a macro to d this. Please help.
Can anyone help me . I have combo box and text box for data entry. Is there any way when i select item listed on combo box and enter anything on textbox would go to the sheet where i want to put. But combo box will only find the item on the sheet of what was being selected . Onlythe text will be entered once selected item found
Hello, thanks for the video and need help to enter date in the form without typing slash symbol. Ex: if i type only the number slash should be default for the date format mm/dd/yyyy. can you please help me
If I want to use this for on server then How many people can access this form at a time ? If 40 to 50 people are accessing this form at a time then will this code work ?
I think all of you tutorials are very helpful for us newbies on excel. I do have to ask one thing, "After clicking the button, how do we make the cursor default to the beginning or first selection on the userform with out using the mouse?. Thanks in Advance.
Hey mate great video. Just a quick question- For listbox, how would you adapt your code to have multiple selections passed back to a single cell seperated by a comma back in your worksheet.
I love your videos! Keep up the great work! Thanks for your time.I am fairly new to Visual Basic and I am going through one of your Udemy classes now. You declared a sheet in this video, I would like to use a table and reff the columns. I use Power Query to up date data and it would seem like making a declaration to a table would be more versatile for dynamic data. So how would you use a table instead of a sheet?
Hello friend and thank you for this video, I am having an error when trying to run part of this code, more specifically with the submit button, the error I get says "Wrong number of arguments or invalid property assignment" I´m not sure what´s going on, I hope you can shed some light over here...
for current workbook data entry use set ssheet = thisworkbook.sheets("Sheets1") How about if the data entry for different workbook. How the code would be?
="sally" Why the "sally" The code written as below but it doesn't work. Where is the mistake Dim ws As Worksheet Dim addto As Range Set ws = workbooks ("c:/test folder/othrworkbook.xlsx").sheets ("sheet1") Set addto = ws.Range("a50000").End(xlUp).Offset(1, 0)
great video series. I want to use a UserForm to populate items in a cel range. How can I get the userform to populate starting at a specific place and then go down 1 after each entry? You mentioned another video, but having a link to it would be great. I havent been able to find it yet (the one where you define nr= etc.. Thanks again for great work!
Hi, Can you make a userform button to send into a data? example: i want to create 1 to 37 buttons, and if i press button-1, it will send number 1 in sheet A:1? and if i press button-15, it will send and add 15 into the seet A:1 (just below1)? and if i press button-36 it will send and add 36 into sheet A:1 (just below 15) and so on... 1 15 36 . . .
I am trying to set up my submit button and I keep getting Run Time Data error 9 on the following code. It is highlighting the "Set ssheet" row when I debug. Can you help me troubleshoot? Private Sub btnsubmit_Click() Dim ssheet As Worksheet Set ssheet = ThisWorkbook.Sheets("sheet2") nr = ssheet.Cells(Rows.Count, 1).End(x1up).Row + 1 ssheet.Cells(nr, 1) = Me.Cmbstaff ssheet.Cells(nr, 2) = Me.Cmbposition ssheet.Cells(nr, 3) = Me.Cmbkeyfob ssheet.Cells(nr, 4) = Me.TBkeyid ssheet.Cells(nr, 5) = Me.Tbdescription
Sure thing. Check out my Worksheet events playlist for more info on the Workbook_Open feature. Anything you put in this will run as the wb opens. Great question!! Dan
@ExcellVbaIsFun If you Type a string into the combo box that isnt in your list is it possible to send that new string to the list. Im stuck on this. I mostly use python or C so Im new to Vba. Is the above possible?
Mark O'Driscoll Yes. There's always a way to do most things. Depends how your combobox is populated - by listfillrange, (or rowsourse if userform) or is it by using .ADDITEM ?
Good Morning Dan i use the same code, and i inserted the second value, in the combobox, the first value was replaced for this second value. I dont know why because when i inserted the second value, didn't increment to the next row. Can you help me please ? Best Regards
ExcelVbaIsFun Hi Dan I send the code in another message, but already find the problem, and i solve him. The macro run without any problem, Thanks for the video and for the availability. Best Regards
Hi thanks for the tutorial it's awesome but I'm having one problem so far and I was wondering if you could help. I looked in the comments and don't see anyone else with the same issue. When my userform runs initializes in the sheet with my list there is no problem but when my userform runs in the sheet where I want my data to populate I get "Run-time error '13': Type mismatch". I was also wondering if there was a way to initialize a text box to add 1 to the previous text box so that it could keep track of the log I am making. I realize that everything in the text box is a string and that why I'm kinda stuck.
Hi there, I love the tutorials and am having the same problem with the Type Mismatch. I have checked everything 1000 times and can't see to get around it. Did you get an answer for how to resolve this? Any help would be so appreciated. As I feel so close to finishing..
Hey guys, I see a theme building here. Please send me what you're having trouble with and I'll address the issue online on a special 'troubleshooting' UA-cam video for you. Thanks for letting me know. send here: excelvbaisfun@gmail.com
The Combobox remains in US format whilst once the UK date exceeded 12 the Excel sheet date went from US to UK. (It may revert back when the next month starts.) If I write over the date it is fine but that sort of defeats the purpose of the whole purpose of the exercise (or should I say exercize).
dan, Hopefully you can help me with this error i'm getting. It reads: --- compile error: variable not defined ----. it highlights the nr =, in the next row sentence.
Hi!, how can I set it in such a way that it will automatically select or return the cursor to list Item after entering submit button?.. please help!!!... Thanks..
Francis A Yes, you can use .Setfocus eg: me.cmbStuff.SetFocus and that will return you to that item. if you need the cmb to return to a value then me.cmbStuff = myValue Dan
I keep getting Run-time error '1004'.. I wrote the code exactly like yours but with addition of a new variables and changing their name. Please help me, what caused it?
Bro I try to do your steps and now I am on the submit button.. I wrote the code but when I run it I fill the info and then when I click submit it does not run it and say run time error 9. Subscript out of range. What should I do my man?
Hi Simon, If you use .End(xlup) method, it will start from the bottom most cell in column A and keep searching for the next available non-empty cell. So this should work unless you have anything non-empty below this area: lastRow = cells(rows.count,1).end(xlup).row nextRow = lastRow +1 Hope that makes sense Thanks Dan
This is a nice tutorial, however, I just can not get the List1 range named. I have tried it over and over and over, and it just does not appear in the range list. It seems not to exist
I'll bet it's in there, but hiding a bit. Try this: press Alt, I,N,D to open the Names Manager. See if it's in the Names Manager, but perhaps not visible in the names dropdown list? That's where I usually find the ones that are problematic.
Awesome! Keep up the great work Steve Hedemann . Don't forget! for a few more days you can get my brand new course "Barcoding in Excel VBA" for just $19 at this special coupon link www.udemy.com/barcodes-excelvba/?couponCode=JULY14 instead of retail $49. God bless! Dan
Thanks for the video, its just what I was looking for. I managed to get it going up to the very last step. When I press the submit button to enter the data I get a run time 424 error, object required and bedug the 3rd line{Set ssheet = thisworkibook.Sheets("Sheet4")} What would be causing the error? what shoud i do? this in my coding Private Sub bttnSubmit_Click() Dim ssheet As Worksheet Set ssheet = thisworkibook.Sheets("Sheet4") nr = ssheet.Cells(Row.Count, 1).End(xlUp).Row + 1 ssheet.Cells(nr, 1) = Me.tbRef ssheet.Cells(nr, 2) = Me.cmbMonth ssheet.Cells(nr, 3) = CDate(Me.tbDate) ssheet.Cells(nr, 4) = Me.cmbName ssheet.Cells(nr, 5) = Me.cmbItem ssheet.Cells(nr, 6) = Me.cmbPurpose ssheet.Cells(nr, 7) = Me.tbReceivedamount ssheet.Cells(nr, 8) = Me.tbPaidamount ssheet.Cells(nr, 9) = Me.tbTransferamount ssheet.Cells(nr, 10) = Me.cmbPaymentmode ssheet.Cells(nr, 11) = Me.tbCheque ssheet.Cells(nr, 12) = Me.cmbBankname End Sub Private Sub UserForm_Initialize() Me.tbDate = Date 'this is for cmbMonth 'row source and write the name of named range 'this is for cmbName 'row source and write the name of named range 'this is for cmbItem 'row source and write the name of named range 'this is for cmbPurpose 'row source and write the name of named range 'this is for cmbPaymentmode 'row source and write the name of named range 'this is for cmbBankname 'row source and write the name of named range End Sub
It's either something missing or a typo, can I see your code. If it's too big, maybe put it on our facebook page. Also, you can download the sample workbooks by clicking the dropbox link in the description. Dan
Hi Dan, I have been scouring the web and your videos for a solution to a problem and I have come up empty. I am sure there is a simple solution. I have a spreadsheet that tracks license numbers and expiration dates for 50 states. The spreadsheet is 23 ‘fields’ wide by 50 ‘records’ high. I would like to edit (via text boxes on my UserForm) some of the fields periodically when I invoke the UserForm and choose the state in a combo box. Thanks to your help, everything is working famously except being able to edit the text boxes. Can you shed any light on this issue? Any help would be greatly appreciated! This is one of many lines code I am using to populate the data points when I select the State (CA, AZ, AR, NY, etc.) from the dropdown. Me.tbLicNotes = Application.WorksheetFunction.VLookup(Me.cmbState, [Master], 13, 0)
Hey Dan, I've resolved this issue so please don't spend any time on it however a "PutData/GetData" video may help some of us if you haven't already done one. I also used a tb change event to trigger, works great!
andreikilla "Me" technically represents the object in which you are writing the code. So in this case, "Me" represents the form - however if you type "Me" on code found in a sheet for example, "Me" represents the sheet. Same goes for classes and modules!
bro your video are more simple and straight to point . good job man
Thank you, Soumitra!
Merry Christmas everyone. I hope God blesses you richly this upcoming year. Remember, you ARE a success!! Believe it, be it! Blessings my friends!! Dan
the free ebook link did not work :( but thanks for the vid
Nice an clear Mr Dan :)
Do you find the latest version of MS Office Excel VBA still compatible with the syntax or are there serious changes?
So sorry, please email me excelvbaisfun@gmail.com. Dropbox is dropping the ball for me lately.
Still works. They mainly add to VBA when new excel features come out, rarely remove any existing features. Thanks
Dan
this video is tha bomb. i mean seriously, i now have a basic grasp of how to use VB and i have a data entry form of drop downs!!!!
Wow! Thanks Louisa! So glad it helps!
Amey, very good point. The reason one may loop through and use the .AddItem is merely to customize the combobox on the fly or filter it for whatever reason. Thanks for your comments, have a wonderful day!! Dan
If I may, I can only suggest that you can reference the list for combobox just by typing the name of a Named Range you've created into combobox properties, search for option called: RowSource. Thanks to that you can skip the bit of code responsible for building up the list of items for combobox while initializing UserForm. You'll get the same AutoComplete effect.
I love your work by the way.
Absolutely fantastic point, Lukasz! Lots of pros for doing it that way, it is very fast and easy. The only thing you lose is the ability to pick and choose which columns and which records you want to include easily. But I do love using RowSource or ListFillRange. Thanks for the comment!! :)
To open the Names Manager - hit the ALT button, then hit I,N,D. So Alt+I+N+D, but don't hold anything down, just press them in order. Thanks! Dan
Oh! Had another look and sorted it out. Runs like a dream! Thank you.
Hi, I love your video! It helped me a lot, but I can't get the final step to work! Whenever I attempt to use the form I get Run-time error '1004: Application-defined or object-defined error.
Christian Thomas same error for me
+lenoy wiliams same here. Any fixes?
Hi. Thanks for the NYC vid. Sir how can we attach two text box lists to each other for controlling wrong data feeding . Linking two lists of data.
You should be able to use the .SetFocus of any control. So at whatever point you want to set the focus to that combobox, just say something like this:
cmbListItem.SetFocus
And SHAZAM! You're done! Thanks James! Dan
Clear, right speed, simple example explanation, thanks! Although, in my example I run into a bug at the line where we search for the next empty row. "application-defined or object-defined error". I can't solve this...
Finally someone showing Autocomplete the way i wanted but i have a question how to use autocomplete with custom list in normal excel cell. Please do let me know.
it's amazing how much I learned in only a few short minutes! Thanks!
You are so welcome, teachmeautocad! Glad this helps you learn quickly. Dan
Hi Dan,
This was a world of help in a sea of issues I was coming across, thank you! With this however, I can not seem to get my date to auto-fill AND I can not seem to get my data to go to the next row, it continues to overwrite my last line. Please advise.
thanks nice video. please what is a the code if i want to enter the date into sheet 2, 3 and 4 using different user form
Many Thanks. The Video is very easy to understand and very helpful.
My question is: Can I make a Userform with different list items which has different items as well? So once I enter list item name and I will get a chance to choose items which comes under list items entered in the first list box...
Many thanks
Thanks in a million. Great content. Awesome imagination.Grade: A++💥
Glad you liked it, John!
Hello and thank you, i would like ask you: with combox1 It is possible make the a opposite as in this video so, choose the text in cells and full the combox?
Hey, Awesome tutorial!!! What hot key did you use to open up the New Name window?
Hi i need help in creating a data entry form with macros. when i enter reference number in part no start time should automatically come and when i click end it should end. can you help
Hi Dan,
Great video but most of userform are entering the data 'horizontally' including in your videos the Title is in A1, B1 and C1, what if the data need to be entered 'vertically' for example Title are in A1, A2 and A3, how the VB statement when the Submit button is clicked ?
Thanks in advance
is there a way to have it add more items (from the user form to the auto complete list) if those items are not there yet?
This is great. Just what i've been looking for. Just one question: is there a way to make the search function in the combo box look for more than the first letter(s)? I'm building a list of a few thousand ingredients and would like someone to be able to type in, for example, "roma" instead of "tomato, roma", or if they typed in "grape" they would get "grape, red" "grape, green" "tomato, grape". This way they can search whatever is natural to them and not have to worry about specific nomenclature. Any help would be greatly appreciated. I've look all over for an answer to this. Thanks.
Great tutorial. I followed it and it works perfectly.
Please bear with my question as I'm a novice working with userforms. How can I get the data entry form to show up on the worksheet upon opening the file without opening the VBA editor and hitting F5?
I made a button that gets the userform up using userform1.show, but wanted to get around this by running the userform after opening.
Thanks
Hello ! I have an Excel form with two text boxes. The first must display the result in figures of an operation; The second must display this same but in letters. I have the function of translating numbers into letters. But I do not know how to take me so that a click of a control button can simultaneously operate the operations. Thank you for helping me!
Hi, Thanks for the video. Just one question - having followed your instructions to the letter, for some reason the name of the dynamic range does NOT appear in the spreadsheet window that lists named ranges (near the top left), and is not (apparently) recognised as a named range. I've noticed that in the define name window, the "Workbooks" is ghosted out - is there some Excel setting that's preventing recognition of the named range? It does however, appear in the Name Manager listing.
Hi Dan! Your videos have helped me greatly.
Hi Dan, i have written the code but the data keeps on going to the same row after entering and submiting new data.
and kindly provide the link where i can learn how to make the boxes disapear when i click the sbmit button.
Great stuff
If this is a combobox on worksheet it may have different verbage from a userform combobox, but virtually the same.
1. Make sure there isn't a conflicting range listed in the combobox's ListFillRange or RowSource (depending on userform vs worksheet).
2. Excel sometimes gets fussy when you use evaluate brackets like this, other times it's fine. In that event, I try using the exact pin-pointed location, eg:
For each art in Thisworkbook.sheets("Sheet1").range("ListaArticulos")
etc. . . Dan
What changes would I need to make in order to allow for multiple entries in a single userform? Any guidance on the changes to the code would be great!
Hi Beau,
if you had a second userform control, you can put it on say column 4 or 5. ex
cells (x,4) = "Hello this is column 4"
cells (x,5) = me.textbox2
'where x is the current row
First time doing VBA and managed to sort of do this. Have a problem with where you kept the list and where the data entry ended up (my data ended up on the bottom of my list - LOL). Looked like you were going to name the list and then didn't. I am quite used to naming lists on another sheet for references for dropdowns etc. I also need to work out how to save it and run it. The form reminds me of doing things in Lotus Approach. Perhaps I will look up some more Vlogs on VBA but any help would be appreciated. Thank you
Thank you!
What would be this best way to write form data into a table? Instead of just the next available row like in this tutorial. I can't quite get it right.
Thanks
Dan, love the entire series. It is very generous of you to share your knowledge.
I have one request and that is going back to the very basics being creating a user form that simply has some text fields that will be placed into a specific sheets next row. You have indeed gone through quite a range of topics, and I did try to place together the code from a couple of your videos (which I might add worked for me), but when opening it on other computers just errors any code I try.
Hi, is it possible to make a command button multi-functional??? I really need help with this!
well, you can increment the row by using a for - next loop or you can simply increment your row counter variable by adding one to it. (x=x+1) each time.
Let me know if I can help you further. If you have any code you're messing with, I can take a look. Thanks!! Dan
Hi Dan, Im in dire need of help with my code. Keeps hitting me with a "Run-time error '9': Subscript out of range." Please can you assist.
Kyle Kloppers Hi Kyle,
Would you share the offending code with us? Many thanks!
Dan
Hi Dan, I have made a simple spelling error in the naming of a TextBox. This is now working 100% thanks for the willingness to help.
Great work, and have a supernatural year of 2015!
Which screen recorder have you used?
excelente trabajo profesor
At 4:56 the user form "Me" appears, but nowhere in the video was the user form previously named "me". In fact, I can see in the corner of the display it is still called Userform1. Where did "Me" come from?
Never mind... I just found out "Me" refers to the parent object in VBA. It isn't user defined.
hi treav as always your tutorial is awesome and very helpful... hope you can upload a tutorial about making a userform that add data on the designated worksheet...
hey so i made my userform and i have a ton of data that won my 30th scan i cant see anymore is there any auto scroll option?
I have a question. Why i cannot run the code. It shows run-time error ‘424’ object required
Hi Dan,
Thanks for all of the help!
I love the way you set the date to today's date. Any way to set it so it doesn't update the date but the input HAS to be a date. I don't want users typing in "Mar-3/2015" and my date formula's giving an error because of this.
Thanks again!
which your video show how to get next row (nr)? Please help me...
I get an error on the "me.date = date" when the end user is using an older version of excel. I have 2010, my users have 2007. How can I change the userform initialise script to late binding?
Hi, Can you create a command button that has two functions e.g. Submit and Clear?
you said you can program that after the submit button is hit you can program it to delete its previous entry, how so?
Great tutorial ! but got a little problem. Mine has a run-time error '438'
'Object doesn't support this property or method'. how could I fix it? thanks
good video, i'm trying to make a form that the data i put in, will clear after 15 or 30 days its it possible?? for example I put x on 3/14/2014 x will delete on 4/14/2014.
Suppose instead of a single selection drop down list, I wanted to use a multi-select list just in case the user needs to make more than one choice (e.g. signing out inventory). How would this be done?
Also, is there a way to populate a dynamic list with a userform?
Thank you!
J Wandy yes it should be possible, I have a userforms video and will be working on selecting multiple items from userform list box. Video coming soon.
Thanks for the video, its just what I was looking for. I managed to get it going up to the very last step. When I press the submit button to enter the data I get a run time 424 error. Other than the destination sheet being "Sheet2" all else is just as in your example. What would be causing the error?
I dunno, can I see your line of code that brings the error? Yours, Dan.
ExcelVbaIsFun
the submit button code is as follows;
Private Sub btnSubmit_Click()
Dim ssheet As Worksheet
Set ssheet = ThisWorkbook.Sheets("Sheet2")
nr = ssheet.Cells(Rows.Count, 1).End(x1Up).Row + 1
ssheet.Cells(nr, 1) = CDate(Me.tbDate)
ssheet.Cells(nr, 2) = Me.cmbsection
ssheet.Cells(nr, 3) = Me.cmbtitle
ssheet.Cells(nr, 4) = Me.tbfname
ssheet.Cells(nr, 5) = Me.tblname
ssheet.Cells(nr, 6) = Me.cmbposition
ssheet.Cells(nr, 7) = Me.tbsdate
ssheet.Cells(nr, 8) = Me.tbfdate
ssheet.Cells(nr, 9) = Me.cmbemployment
End Sub
the error line is the "nr = ssheet.Cells(Rows.Count, 1).End(x1Up).Row + 1" line.
Thanks
Timothy Humphery OK i know what it is. When using the dot-End (.End) method to find the end of a row or column in one direction or the other, use either xlUp, xlDown,xlToLeft, or xlToRight. Make sure it's XL, not X1. Use the letter 'el' (L) not the numeral one (1). So, instead of this:
nr = ssheet.Cells(Rows.Count, 1).End(x1Up).Row + 1
TRY THIS:
nr = ssheet.Cells(Rows.Count, 1).End(xlUp).Row + 1
Thanks,
Dan
ExcelVbaIsFun
That does the trick. Thank you Dan!
I will be sure to check out some of your other tutorials too.
Cheers,
Tim
no prob!
hey i have made a similar kind of project but this function of entering the data into the next empty row is not working on it can u plz help in this regard.
can i see your wb? excelvbaisfun@gmail.com, if I can help, may I post a youtube video so I can help the YT community? Dan
Hi I have one doubt whenever we enter the data in userform how to convert that data in directly xml format file without excel sheet
Thanks in advance
Hi Dan, really good and informative instructions, made a small project I am working on so much easier for data input.Are you able to advise if this will work by converting the Excel worksheet into a Table and if so what is the code. When I tried it placed the new records at then very end of the blank records and not the first row of the table.Thanks if you can offer assistance. SteveDim ssheet As WorksheetSet ssheet = ThisWorkbook.Sheets("EAU_Data")nr = ssheet.Cells(Rows.Count, 1).End(xlUp).Row + 1
Thank u sooo much for sharing the video!!! :D I was desperate trying to fill the comboBox!!
Hi there, How do I create a macro to pull information from outlook emails and insert them into excel.
We have a website that customers enter their email address, name, surname, company name and admins name. All that info gets sent to my inbox with the relevant Form Data.
All emails are the same except the information they input differ.
I want to pull that information and insert it into excel in the following columns
Company name I name I surname I email address I administrator
I want all new data to be inserted in a new row in the same excel workbook.
How do I go about using a macro to d this.
Please help.
Can anyone help me . I have combo box and text box for data entry. Is there any way when i select item listed on combo box and enter anything on textbox would go to the sheet where i want to put. But combo box will only find the item on the sheet of what was being selected . Onlythe text will be entered once selected item found
Hello, thanks for the video and need help to enter date in the form without typing slash symbol. Ex: if i type only the number slash should be default for the date format mm/dd/yyyy. can you please help me
Please see this new video I made for you: ua-cam.com/video/pOGk3J0JIhU/v-deo.html
Thanks
Dan
If I want to use this for on server then How many people can access this form at a time ? If 40 to 50 people are accessing this form at a time then will this code work ?
Goodwork. please could you explain the use of "nr" . Code does not work for me
Sure thing, lemme see your code please and I'll try to help. Thanks!
Dan
I think all of you tutorials are very helpful for us newbies on excel. I do have to ask one thing, "After clicking the button, how do we make the cursor default to the beginning or first selection on the userform with out using the mouse?.
Thanks in Advance.
if it's a textbox for example Textbox1, use this. me.Textbox1.SetFocus
Thank you so much, just that simple code made my work 100x easier. Now no need to look at the screen while entering my data.
So glad that helped, Graciano!!
Please can you explain what I am to replace 'blah' with? As you do not define what it is! Thanks
Hey mate great video. Just a quick question- For listbox, how would you adapt your code to have multiple selections passed back to a single cell seperated by a comma back in your worksheet.
Hi Cameron,
Please be on the lookout for a new video to address your question. thanks!
Dan
here is the link: ua-cam.com/video/7fVLbCRyXEI/v-deo.html
Thanks
Dan
I love your videos! Keep up the great work! Thanks for your time.I am fairly new to Visual Basic and I am going through one of your Udemy classes now. You declared a sheet in this video, I would like to use a table and reff the columns. I use Power Query to up date data and it would seem like making a declaration to a table would be more versatile for dynamic data. So how would you use a table instead of a sheet?
Thanks so much for making these videos. Yes I do watch mikes videos.Amazing guy and amazing videos.
Hello friend and thank you for this video, I am having an error when trying to run part of this code, more specifically with the submit button, the error I get says "Wrong number of arguments or invalid property assignment" I´m not sure what´s going on, I hope you can shed some light over here...
for current workbook data entry use
set ssheet = thisworkbook.sheets("Sheets1")
How about if the data entry for different workbook. How the code would be?
workbooks ("c:/test folder/othrworkbook.xlsx").sheets ("sheet1").range ("a1") = "sally"
Thanks!
Dan
="sally"
Why the "sally"
The code written as below but it doesn't work. Where is the mistake
Dim ws As Worksheet
Dim addto As Range
Set ws = workbooks ("c:/test folder/othrworkbook.xlsx").sheets ("sheet1")
Set addto = ws.Range("a50000").End(xlUp).Offset(1, 0)
great video series. I want to use a UserForm to populate items in a cel range. How can I get the userform to populate starting at a specific place and then go down 1 after each entry? You mentioned another video, but having a link to it would be great. I havent been able to find it yet (the one where you define nr= etc.. Thanks again for great work!
Hi, Can you make a userform button to send into a data?
example: i want to create 1 to 37 buttons, and if i press button-1, it will send number 1 in sheet A:1?
and if i press button-15, it will send and add 15 into the seet A:1 (just below1)?
and if i press button-36 it will send and add 36 into sheet A:1 (just below 15)
and so on...
1
15
36
.
.
.
I am trying to set up my submit button and I keep getting Run Time Data error 9 on the following code. It is highlighting the "Set ssheet" row when I debug. Can you help me troubleshoot?
Private Sub btnsubmit_Click()
Dim ssheet As Worksheet
Set ssheet = ThisWorkbook.Sheets("sheet2")
nr = ssheet.Cells(Rows.Count, 1).End(x1up).Row + 1
ssheet.Cells(nr, 1) = Me.Cmbstaff
ssheet.Cells(nr, 2) = Me.Cmbposition
ssheet.Cells(nr, 3) = Me.Cmbkeyfob
ssheet.Cells(nr, 4) = Me.TBkeyid
ssheet.Cells(nr, 5) = Me.Tbdescription
Sure thing. Check out my Worksheet events playlist for more info on the Workbook_Open feature. Anything you put in this will run as the wb opens. Great question!! Dan
@ExcellVbaIsFun If you Type a string into the combo box that isnt in your list is it possible to send that new string to the list. Im stuck on this. I mostly use python or C so Im new to Vba. Is the above possible?
Mark O'Driscoll Yes. There's always a way to do most things. Depends how your combobox is populated - by listfillrange, (or rowsourse if userform) or is it by using .ADDITEM ?
Good Morning Dan
i use the same code, and i inserted the second value, in the combobox, the first value was replaced for this second value. I dont know why because when i inserted the second value, didn't increment to the next row. Can you help me please ?
Best Regards
Rui Prata Hi can you show the code? Thanks!
ExcelVbaIsFun
Hi Dan
I send the code in another message, but already find the problem, and i solve him. The macro run without any problem, Thanks for the video and for the availability.
Best Regards
Hi thanks for the tutorial it's awesome but I'm having one problem so far and I was wondering if you could help. I looked in the comments and don't see anyone else with the same issue. When my userform runs initializes in the sheet with my list there is no problem but when my userform runs in the sheet where I want my data to populate I get "Run-time error '13': Type mismatch". I was also wondering if there was a way to initialize a text box to add 1 to the previous text box so that it could keep track of the log I am making. I realize that everything in the text box is a string and that why I'm kinda stuck.
excel sometimes has issues on adding with textboxes. Check out my video here: SUM of two or more Textboxes and Tricks to Automate This - Excel VBA
Hi there, I love the tutorials and am having the same problem with the Type Mismatch. I have checked everything 1000 times and can't see to get around it. Did you get an answer for how to resolve this? Any help would be so appreciated. As I feel so close to finishing..
Hey guys, I see a theme building here. Please send me what you're having trouble with and I'll address the issue online on a special 'troubleshooting' UA-cam video for you. Thanks for letting me know. send here: excelvbaisfun@gmail.com
Did you have any luck with the type mismatch troubleshooting. I couldn't get mine to work either
oh, also, on a worksheet Activex combobox, use the ListFillRange instead of RowSource, but they work the same I believe. Thanks again!
I still can’t figure out how to make the text box default to date.
The Combobox remains in US format whilst once the UK date exceeded 12 the Excel sheet date went from US to UK. (It may revert back when the next month starts.) If I write over the date it is fine but that sort of defeats the purpose of the whole purpose of the exercise (or should I say exercize).
dan,
Hopefully you can help me with this error i'm getting. It reads: --- compile error:
variable not defined ----. it highlights the nr =, in the next row sentence.
Hi!, how can I set it in such a way that it will automatically select or return the cursor to list Item after entering submit button?..
please help!!!... Thanks..
Francis A Yes, you can use .Setfocus eg:
me.cmbStuff.SetFocus
and that will return you to that item. if you need the cmb to return to a value then
me.cmbStuff = myValue
Dan
Thanks Dan, sure I can put this to good use somewhere.
How do I add a data entry form with drop down lists to an existing spreadsheet with data?
I keep getting Run-time error '1004'.. I wrote the code exactly like yours but with addition of a new variables and changing their name. Please help me, what caused it?
oops, never mind. mistook l in xlup for 1. thanks for this awesome video,
mr_show thanks and good job Mr show
Great tutorial! Thanks 😉
After hitting submit, how can the form clear?
Thx for the video.
+Mike Anaya add a "clean" button and just put TextBox.Text = " "
I'm looking for Excel 365 for Mac advanced short cut keys instead of you PC Alt + I + D (dynamic name range)
Bro I try to do your steps and now I am on the submit button.. I wrote the code but when I run it I fill the info and then when I click submit it does not run it and say run time error 9. Subscript out of range. What should I do my man?
When I click on debug I see with yellow line the place that we wrote set sheet = this workbook. Sheets
how do you add an If statement in a userbox you created ?
Just need to format the date as UK not US. Any clues please?
Move. :D
Nicholas Sequeira In all the years I've been playing on the Internet, that has to be the best answer to a question I have ever seen!
N7eptune Format(Now, "DD-MM-YYYY")
Superb.. Its giving auto date and time in the userform in the simple one line me.tbdate = date & "" & time
Good pace on this video.
I love, love, love this! Thank you! :)
Awesome! Thanks for the comments and the like DatabaseDesigns4U .
DAn
awesome tutorial! Thanks!
how could we make the cmbListItem select after we hit Insert
how i can put data in row 14 not in row 1
I have date before 14 first row and after 18 row i need to put de data between. THanks
Hi Simon,
I'm not sure if I understand what you're asking. Can you explain a bit more please?
Thanks
Dan
when i push add, data need to go in cell A14. If i add more product go on line A15-16 etc. Not going on first row available.
Hi Simon,
If you use .End(xlup) method, it will start from the bottom most cell in column A and keep searching for the next available non-empty cell. So this should work unless you have anything non-empty below this area:
lastRow = cells(rows.count,1).end(xlup).row
nextRow = lastRow +1
Hope that makes sense
Thanks
Dan
This is a nice tutorial, however, I just can not get the List1 range named. I have tried it over and over and over, and it just does not appear in the range list. It seems not to exist
I'll bet it's in there, but hiding a bit. Try this: press Alt, I,N,D to open the Names Manager. See if it's in the Names Manager, but perhaps not visible in the names dropdown list? That's where I usually find the ones that are problematic.
ExcelVbaIsFun
Thank you, Dan, for your quick reply. That worked
Awesome! Keep up the great work Steve Hedemann . Don't forget! for a few more days you can get my brand new course "Barcoding in Excel VBA" for just $19 at this special coupon link www.udemy.com/barcodes-excelvba/?couponCode=JULY14 instead of retail $49. God bless! Dan
Thanks for the video, its just what I was looking for. I managed to get it going up to the very last step. When I press the submit button to enter the data I get a run time 424 error, object required and bedug the 3rd line{Set ssheet = thisworkibook.Sheets("Sheet4")}
What would be causing the error?
what shoud i do?
this in my coding
Private Sub bttnSubmit_Click()
Dim ssheet As Worksheet
Set ssheet = thisworkibook.Sheets("Sheet4")
nr = ssheet.Cells(Row.Count, 1).End(xlUp).Row + 1
ssheet.Cells(nr, 1) = Me.tbRef
ssheet.Cells(nr, 2) = Me.cmbMonth
ssheet.Cells(nr, 3) = CDate(Me.tbDate)
ssheet.Cells(nr, 4) = Me.cmbName
ssheet.Cells(nr, 5) = Me.cmbItem
ssheet.Cells(nr, 6) = Me.cmbPurpose
ssheet.Cells(nr, 7) = Me.tbReceivedamount
ssheet.Cells(nr, 8) = Me.tbPaidamount
ssheet.Cells(nr, 9) = Me.tbTransferamount
ssheet.Cells(nr, 10) = Me.cmbPaymentmode
ssheet.Cells(nr, 11) = Me.tbCheque
ssheet.Cells(nr, 12) = Me.cmbBankname
End Sub
Private Sub UserForm_Initialize()
Me.tbDate = Date
'this is for cmbMonth
'row source and write the name of named range
'this is for cmbName
'row source and write the name of named range
'this is for cmbItem
'row source and write the name of named range
'this is for cmbPurpose
'row source and write the name of named range
'this is for cmbPaymentmode
'row source and write the name of named range
'this is for cmbBankname
'row source and write the name of named range
End Sub
It's either something missing or a typo, can I see your code. If it's too big, maybe put it on our facebook page. Also, you can download the sample workbooks by clicking the dropbox link in the description. Dan
Hi Dan, I have been scouring the web and your videos for a solution to a problem and I have come up empty. I am sure there is a simple solution.
I have a spreadsheet that tracks license numbers and expiration dates for 50 states. The spreadsheet is 23 ‘fields’ wide by 50 ‘records’ high. I would like to edit (via text boxes on my UserForm) some of the fields periodically when I invoke the UserForm and choose the state in a combo box.
Thanks to your help, everything is working famously except being able to edit the text boxes. Can you shed any light on this issue? Any help would be greatly appreciated!
This is one of many lines code I am using to populate the data points when I select the State (CA, AZ, AR, NY, etc.) from the dropdown.
Me.tbLicNotes = Application.WorksheetFunction.VLookup(Me.cmbState, [Master], 13, 0)
Hey Dan,
I've resolved this issue so please don't spend any time on it however a "PutData/GetData" video may help some of us if you haven't already done one. I also used a tb change event to trigger, works great!
in the line
Me.tbDate =Date
what does "Me." stand for ?
Me. stands for the form name. This is like a shorthand instead of putting the name of the form.
scientist100 thanks scientist100. You nailed it!!
Dan
ExcelVbaIsFun Hi Dan. How can I change this default current date format to European? Thanks
andreikilla "Me" technically represents the object in which you are writing the code. So in this case, "Me" represents the form - however if you type "Me" on code found in a sheet for example, "Me" represents the sheet. Same goes for classes and modules!
Sir. The
nr=ssheet.cells(rows.count,1).end(x1up).row+1
Not working. It keep prompted yellow.
Please use XLUP instead of X1UP. Thanks
Dan
@@elliesworld1076 thanks, i already have the full code. ☺️