I needed this exact type of lookup in my userform and couldn't get it working at first. Stumped me for an 1.5 hours. The code you are using for the sheet2.range would not work for me at all. Had to use the explicit Worksheets("Sheet2").Range like this to get it to work. If WorksheetFunction.CountIf(Worksheets("Sheet2").Range("B:B"), Me.Reg1.Value) = 0 Then. The five other lines of code that used that sheet2 reference also had to be changed. But it's working great and appreciate the concise and very clear instruction that you give.
Hi I have used the code name not the sheet name. You will see the code name to the left of the sheet name in the VBA explorer. There is no problem using the sheet name as you have except if someone changes the sheet name the code will not work. That is why I use the code name for the sheet instead. If the sheet name is changed then the code will still work.
Online PC Learning someone could change the sheet order around and mess your code up as well. Thanks for clearing that up for me. Makes perfect sense now. Edit: I had some time today to experiment with the sheet naming and you are absolutely right about using code name for programing. I tried dragging the sheets around and the code name stays with the sheet so that is definitely the way to go. Thanks again Trevor.
Thank you Trevor... Question: You are using CLng in you vlookup statement. What if your lookup range contains both numbers and text numbers? What would your code be? Thanks
Hi Trevor, very useful tutorial. The next thing I want to do is to auto fill the same data in another combo box and vice versa. For example; I have a list of 'items' and their corresponding part numbers, price and one or two attributes may be- now I want user to select either of the 'items' or 'part no' from a drop down list and auto fill the other fields. How to do it? Thanks
Thanks for wonderful tutorial! Want to know as how I can display pre-populated form for the respective employee ID separately in "Interface" worksheet when users clicks on 6 different macro button for 6 different employee.
Hi! I love your videos and I need help regarding how can I change value of a specific cell based on textbox that is connected to combobox. Like for example if I change the combobox to A3 the Textbox will type to B3 and if I change the combobox to A2 the textbox will type to B2. Thank you in advanced!
Thank you Trevor for sharing this tutorial .. the only one of its kind actually dealing with Vlookup in a userform and returning values to the textboxes. I have been trying to adapt this to my needs, but am failing miserably! I have a textbox called SACNo where the user enters the 4 number, and in another text box named 'SiteAddress' I would like the contents of the vlookup (named range: SiteAddress). I am not able to adapt your code successfully to get it to work for me. The other glitch is, I have an entry in the named range of 0000 which, instead of displaying an address, it actually says, please enter the address - so I will need the address box to also be overwritten if the SACNo is 0000. I have tried various Excel VBA forums, but it appears nobody knows how to do it or it is just too much trouble. ... I am getting desperate .. as I am not that versatile in VBA .. learning as I go. I'd be mighty grateful if you would be able to help me out :) ShyButterfly
Your tutorial is helpful, but I'm having trouble with your code. We have different codes in Add Command Button. The Lookup code works well, but it is assign to a textbox after update. What if I assign a Vlookup on Command Button or Search button?
Question: what other function can I use instead of CLng if the lookup ID is both numbers and numbers mix with letters. For example the ID can be 54 or T37. CLng causing error when ID T37 is used to look up. Thanks.
Trevor: Good day. Your videos are great. Thanks for your posts. Maybe this video is a bit old, but I found it very useful and hope that you can reply to this. I have one question that I hope you can help me with here: I'm trying to get two TextBox values that I want to Vlookup four columns in a sheet in which I have the information setup, but I need to have these two values "concatenate" if I may (with a "dash" between them and Vlookup in my sheet.
Hello, the data that is being pulled from the excel worksheet into the userform are dates. But the macro/userform converts it to text. Which code is proper so the text box converts that number back to a date? Thanks
Ok. This sounds simple enough but...I have a userform that tracks my POs. When I search the PO# on my userform the data populates in a listbox. when I click the listbox entry it populates the userform text boxes. On my userform I want to click the reconcile cmd button to cut that record from the current sheet (Purchase Orders - table "T-INV") and paste it to Sheet (Reconciled - table "Reconciled") the tables are identical in columns. I also would like to keep the POs in the correct number sequence even though they are not always reconciled in numeric order. I would also like the row that was cut, deleted from the Purchase order sheet. I know that sounds complicated but I just want to cut and paste the selected PO from one sheet/table to another using a command button.
May I ask a different question, I have a table consisting of company code and documents numbers. In column A the list of company code which could be repeated depends on the number of documents posted. Column B is for the document numbers which has unique value.. I want to have a combobox1 which displays the list of the company codes, and a combobox2 which displays a dynamic list depends on the chosen company code which picks up every document numbers pertaining to the company code chosen..i hope I have expalined it clear..please help!
Thanks a lot, that was very HELPFUL TO ME .. ^^ I was able to create code that disables VLookUp when it is returned an Empty value that is not in my database table ..
Hi Trevor, i need some help with VBA forms, i already have a list on a spreadsheet but i need a form so that when i select an item on the list it will add a numeric value to another box, any ideas
Hi Mr.Trevor Easton. I am stuck with a problem in Excel VBA as I am a newbie. I am making a Fee Collection Worksheet in Excel. The problem is I want to make a Userform in which it could search the Registration No of a Student and then by selecting a Month from a dropdown list and entering the fee. It should automatically update the fee in that specified cell using the registration no and month. Note: The data is in column like R NO, Name, Aug, Sep, Oct 1001, John, 1000, 1000, 1000 1002, Smith, 1250, 1250, 1250
Hi- This video was really helpful! :) However Could you kindly help me letting me know, how I could further copy these auto generated data on to another sheet one after another in cells of the same row?
instead of guessing the correct id, how do i let excel suggests results as i type? like in a google search box. ive got a huge database and the id consists of 10 alphanumeric with hypens.
hi, I am starting to play with Excel and I am building an app using Excel to send to a developer, I'd like to know how I would do a sign up/ log in page, I'd like it to be on the screen I have made rather than a form, any ideas thanks John.
Can i use "vlookup" in "randbetween"? What i meant is I want to assign a value to any numbers within a range with characters or symbols and show it up in labels or textbox?
Hi Sir Trevor, I made the userform using vloolup function like this video. then i want to replace that searched data same row. can you let me know, how can do it.
Thank you for your useful information, I wanna use text to Reg1, not figure. what function can I use instead of 'Clng' . I have a runtime err.13, please help me
Hi fantastic video really helpful just having a problem getting Vlookup search for ID that contains both numerical and alphabetical values any help please
Hi Trevor, Can't thank you enough for all great videos you've posted. I have a question for you regarding the userform vlookup if you don't mind. I use a combobox rather than a textbox for Reg1. Below is the subroutine: Private Sub cmbWrkingDRG_AfterUpdate() With Me .tbxReadmitMDC = Application.WorksheetFunction.VLookup(me.cmbWrkingDRG), Sheet11.Range("Map"), 6, 0) End With End Sub I get a "Expected End of Statement" error. it's pointing to the comma just before "Sheet11" I'll keep working on this but I hope you can provide some clues. Thank you!
Hi! I just want to ask what data type i will be using if my lookup value in a textbox contains more than 15 numbers. I received a runtime error 6 every time i run the macro. Thanks in advance for your response. God bless!
I tried to declare the textbox where the id# is as variant but now i received a run time error 1004. Please help me how to declare the right variables so that i can lookup more than 10numbers.
Thank you very much engineer If you would kindly need help it is very important from you if you allow I need code for the vlookup function, but with two criteria to use it inside the user form through text box Thank you very much
quick question... I am trying to reference the sheet name: "DATA_Site List" instead of "Sheet2" but the code is having trouble recognizing it due to the spacing... how can I resolve this?
SO VERY HELPFUL! Thank you! I also figured out that if you write it like this, it will work: .Vlookup(me.combobox,Sheets("Sheet Name").Range("Range Name or Range",column #,0)
Any help with this, I keep getting the same error Runtime Error 1004 "unable to get the vlookup property of the worksheetfunction class" I tried to use this for a form I had, and figured I was just missing something, so I went to your site and then just downloaded and recreated this by copy paste and following your steps and still get this error message.
Hi Jamie, You should check that you have the sheet referenced properly in the code. If you wish to contact me there is a contact form on my website. best wishes Trevor www.onlinepclearning.com/support/
Trevor Easton I keep getting the error "Run-time error 424" This highlights the line If WorksheetFunction.CountIf(Macro.Range("A:A"), Me.Jobbox.value) = 0 Then Was wondering what is actually wrong with it and how to fix it?
Sir Trevor I would like to thank you for this formula Sir it really helps me a lot.I would like to ask one favor from you Sir.I would like to ask if you could help me on how to vlookup on another workbook w/o opening the file? Ill be glad if you could me w/ this.Thanks a lot sir and God Speed to your tuturials
If WorksheetFunction.CountIf(Info.Range("K:K"), USForm.U1.Value) = 0 Then Getting error "Run time error 424: Object required" in the above line, Kindly help here. I have the userform named USForm textbox1 is name as U1 sheet named Info having userid value in k:k
If I try this with the button to save, it gives me an error: me.textbox1.value = " " and it sends me back to textbox1 where I have all the info so I fix it
My coding stops me with my own msg box saying the number I typed in isn't valid... I realized that coding was in the private sub of my 2nd textbox, so I moved it to wherever double clicking the actual userform brings you to. I'm not getting stopped there, so I can finish typing the 3 digit ID I want to use to lookup, but the 2nd textbox still isn't doing the Vlookup when I click on it..... Any thoughts?
Hello Mr.Trevor Easton, thanks for the tutorial the code is working perfectly,but what if i want to update data directly from userform, so if i enter the id and the address come out,, and i change the text in the userform and then press command button,, it will automatically update the data in the table corresponding to the id itself can you please help me with this thanks :)
+Herwin Chainara Hi, There are a number of tutorials that provide the edit feature from a userform. Here is one www.onlinepclearning.com/excel-advanced-filter-employee-database/ best wishes Trev
hi, i want to ask one thing regarding userform. i have created userform on my excel file, its working properly. i can add data easily. but after closing my excel file where i have entered my data and created the userform, when re-opening the same file for more data update, the userform is missing and showing some error, and not able to add more data with the help of that user form, i am very sure that i am not saving the file properly, So, Plz will you help me that how can i save that file correctly? and can update my data in future with same userform which i created.. thanks.....
Hi , i downloaded this example but its dosent work .Im getting the error : cannot run the macro 'Template-Vlookup-Userform-in-Code.xlsx'!showme'. the macro may not be available in this workbook or all macros may be disabled .In my Trust Center all macro is enable - please HELP!Thanks
Sir can u ple tell me how to debug this runtime error!! Set nextrow = Sheet3.Cells(Rows.Count, 3).End(x1Up).Offset(1, 0) for the above statement it is showing "run time error '1004'"
Yes sir that is what i gave! Sir if u can, ple give your email address i can send u the attachement i have made!please sir its my sincere request. cos it is vry imp fr my project...
Good Day Trevor. I am busy with a project, thus far all your videos helped a lot! I was wondering if perhaps you could help me improve on it though. Do you perhaps have an email address that I could use to mail my project to you for suggestions please!
.PartNumber = Application.WorksheetFunction.VLookup((Me.Job), Sheet2.Range("LookUp"), 1, 0) This line never works. I have everything defined exactly the way is should be. My "LookUp" range is specified in the Name Manager. The values exist in the sheet that I'm trying to retrieve. But I'm getting a 1004 runtime error "Unable to get the VLookup Property of the Worrksheetfunction class"
This line never works: .reg2 = Application.WorksheetFunction.VLookup(CLng(Me.reg1), Sheet2.Range("lookup"), 2, 0) it shows "method 'Range' of 'Object'_worksheet' failed any advise?
Rommel Perez The error is because of the name used by Trevor for the lookup range is "lookup" in his excel sheet, so either you have to keep the same name for the lookup range i.e. "lookup" or change the name "lookup" in the code and replace it with the name you have used for lookup range.
Thank You Trevor. I am trying to use a customer name rather than a customer ID and here is where I am running into a problem. This is for my sales report that will go to my team. Any guidance is much appreciated.
Can you help me with an issue I am having. I would like to use this method with drop down list that once customer selected it automatically pulls up all info that I would need. Such as Customer name, ID, City, Sales2015, sales 2016, sales 2017, and growthloss. I am doing something wrong. Can you help with code?
With Me .TextBox3 = Application.WorksheetFunction.VLookup(ComboBox1.Value, Sheet2.Range("A:B"), 2, 0) End With If the program no with Me.TextBox3 = Application.WorksheetFunction.VLookup(ComboBox1.Value, Sheet2.Range("A:B"), 2, 0) or TextBox3.Value = Application.WorksheetFunction.VLookup(ComboBox1.Value, Sheet2.Range("A:B"), 2, 0) If cobobox is't long do not use Clng
I love the help that Trevor gives us. Thank you so so much Trevor!!
Thank you so much trevor!! You saved my life
Just learning VBA in Excel. Great tutuorial!
Hi Trevor. Spot on tutorial. Helped me out no end. Cheers
Thanks
I needed this exact type of lookup in my userform and couldn't get it working at first. Stumped me for an 1.5 hours. The code you are using for the sheet2.range would not work for me at all. Had to use the explicit Worksheets("Sheet2").Range like this to get it to work. If WorksheetFunction.CountIf(Worksheets("Sheet2").Range("B:B"), Me.Reg1.Value) = 0 Then. The five other lines of code that used that sheet2 reference also had to be changed. But it's working great and appreciate the concise and very clear instruction that you give.
Hi
I have used the code name not the sheet name. You will see the code name to the left of the sheet name in the VBA explorer. There is no problem using the sheet name as you have except if someone changes the sheet name the code will not work. That is why I use the code name for the sheet instead. If the sheet name is changed then the code will still work.
Online PC Learning someone could change the sheet order around and mess your code up as well. Thanks for clearing that up for me. Makes perfect sense now. Edit: I had some time today to experiment with the sheet naming and you are absolutely right about using code name for programing. I tried dragging the sheets around and the code name stays with the sheet so that is definitely the way to go. Thanks again Trevor.
Happy to help
Fantastic product publication for all
Thank you Trevor... Question: You are using CLng in you vlookup statement. What if your lookup range contains both numbers and text numbers? What would your code be? Thanks
Hi Trevor,
very useful tutorial. The next thing I want to do is to auto fill the same data in another combo box and vice versa. For example; I have a list of 'items' and their corresponding part numbers, price and one or two attributes may be- now I want user to select either of the 'items' or 'part no' from a drop down list and auto fill the other fields. How to do it?
Thanks
Thank you very much Todd. You did awesome jobs!
You helped me so much, God bless you!
Hello Trevor , Amazing tutorial , Thanks a ton , It was such a help
Thanks for the feedback
Trev
Thanks for wonderful tutorial! Want to know as how I can display pre-populated form for the respective employee ID separately in "Interface" worksheet when users clicks on 6 different macro button for 6 different employee.
Hi! I love your videos and I need help regarding how can I change value of a specific cell based on textbox that is connected to combobox. Like for example if I change the combobox to A3 the Textbox will type to B3 and if I change the combobox to A2 the textbox will type to B2.
Thank you in advanced!
Thank you Trevor for sharing this tutorial .. the only one of its kind actually dealing with Vlookup in a userform and returning values to the textboxes.
I have been trying to adapt this to my needs, but am failing miserably!
I have a textbox called SACNo where the user enters the 4 number, and in another text box named 'SiteAddress' I would like the contents of the vlookup (named range: SiteAddress). I am not able to adapt your code successfully to get it to work for me.
The other glitch is, I have an entry in the named range of 0000 which, instead of displaying an address, it actually says, please enter the address - so I will need the address box to also be overwritten if the SACNo is 0000.
I have tried various Excel VBA forums, but it appears nobody knows how to do it or it is just too much trouble. ... I am getting desperate .. as I am not that versatile in VBA .. learning as I go.
I'd be mighty grateful if you would be able to help me out :)
ShyButterfly
Your tutorial is helpful, but I'm having trouble with your code. We have different codes in Add Command Button. The Lookup code works well, but it is assign to a textbox after update. What if I assign a Vlookup on Command Button or Search button?
Hi Trevor, really nice little tutorial. How would you handle your lookup returning multiple values?
Question: what other function can I use instead of CLng if the lookup ID is both numbers and numbers mix with letters. For example the ID can be 54 or T37. CLng causing error when ID T37 is used to look up. Thanks.
Trevor:
Good day.
Your videos are great. Thanks for your posts.
Maybe this video is a bit old, but I found it very useful and hope that you can reply to this.
I have one question that I hope you can help me with here:
I'm trying to get two TextBox values that I want to Vlookup four columns in a sheet in which I have the information setup, but I need to have these two values "concatenate" if I may (with a "dash" between them and Vlookup in my sheet.
Thank you, knowledge surely knows no age :)
Hello, the data that is being pulled from the excel worksheet into the userform are dates. But the macro/userform converts it to text. Which code is proper so the text box converts that number back to a date? Thanks
did you get an answer for that?
Thanks Trevor for all the great videos! Can the "AfterUpdate" be replaced with data_lookup_click() and still work?
Ok. This sounds simple enough but...I have a userform that tracks my POs. When I search the PO# on my userform the data populates in a listbox. when I click the listbox entry it populates the userform text boxes. On my userform I want to click the reconcile cmd button to cut that record from the current sheet (Purchase Orders - table "T-INV") and paste it to Sheet (Reconciled - table "Reconciled") the tables are identical in columns. I also would like to keep the POs in the correct number sequence even though they are not always reconciled in numeric order. I would also like the row that was cut, deleted from the Purchase order sheet.
I know that sounds complicated but I just want to cut and paste the selected PO from one sheet/table to another using a command button.
May I ask a different question, I have a table consisting of company code and documents numbers. In column A the list of company code which could be repeated depends on the number of documents posted. Column B is for the document numbers which has unique value.. I want to have a combobox1 which displays the list of the company codes, and a combobox2 which displays a dynamic list depends on the chosen company code which picks up every document numbers pertaining to the company code chosen..i hope I have expalined it clear..please help!
Thanks a lot, that was very HELPFUL TO ME .. ^^
I was able to create code that disables VLookUp when it is returned an Empty value that is not in my database table ..
Hi Trevor,
i need some help with VBA forms, i already have a list on a spreadsheet but i need a form so that when i select an item on the list it will add a numeric value to another box, any ideas
Hi Mr.Trevor Easton. I am stuck with a problem in Excel VBA as I am a
newbie.
I am making a Fee Collection Worksheet in Excel. The problem is
I want to make a Userform in which it could search the Registration No
of a Student and then by selecting a Month from a dropdown list and
entering the fee. It should automatically update the fee in that
specified cell using the registration no and month.
Note: The data is in column like
R NO, Name, Aug, Sep, Oct
1001, John, 1000, 1000, 1000
1002, Smith, 1250, 1250, 1250
Hi- This video was really helpful! :) However Could you kindly help me letting me know, how I could further copy these auto generated data on to another sheet one after another in cells of the same row?
Hey Trevor, i downloaded the files from your website but the button cannot call up the macro. When i look at the VBA, there is no form?
Hi. just want to ask where did you get the Me.controls ?
thanks. :)
Sir do you have tutorial on how to make Time in Time out userfirm
instead of guessing the correct id, how do i let excel suggests results as i type? like in a google search box. ive got a huge database and the id consists of 10 alphanumeric with hypens.
hi,
I am starting to play with Excel and I am building an app using Excel to send to a developer,
I'd like to know how I would do a sign up/ log in page,
I'd like it to be on the screen I have made rather than a form, any ideas
thanks John.
Can i use "vlookup" in "randbetween"? What i meant is I want to assign a value to any numbers within a range with characters or symbols and show it up in labels or textbox?
Sir, I've been trying to input my customized ID (500100) but "Reg1" won't accept it? What could be wrong?
If I want to Enter the lookup value both as integer and char what function we need to use
Hi Sir Trevor,
I made the userform using vloolup function like this video. then i want to replace that searched data same row. can you let me know, how can do it.
Thank you very much for the tutorial ...
Thank you for your useful information, I wanna use text to Reg1, not figure. what function can I use instead of 'Clng' .
I have a runtime err.13, please help me
Hi
Text is default for a textbox. Remove (CLng and the last bracket in the formula.
It should default to text.
Trev
OMG!!! Really thank you Trev.!!!
Dear Sir Trevor,
SInce Senddata will create a new record, what if I want to edit it and replace an old one? Thanks for your time.
Hi fantastic video really helpful just having a problem getting Vlookup search for ID that contains both numerical and alphabetical values any help please
Hi Lee,
You can not use Vlookup on the first column of data.
Can you give an example of what you mean by text and numbers in the same column.
Trev
i need to populate specific cell locations based off a combobox drop down population. can you help?
I love your tutorial, how can I get the codes, PM
one more thing.. i want to know also how to transfer specific data only once i click add to database and assign it also based on my format.
Hi Trevor,
Can't thank you enough for all great videos you've posted. I have a question for you regarding the userform vlookup if you don't mind. I use a combobox rather than a textbox for Reg1. Below is the subroutine:
Private Sub cmbWrkingDRG_AfterUpdate()
With Me
.tbxReadmitMDC = Application.WorksheetFunction.VLookup(me.cmbWrkingDRG), Sheet11.Range("Map"), 6, 0)
End With
End Sub
I get a "Expected End of Statement" error. it's pointing to the comma just before "Sheet11"
I'll keep working on this but I hope you can provide some clues.
Thank you!
Can I get this work book in this link?
Hi! I just want to ask what data type i will be using if my lookup value in a textbox contains more than 15 numbers. I received a runtime error 6 every time i run the macro. Thanks in advance for your response. God bless!
I tried to declare the textbox where the id# is as variant but now i received a run time error 1004. Please help me how to declare the right variables so that i can lookup more than 10numbers.
Great! it works perfect. thank you.
Thank you very much engineer
If you would kindly need help it is very important from you if you allow
I need code for the vlookup function, but with two criteria to use it inside the user form through text box
Thank you very much
quick question... I am trying to reference the sheet name: "DATA_Site List" instead of "Sheet2" but the code is having trouble recognizing it due to the spacing... how can I resolve this?
Hi,
sheet2 is not the sheet name.Itis the code name for the sheet.
You will find this to the left of the sheet name in the VBA explorer.
Trev
SO VERY HELPFUL! Thank you! I also figured out that if you write it like this, it will work: .Vlookup(me.combobox,Sheets("Sheet Name").Range("Range Name or Range",column #,0)
Any help with this, I keep getting the same error
Runtime Error 1004
"unable to get the vlookup property of the worksheetfunction class"
I tried to use this for a form I had, and figured I was just missing something, so I went to your site and then just downloaded and recreated this by copy paste and following your steps and still get this error message.
Hi Jamie,
You should check that you have the sheet referenced properly in the code.
If you wish to contact me there is a contact form on my website.
best wishes
Trevor
www.onlinepclearning.com/support/
+Trevor Easton Thank you! I had only referenced the first column of my table instead of the whole table when I had used "lookup"
Trevor Easton
I keep getting the error "Run-time error 424"
This highlights the line
If WorksheetFunction.CountIf(Macro.Range("A:A"), Me.Jobbox.value) = 0 Then
Was wondering what is actually wrong with it and how to fix it?
+Vanessa Brown unfortunately no, left it in the end cause was only abit extra to do in an assignment. Good Luck with the coding
how can we export data from sheet to listbox more than 10 columns
Sir Trevor I would like to thank you for this formula Sir it really helps me a lot.I would like to ask one favor from you Sir.I would like to ask if you could help me on how to vlookup on another workbook w/o opening the file?
Ill be glad if you could me w/ this.Thanks a lot sir and God Speed to your tuturials
Sorry don't have any code for that.
Trev
how to make search type if any textbox ?
If WorksheetFunction.CountIf(Info.Range("K:K"), USForm.U1.Value) = 0 Then
Getting error "Run time error 424: Object required" in the above line, Kindly help here.
I have the
userform named USForm
textbox1 is name as U1
sheet named Info
having userid value in k:k
what if the ID is Alpha-Numeric, what code do i need to use?.. thanks..
What would be the code if I have combo box instead Reg1?
Code in Private sub Reg1_AfterUpdate() section.
Thanks,
Tek
+Trevor Easton
Hello Trev. I have the same concern with this. Can you give us a sample vba coding?
If I try this with the button to save, it gives me an error:
me.textbox1.value = " "
and it sends me back to textbox1 where I have all the info so I fix it
My coding stops me with my own msg box saying the number I typed in isn't valid... I realized that coding was in the private sub of my 2nd textbox, so I moved it to wherever double clicking the actual userform brings you to. I'm not getting stopped there, so I can finish typing the 3 digit ID I want to use to lookup, but the 2nd textbox still isn't doing the Vlookup when I click on it..... Any thoughts?
Nevermind. I realized I had to click in the userform body, not the next textbox, to make it update. That's good enough for me. Thanks for the video!!!
😀 ok
Hello Mr.Trevor Easton,
thanks for the tutorial the code is working perfectly,but what if i want to update data directly from userform, so if i enter the id and the address come out,, and i change the text in the userform and then press command button,, it will automatically update the data in the table corresponding to the id itself
can you please help me with this thanks :)
+Herwin Chainara
Hi,
There are a number of tutorials that provide the edit feature from a userform.
Here is one
www.onlinepclearning.com/excel-advanced-filter-employee-database/
best wishes
Trev
hi,
i want to ask one thing regarding userform. i have created userform on my excel file, its working properly. i can add data easily. but after closing my excel file where i have entered my data and created the userform, when re-opening the same file for more data update, the userform is missing and showing some error, and not able to add more data with the help of that user form, i am very sure that i am not saving the file properly,
So, Plz will you help me that how can i save that file correctly? and can update my data in future with same userform which i created..
thanks.....
Hi,
You should save the file as a macro enabled file.
File type .xlsm
Best wishes
Trev
I wan to know why vlookup notworking on the same file when i perform the same proceedure
keith castillo is it still not working?
Yes there was error 1004
I stuck in Vlookup i get the error ( sheet1.range("Lookup")=《 method ' range of object _worksheet failed》 pls help me
Please note also that I am using a multi-pages userform and it is applicable to page1.
Hi , i downloaded this example but its dosent work .Im getting the error : cannot run the macro 'Template-Vlookup-Userform-in-Code.xlsx'!showme'. the macro may not be available in this workbook or all macros may be disabled .In my Trust Center all macro is enable - please HELP!Thanks
Assaf, did you get this figured out? I'm having the same issue! Help please.
While using .Reg2=Application.WorksheetFunction.....
It gives me worksheet error here, please help me
How would it be if i type without CLng and ("Lookup"). Please help.
Sorry, i am done, thank for the awesome great video
It says overflow how can I fix this?
Sir can u ple tell me how to debug this runtime error!!
Set nextrow = Sheet3.Cells(Rows.Count, 3).End(x1Up).Offset(1, 0)
for the above statement it is showing "run time error '1004'"
Hi,
This is an object error.
Check " Sheet3. " This is a code reference to the sheet NOT a sheet reference.
Yes sir that is what i gave!
Sir if u can, ple give your email address i can send u the attachement i have made!please sir its my sincere request.
cos it is vry imp fr my project...
Trevor, when I click update database some of my text boxes are dates. Excel shows them in US not UK format, any idea how I can get around this?
I did it all but its not adding to data base
Nice tutorial
thanks it fits for my file.. :)
Good Day Trevor. I am busy with a project, thus far all your videos helped a lot! I was wondering if perhaps you could help me improve on it though. Do you perhaps have an email address that I could use to mail my project to you for suggestions please!
.PartNumber = Application.WorksheetFunction.VLookup((Me.Job), Sheet2.Range("LookUp"), 1, 0)
This line never works. I have everything defined exactly the way is should be. My "LookUp" range is specified in the Name Manager. The values exist in the sheet that I'm trying to retrieve. But I'm getting a 1004 runtime error "Unable to get the VLookup Property of the Worrksheetfunction class"
Need Hlookup, plz help
This line never works:
.reg2 = Application.WorksheetFunction.VLookup(CLng(Me.reg1), Sheet2.Range("lookup"), 2, 0)
it shows "method 'Range' of 'Object'_worksheet' failed
any advise?
Rommel Perez The error is because of the name used by Trevor for the lookup range is "lookup" in his excel sheet, so either you have to keep the same name for the lookup range i.e. "lookup" or change the name "lookup" in the code and replace it with the name you have used for lookup range.
Thank You Very much sir.
Thanks bro!!!!!!!!!!!!!
Sir RunTime Error "13" please help
thanks alot for you ,but my excle data didnt connect to user form
Thanks for it, can you give me a template file of it.
salauddin cybernet www.onlinepclearning.com/excel-vba-userform-vlookup/
can you share the final file?
luis manuel galeana orozco www.onlinepclearning.com/excel-vba-userform-vlookup/
Runtime error 424
Object required
Set nextrow = sheet5. Cells(row.count, 3).end(xlup).offset(1, 0)
Highleted yellow
Any one help
Any one
Anyone else getting "error 438" Object does not support this property? Need help...
Hi Joe,
You may want to spcheck that you are using the controls shown in the tutorial and that you are using a Windows operating system.
Trev
Thank You Trevor. I am trying to use a customer name rather than a customer ID and here is where I am running into a problem. This is for my sales report that will go to my team. Any guidance is much appreciated.
You will need to remove the CLng and brackets for text instead of a number.
Trev
customer account management system without database project
PLZ UPLOAD IN GOOGLE FORM WITH VLOOKUP VIDEOS
pls send me this excel file
.textbox_regdate2 = Application.WorksheetFunction.VLookup((Me.Cmbox_CustomerID), master.Range("LookUp"), 2, 0)
Run-time error "424":
object required
Can you help me with an issue I am having. I would like to use this method with drop down list that once customer selected it automatically pulls up all info that I would need. Such as Customer name, ID, City, Sales2015, sales 2016, sales 2017, and growthloss. I am doing something wrong. Can you help with code?
Thanks, mate
Thanks ... Thanks ... Thanks
Thanks
please share your email id i have a query of data entry form
With Me
.TextBox3 = Application.WorksheetFunction.VLookup(ComboBox1.Value, Sheet2.Range("A:B"), 2, 0)
End With
If the program no with
Me.TextBox3 = Application.WorksheetFunction.VLookup(ComboBox1.Value, Sheet2.Range("A:B"), 2, 0)
or TextBox3.Value = Application.WorksheetFunction.VLookup(ComboBox1.Value, Sheet2.Range("A:B"), 2, 0)
If cobobox is't long do not use Clng
VBA is good, but BI tools replacing Excel VBA
Template doesn't have macro or form
o