Great information. Followed the steps and everything is working perfectly. Thank you. Do you have a VBA tutorial that uses the same concept but allows the user form to update multiple rows? Example: The search parameter of Completed will display a select number (10-15) work orders. Once populated a date will be added to update those orders.
I just followed your instructions, but it doesn't really work, when I press on "debud", i see that it highlights the lastrow=Worksheets("Sheet1")...line and it says "Lastrow=Empty", what am I doing wrong?
Thank you - not sure why I am getting Complie error: Variable not defined error on Lastrow = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row Private Sub CommandButton1_Click() Dim product_id As String product_id = Trim(TextBox1.Text) Lastrow = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row For i = 2 To Lastrow If Worksheets(Sheet1).Cells(i, 1) = product_id Then TextBox2.Text = Worksheets(Sheet1).Cells(i, 2).Value TextBox3.Text = Worksheets(Sheet1).Cells(i, 3).Value TextBox4.Text = Worksheets(Sheet1).Cells(i, 4).Value End If Next End Sub
Sir Thank You So much. But I have One Problem. For Text Box 1 Code I think this is Search Button. I want For this button msg box if data ot found. can you help me please
Great Video. Very simple and easy to understand. Is there any way that I can edit the search criteria itself? Suppose, I want to rectify a wrong customer ID. Is there any way to do this without deleting the whole entry and reentering the same after correction?
This is almost perfect. It would be much better if you could search for containing strings so like Searching for product name and searching for a string would bring up results and then a next button would allow you to scroll through the results - instead of just giving one result.
Thanks for this video! However, if I have a lot of columns and rows, are there any ways I can do that in a shorter way or I have to do it one line by one line?
@@ExcelDestination thanks for quick reply....do you have any tutorials on how to run it across several sheets/tabs in a workbook ? I am trying to add a button that will pull data together in one place? any help appreciated....:)
HELLO SIR I HAVE A QUERY ABOUT SERCH AND DISPLAY OPTION MY QUSESTION IS THIS I CAN SERCH ONLY BY PRODUCT ID ?? I CAN'T SERCH BY PRODUCT NAME ? QUANTITY ? CITY ?? PLZ REPLY
Thank you . I find the forms are filled for text boxes and command boxes . But how about when we have option buttons? I couldn’t get the button tick . Your brilliance is again I seek
Is there a way to have a message box show up if you try to enter a product ID that is not already in your table? For example If I look for Product ID 2004 but does not exist in your table. I would like a message box to pop up.
i got stuck in the Userform , it looks like its not available in MAC. I need this thing that you're doing so i can put in our inventory and work it seamlessly.
Great video Excel Destination. can you please add a code or teach us how to put a message if the PRODUCT CODE is not listed when we click the search and also message for "record has been updated" when record has been updated. thanks and more video please.
How to search for the data in 19th column in excel. My sheet has a data stored from 19th column. I tried using your code but it is not searching . give me blank output.
I have a Userform with 11 textboxes that I want to use to update the worksheet...I used your code however I can only get the second textbox to work...when I put data in the other textboxes when I click the update command button the data in the textboxes disappear...what do you think I'm doing wrong?
Hey great Video bro it made my work so easy, but i need help, instead of extracting data after typing it in box and search, will u pls show how to exract data frm dropdown list in the same user form with add buttion option??
Hi How we have to change the code if we have different sheets and we want to update a field on same userform. For eg, if we have same product id for two different cities, which we can put as sheet name. Then we can change product name for specific city into their sheet by this userform.
I have been looking for a streamlined way to do this and your method works great! Thank you so much. One issue though: What if the user searches for a value that does not exist in the data? There needs to be a MsgBox indicating the value was not found. When I run the code with data not found, it just stalls and does nothing and the userform stays open.
What if in my table the product ID is not unique, can I still search and select the correct product name that i want to be displayed in the form? The product ID in my data can have two or more different product name.
If the product I'd meets more than one row in worksheet how will you update. If we only want to update few rows that meets the if statement how will we go to next row which is having the same product Id
This example is good to me. However, could you tell me how to add "Delete" record code on that example ? If possible, it would be great if you could send me the excel file . Thanks !
I had some matters with my USER FORM and thank to you I have solved it. THANK YOU! Please if you can post a video about how to insert FILTERS controlled by Combo box inside a USERFORM.
Hello Boss, yesterday I prepared a excel sheet according to the video, but today when I opened this macro-enabled excel list, It is not showing the VBA coding/design in this. Kindly reply how it will showing.....
Hi Bro.. Good but if product id not found , it has to show some message and also deleting options . should have been better. I know this is basic but these are all mandatory.
Dear daood sir, I have make user form with add, search, next and previous button. I also create Update button but whenever i click update button it changes whole entries to same data whatever i updated it shows in all rows in all entries. I am stuck with this. kinldly help . Can you share your mail so i can share my excel to you
My search button works fine if i entered the data that is inthe first row, but none after that. If it makes a difference, the criteria im searching is in date format
I have 5 row first Colum have same 5 name “sanjay, second column have same 5, date 27-07-2020 and third column have 5 value ,100,200,300,400,500 …I want to merge duplicate name and sume of 5 value in and merge same date….i want whole data in one row….please help me …excel VBA code for it.
Private Sub searchBTN_Click() Dim wks As Worksheet Set wks = Sheet1 Dim product_id As String product_id = Trim(TextBox1.Text) lastrow = wks.Cells(Rows.Count, 1).End(xlUp).Row For i = 2 To lastrow If wks.Cells(i, 1).Value = product_id Then TextBox2.Text = wks.Cells(i, 2).Value TextBox3.Text = wks.Cells(i, 3) TextBox4.Text = wks.Cells(i, 4) TextBox5.Text = wks.Cells(i, 5) TextBox6.Text = wks.Cells(i, 6) TextBox7.Text = wks.Cells(i, 7) End If Next End Sub
This is what i have in my form you may have to change things around but should work Private Sub Reset_Click() Dim txt For Each txt In Frame2.Controls 'Change to the frame name you used If TypeOf txt Is MSForms.TextBox Then txt.Text = "" End If Next txt txtsearch.Text = "" ' clears text fields by names if not in frame add more if needed GradedYes = False ' all of these false statements are for Toggle /check boxes GradedNo = False ListedYes = False ListedNo = False ebaytoggle = False amazontoggle = False End Sub
I really like this example!... Congratulations. i'm 68years men and i start now to understand something .Thanks
This is the best video I've found on the subject. Short, easy to follow. You are great.
Beautifully explained. Left no place to say anything negative.
the best! easy to follow and understand because not only you make the code but also you explain what is the action following the code! more vids Dude!
Perfect video. These are the ones that make these concepts sink in and stick. Thank you very much for the informative walkthrough
This was so easy to follow and just what I needed for a user form I was designing at work. Thank you!
welcome
This Form works works better than the previous form that you sent me. I will Email you with a question or two. Thanks very much for your help!
Very subtle explanation. Very useful. Screen shot itself is very informative
thanks
You have done so well with your explanation. It was quick and simple to go along
Glad you liked it
Good to have your lecture, it's so clear ........
Thanks
time table :
@4:42 creating the form
@10:41 the code to find data
@16:34 to update data
nice one week for me to get working thank u
I pray for you successful in every wrok
Thank You!
Thanks a million sir! Used this form as a stock-lookup/update for inventory
Great, I was exactly looking for it, thanks bro great work 🙏
Welcome 👍
Thank you very much @excel destination, I was also looking for same
Thank you very much, it's very useful.
Welcome
this is the thing i was looking for... thank you very much sir for this upload.
Thanks in a million. Great content. Awesome imagination. Grade: A++💥
welcome
Teaching method is very good
Thanks and welcome
Such a helping videos thank you very much Sir
Sir this is very useful information. Tqs Sir.
Welcome
Great information. Followed the steps and everything is working perfectly. Thank you. Do you have a VBA tutorial that uses the same concept but allows the user form to update multiple rows? Example: The search parameter of Completed will display a select number (10-15) work orders. Once populated a date will be added to update those orders.
^ this information would exponentially help my workflow as well!
Gvď
I just followed your instructions, but it doesn't really work, when I press on "debud", i see that it highlights the lastrow=Worksheets("Sheet1")...line and it says "Lastrow=Empty", what am I doing wrong?
I have same issue. Did you manage to resolve this?
Nice and simple.
Thanks
Thank you - not sure why I am getting Complie error: Variable not defined error on Lastrow = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
Private Sub CommandButton1_Click()
Dim product_id As String
product_id = Trim(TextBox1.Text)
Lastrow = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To Lastrow
If Worksheets(Sheet1).Cells(i, 1) = product_id Then
TextBox2.Text = Worksheets(Sheet1).Cells(i, 2).Value
TextBox3.Text = Worksheets(Sheet1).Cells(i, 3).Value
TextBox4.Text = Worksheets(Sheet1).Cells(i, 4).Value
End If
Next
End Sub
Hi, Can you give examples on searching two rows. I need to search product Id and product name
Thank you! code very helpful for me in office.
Great to hear!
Great Great information. Thanks very useful
Glad it was helpful!
Amazing, Thanks very informative. How about incorporating message for records not found within the sheets? thanks
U r amazing my friend 😘😘😘
Thanks 😊
its really wonderful...thank you so much....
welcome
Sir Thank You So much. But I have One Problem. For Text Box 1 Code I think this is Search Button. I want For this button msg box if data ot found. can you help me please
Thanks for uploading... Please upload the one video on understanding of vba code...
Will upload soon
Great Video. Very simple and easy to understand. Is there any way that I can edit the search criteria itself? Suppose, I want to rectify a wrong customer ID. Is there any way to do this without deleting the whole entry and reentering the same after correction?
Thank u very much
that code helped me a lot
This is almost perfect. It would be much better if you could search for containing strings so like Searching for product name and searching for a string would bring up results and then a next button would allow you to scroll through the results - instead of just giving one result.
Awesome Sir 👌
Thanks ✌️
Thanks for this video! However, if I have a lot of columns and rows, are there any ways I can do that in a shorter way or I have to do it one line by one line?
ه ه ه ه ه ه ه ن ه ه ه ه ه ه ه ه ه ه ه هه ه ه هه هه ه ه هه ه ه ه ه ه ه ه ه ه ه ه ه ه ه هه ه هه ه ه ه هه ه ه ه ه ه ه ه ه ه ه ه ه هه ه ه ه ه ه ه هه ه ه ه ه ه ه ه ه هه ه ه ه ه ه هه ه ه ه ه هه ه ه هه ه ه ه ه هه هه ه ه ه ههه هه ه ه ه هه هه ه ه ه ه ه ه هه ه ه ه ه هه هههه ه ه ه ه هههه هه ه ه ه ه هه ه ههه ه هههههههه هه ه ههههههه ه ههههه ه ههههههه هههههههههههه هههههه هههههههههههههههههه هههههههههههههههههههه ننهنننننننننهننننننننننننننننننننننننننننننهننننننننننهننننننننهننننننننننننننننننننهنننننننننننننننننننننننننننننننننننننننننننننننننننننننننننننننننهننننننننننننننننننننننننننننهننننننننننننننننننننننننننننننننننننننننننننننننننننننننننننننننننننننننننننننننننننننننننهنننننننننننننننننننننننننننننننهننننهننننننننننننننننننننننننننننننننننننننننننننننننننهننننهننننننننننننننننننننننننننننننننننننننننننننننننننننننننننننننننهننننننننننننننننننننننننننننننننننننننننننننهنننننننننننننننننننننننننننننننننننننننننننننننننننننننننننننننتبتلل
works like a dream! thanking you excel destination.....can this be adjusted to run through several sheets in a workbook?
Yes, absolutely (with some additional code)
@@ExcelDestination thanks for quick reply....do you have any tutorials on how to run it across several sheets/tabs in a workbook ? I am trying to add a button that will pull data together in one place? any help appreciated....:)
HELLO SIR
I HAVE A QUERY ABOUT SERCH AND DISPLAY OPTION
MY QUSESTION IS THIS
I CAN SERCH ONLY BY PRODUCT ID ??
I CAN'T SERCH BY PRODUCT NAME ?
QUANTITY ?
CITY ??
PLZ REPLY
Pls check another tutorial for user form to search and display. Link in given below :
th-cam.com/video/B40-vfnVagc/w-d-xo.html
Thank you . I find the forms are filled for text boxes and command boxes . But how about when we have option buttons? I couldn’t get the button tick . Your brilliance is again I seek
you can check the tutorial, given at below link to learn, list box, option box and other active x controls :
th-cam.com/video/FnJ5sE0m_hg/w-d-xo.html
Lovely. Just the Job! Thanks
Glad you like it!
Is there a way to have a message box show up if you try to enter a product ID that is not already in your table? For example If I look for Product ID 2004 but does not exist in your table. I would like a message box to pop up.
THANKS!
How do you make this work with tables?
HI, Great Tutorial, I have to search the values according the reference value. which is in 6th column. how to code that?
Thank you so mutch.
You're welcome!
i got stuck in the Userform , it looks like its not available in MAC. I need this thing that you're doing so i can put in our inventory and work it seamlessly.
Great video Excel Destination. can you please add a code or teach us how to put a message if the PRODUCT CODE is not listed when we click the search and also message for "record has been updated" when record has been updated. thanks and more video please.
Superb..
Thanks 🤗
Very Good
Thanks
How to search for the data in 19th column in excel. My sheet has a data stored from 19th column. I tried using your code but it is not searching . give me blank output.
I have a Userform with 11 textboxes that I want to use to update the worksheet...I used your code however I can only get the second textbox to work...when I put data in the other textboxes when I click the update command button the data in the textboxes disappear...what do you think I'm doing wrong?
without looking at code, I can not say anything. However, I am hoping that there would be some correction required in naming of text boxes..
Thanks Sir
Hey great Video bro it made my work so easy, but i need help, instead of extracting data after typing it in box and search, will u pls show how to exract data frm dropdown list in the same user form with add buttion option??
Hi
How we have to change the code if we have different sheets and we want to update a field on same userform. For eg, if we have same product id for two different cities, which we can put as sheet name. Then we can change product name for specific city into their sheet by this userform.
I get an error on the update command. It says “compile error variable not defined”. The debug highlight the “lastrow =“
it happens the same to me. I defined the variable as integer but it didn't work... it would be nice, if you could give us a hand with this bug
I have been looking for a streamlined way to do this and your method works great! Thank you so much. One issue though: What if the user searches for a value that does not exist in the data? There needs to be a MsgBox indicating the value was not found. When I run the code with data not found, it just stalls and does nothing and the userform stays open.
منننىت🛀🏾🛀🏾👨👩👦👨👨👦🏇🏇🏇🏇🏇
What if in my table the product ID is not unique, can I still search and select the correct product name that i want to be displayed in the form? The product ID in my data can have two or more different product name.
Hi can you help me to create template for data search in multiple worksheets
You can take help from following tutorial for searching value on multiple worksheets.
th-cam.com/video/Jyls2ZTIqUo/w-d-xo.html
Thank you so much!
Can you add code if you type the product name and then search and all the other fields ...
If the product I'd meets more than one row in worksheet how will you update. If we only want to update few rows that meets the if statement how will we go to next row which is having the same product Id
thanks a lot
Hi, when building a user form, how do I set via search and update data so it searches and changes records vertically instead of horizontal rows?
Would it make a difference if the item I’m searching for is a combobox instead of a text box?
Nice and simple, thanks. How does the code work without declaring Lastrow and i as datatypes?
Thanx
Hi can you add a code if product I’d not found in the search
This example is good to me. However, could you tell me how to add "Delete" record code on that example ? If possible, it would be great if you could send me the excel file . Thanks !
you can watch my another tutorial of userform to learn Search, Add, Delete record.
Link is given below :
th-cam.com/video/B40-vfnVagc/w-d-xo.html
I had some matters with my USER FORM and thank to you I have solved it. THANK YOU! Please if you can post a video about how to insert FILTERS controlled by Combo box inside a USERFORM.
ok let me try to create tutorial as you mentioned
Thanks
Welcome
Hello Boss, yesterday I prepared a excel sheet according to the video, but today when I opened this macro-enabled excel list, It is not showing the VBA coding/design in this. Kindly reply how it will showing.....
You need to save your excel file in "Macro Enabled" format. So that you can always view\use your developed Code.
Can I know different between the "product_id = Trim(TextBox1.Text)" instead of "product_id = TextBox1.Text " without Trim fnction?
Then can we add New data inside the Excel sheet or we need to manually ad the data
You can check following userform tutorial for ADD, Delete, Display.....
Link is :
th-cam.com/video/B40-vfnVagc/w-d-xo.html
it is really good to me but when i save it lost all of sudden why
What If I need to build sheet2 by a form using sheet1 datas....
how to show 2 rows dear?
Hi Bro.. Good but if product id not found , it has to show some message and also deleting options . should have been better. I know this is basic but these are all mandatory.
hey bro can you explain why is there error for the last row for mine
pls share your code at exceldestination@gmail.com
How to highlight the updated cells with different colors.
use code for color
Dear daood sir, I have make user form with add, search, next and previous button. I also create Update button but whenever i click update button it changes whole entries to same data whatever i updated it shows in all rows in all entries. I am stuck with this. kinldly help . Can you share your mail so i can share my excel to you
exceldestination@gmail.com
genius
Thanks
My search button works fine if i entered the data that is inthe first row, but none after that. If it makes a difference, the criteria im searching is in date format
you can declare variable as variant for date.
Excel Destination awesome. It worked!
how can solve it the duplicate item for example : i have 2 same items with different date & i want do update for 2nd date
Any update
Hello, for me, i get a mistake, because "i" is not defined... did you do that somewhere else?
I met the same problem as yours.
I have 5 row first Colum have same 5 name “sanjay, second column have same 5, date 27-07-2020 and third column have 5 value ,100,200,300,400,500 …I want to merge duplicate name and sume of 5 value in and merge same date….i want whole data in one row….please help me …excel VBA code for it.
for any excel vba support, write to exceldestination@gmail.com
Display list of items like sales of same products in a day month
it is showing error in lastrow = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
check sheet name
@@ExcelDestination Worked many thanks for Helping!!
i check my sheet name, correct one, but it still showing the error
Private Sub searchBTN_Click()
Dim wks As Worksheet
Set wks = Sheet1
Dim product_id As String
product_id = Trim(TextBox1.Text)
lastrow = wks.Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lastrow
If wks.Cells(i, 1).Value = product_id Then
TextBox2.Text = wks.Cells(i, 2).Value
TextBox3.Text = wks.Cells(i, 3)
TextBox4.Text = wks.Cells(i, 4)
TextBox5.Text = wks.Cells(i, 5)
TextBox6.Text = wks.Cells(i, 6)
TextBox7.Text = wks.Cells(i, 7)
End If
Next
End Sub
Does not like it if Option Explicit is on
mine is not running can you help me
can you put the vba code
Very Nice, Thanks for the Same. Please give me code for Multiple criteria Search through UserForm
What if product id is not unique?
then you can use any other column, with unique values. or you can make customized column by adding id and one more column.
Excel Destination thank you for your reply!
thank you, exactly what I was looking for. can you add a code on how to clear the form to search for a new record please?
This is what i have in my form you may have to change things around but should work
Private Sub Reset_Click()
Dim txt
For Each txt In Frame2.Controls 'Change to the frame name you used
If TypeOf txt Is MSForms.TextBox Then
txt.Text = ""
End If
Next txt
txtsearch.Text = "" ' clears text fields by names if not in frame add more if needed
GradedYes = False ' all of these false statements are for Toggle /check boxes
GradedNo = False
ListedYes = False
ListedNo = False
ebaytoggle = False
amazontoggle = False
End Sub
Lastrow = Worksheets("Sheet1").Cells(Rwo.Count, 1).End(xlUp).Rwo
Here gives me error the Lastrow value
Lastrow = 0
you have written incorrect spelling of row........ code should be :
Lastrow=Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUP).row
Usfull video...
How can deduct QTY From stock.
for any excel vba task, you can reach out to me at exceldestination@gmail.com
Link please
How to add a new record sir?
you can watch another tutorial to learn add and delete data through UserForm. Link is :
th-cam.com/video/B40-vfnVagc/w-d-xo.html
Thanks much. I tried many tutorials but yours is the easiest. Keep up the good work.