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!! :)
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
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
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.
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
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
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
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.
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...
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
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, 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
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
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
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.
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.
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
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.
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!
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.
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
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.
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)
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 ?
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!
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?
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!
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 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...
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!
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).
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
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 . . .
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
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.
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.
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.
Adding items to combobox using code is a lengthy method but instead we can change the properties of combobox in row source to the name of our name range
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 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' TH-cam video for you. Thanks for letting me know. send here: excelvbaisfun@gmail.com
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!
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.
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
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
Hey, DAN! I am fan of your vids. I learnt userforms from your videos. and I need an urgent help, may be just a direction, about which of your youtube videos suit best to my problem. let me explain it: I can make simple userforms and add data through them.(defining lastrow, typing riht cell adresses etc) Now, I have a list of names. And when I open userform,and add them in a combobox. Second step is adding a data to a choosen name in list. lets say, in cbx are names: Dan, Thomas, Robet.now I wanna add date of birth of each. Firstly I choose a name from combobox, and add thomas' date of birth. VBA must find Thomas and add data in the same row with Thomas.(ohh..typing in english is sometimes annoying .) Could ı explain myself? :):)
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...
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?
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?
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?
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
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
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
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?
bro your video are more simple and straight to point . good job man
Thank you, Soumitra!
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!! :)
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
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
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
Oh! Had another look and sorted it out. Runs like a dream! Thank you.
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.
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
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!
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
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
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.
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...
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
excelente trabajo profesor
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
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,
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
Hi Dan! Your videos have helped me greatly.
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
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.
Hey, Awesome tutorial!!! What hot key did you use to open up the New Name window?
Thanks Dan, sure I can put this to good use somewhere.
oh, also, on a worksheet Activex combobox, use the ListFillRange instead of RowSource, but they work the same I believe. Thanks again!
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
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
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 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 so much for making these videos. Yes I do watch mikes videos.Amazing guy and amazing videos.
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
Thanks in a million. Great content. Awesome imagination.Grade: A++💥
Glad you liked it, John!
Superb.. Its giving auto date and time in the userform in the simple one line me.tbdate = date & "" & time
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.
Thank u sooo much for sharing the video!!! :D I was desperate trying to fill the comboBox!!
Which screen recorder have you used?
I love, love, love this! Thank you! :)
Awesome! Thanks for the comments and the like DatabaseDesigns4U .
DAn
awesome tutorial! Thanks!
Good pace on this video.
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!
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!!
Great tutorial! Thanks 😉
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
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.
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)
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 ?
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.
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?
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?
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!
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?
I have a question. Why i cannot run the code. It shows run-time error ‘424’ object required
you said you can program that after the submit button is hit you can program it to delete its previous entry, how so?
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...
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!
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).
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!
nice. I like it.
Hi, is it possible to make a command button multi-functional??? I really need help with this!
Hi, Can you create a command button that has two functions e.g. Submit and Clear?
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
.
.
.
which your video show how to get next row (nr)? Please help me...
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
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.
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.
thanks so much Dan! your Awesome!
Thankes for it
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: th-cam.com/video/7fVLbCRyXEI/w-d-xo.html
Thanks
Dan
Adding items to combobox using code is a lengthy method but instead we can change the properties of combobox in row source to the name of our name range
Please can you explain what I am to replace 'blah' with? As you do not define what it is! Thanks
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 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' TH-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
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, 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!
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?
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.
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
How do I add a data entry form with drop down lists to an existing spreadsheet with data?
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
Thanks this is so helpful...
So glad it is helpful!
I still can’t figure out how to make the text box default to date.
thanks a lot 👍
Really helpful this
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: th-cam.com/video/pOGk3J0JIhU/w-d-xo.html
Thanks
Dan
Hey, DAN! I am fan of your vids. I learnt userforms from your videos. and I need an urgent help, may be just a direction, about which of your youtube videos suit best to my problem. let me explain it: I can make simple userforms and add data through them.(defining lastrow, typing riht cell adresses etc) Now, I have a list of names. And when I open userform,and add them in a combobox. Second step is adding a data to a choosen name in list. lets say, in cbx are names: Dan, Thomas, Robet.now I wanna add date of birth of each. Firstly I choose a name from combobox, and add thomas' date of birth. VBA must find Thomas and add data in the same row with Thomas.(ohh..typing in english is sometimes annoying .) Could ı explain myself? :):)
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...
After hitting submit, how can the form clear?
Thx for the video.
+Mike Anaya add a "clean" button and just put TextBox.Text = " "
how could we make the cmbListItem select after we hit Insert
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
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
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?
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
Thank You !!
how do you add an If statement in a userbox you created ?
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
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, 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
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.
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")
Thank you!