Excel VBA Userform to Search and Update Data - Excel VBA Userform Example
This Excel VBA Userform example explains, how to search and update excel sheet data with userform.You can update existing data using UserForm in Excel VBA. If you want to search data
on Excel worksheet by entering Specific ID in Text Box and UserForm will display all relevant data and in case of any modification, you can change through Userform.
Watch UserForm Real Time Examples at below link :
• Excel VBA Tutorial 15 ...
Пікірлер: 170
Beautifully explained. Left no place to say anything negative.
This was so easy to follow and just what I needed for a user form I was designing at work. Thank you!
@ExcelDestination
2 жыл бұрын
welcome
I really like this example!... Congratulations. i'm 68years men and i start now to understand something .Thanks
You have done so well with your explanation. It was quick and simple to go along
@ExcelDestination
3 жыл бұрын
Glad you liked it
This is the best video I've found on the subject. Short, easy to follow. You are great.
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!
Thanks a million sir! Used this form as a stock-lookup/update for inventory
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!
this is the thing i was looking for... thank you very much sir for this upload.
Very subtle explanation. Very useful. Screen shot itself is very informative
@ExcelDestination
2 жыл бұрын
thanks
Perfect video. These are the ones that make these concepts sink in and stick. Thank you very much for the informative walkthrough
Thank you so much!
Such a helping videos thank you very much Sir
Good to have your lecture, it's so clear ........
@ExcelDestination
2 жыл бұрын
Thanks
that code helped me a lot
Thank you! code very helpful for me in office.
@ExcelDestination
3 жыл бұрын
Great to hear!
Lovely. Just the Job! Thanks
@ExcelDestination
4 жыл бұрын
Glad you like it!
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.
@alexcalangi1362
3 жыл бұрын
^ this information would exponentially help my workflow as well!
@saadfarrukh3687
2 жыл бұрын
Gvď
its really wonderful...thank you so much....
@ExcelDestination
3 жыл бұрын
welcome
Amazing, Thanks very informative. How about incorporating message for records not found within the sheets? thanks
Great Great information. Thanks very useful
@ExcelDestination
4 жыл бұрын
Glad it was helpful!
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?
Sir this is very useful information. Tqs Sir.
@ExcelDestination
3 жыл бұрын
Welcome
THANKS!
Great, I was exactly looking for it, thanks bro great work 🙏
@ExcelDestination
2 жыл бұрын
Welcome 👍
@maheshkarwande6681
2 жыл бұрын
Thank you very much @excel destination, I was also looking for same
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.
@user-sw3ho1jk4h
2 жыл бұрын
منننىت🛀🏾🛀🏾👨👩👦👨👨👦🏇🏇🏇🏇🏇
Thank you so mutch.
@ExcelDestination
4 жыл бұрын
You're welcome!
Thank u very much
Thank you very much, it's very useful.
@ExcelDestination
Жыл бұрын
Welcome
Thanks for uploading... Please upload the one video on understanding of vba code...
@ExcelDestination
3 жыл бұрын
Will upload soon
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.
Teaching method is very good
@ExcelDestination
3 жыл бұрын
Thanks and welcome
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 a lot
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
@ExcelDestination
4 жыл бұрын
you can check the tutorial, given at below link to learn, list box, option box and other active x controls : kzread.info/dash/bejne/eKJ-l9V-YNDFmMo.html
Awesome Sir 👌
@ExcelDestination
3 жыл бұрын
Thanks ✌️
I pray for you successful in every wrok
@ExcelDestination
3 жыл бұрын
Thank You!
Thanks Sir
Would it make a difference if the item I’m searching for is a combobox instead of a text box?
Nice and simple.
@ExcelDestination
2 жыл бұрын
Thanks
Thanks in a million. Great content. Awesome imagination. Grade: A++💥
@ExcelDestination
2 жыл бұрын
welcome
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?
HI, Great Tutorial, I have to search the values according the reference value. which is in 6th column. how to code that?
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??
Superb..
@ExcelDestination
4 жыл бұрын
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?
@user-er7np1qi3c
2 жыл бұрын
ه ه ه ه ه ه ه ن ه ه ه ه ه ه ه ه ه ه ه هه ه ه هه هه ه ه هه ه ه ه ه ه ه ه ه ه ه ه ه ه ه هه ه هه ه ه ه هه ه ه ه ه ه ه ه ه ه ه ه ه هه ه ه ه ه ه ه هه ه ه ه ه ه ه ه ه هه ه ه ه ه ه هه ه ه ه ه هه ه ه هه ه ه ه ه هه هه ه ه ه ههه هه ه ه ه هه هه ه ه ه ه ه ه هه ه ه ه ه هه هههه ه ه ه ه هههه هه ه ه ه ه هه ه ههه ه هههههههه هه ه ههههههه ه ههههه ه ههههههه هههههههههههه هههههه هههههههههههههههههه هههههههههههههههههههه ننهنننننننننهننننننننننننننننننننننننننننننهننننننننننهننننننننهننننننننننننننننننننهنننننننننننننننننننننننننننننننننننننننننننننننننننننننننننننننننهننننننننننننننننننننننننننننهننننننننننننننننننننننننننننننننننننننننننننننننننننننننننننننننننننننننننننننننننننننننننهنننننننننننننننننننننننننننننننهننننهننننننننننننننننننننننننننننننننننننننننننننننننننهننننهننننننننننننننننننننننننننننننننننننننننننننننننننننننننننننننننهننننننننننننننننننننننننننننننننننننننننننننهنننننننننننننننننننننننننننننننننننننننننننننننننننننننننننننننتبتلل
works like a dream! thanking you excel destination.....can this be adjusted to run through several sheets in a workbook?
@ExcelDestination
4 жыл бұрын
Yes, absolutely (with some additional code)
@madmidgy12
4 жыл бұрын
@@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....:)
Nice and simple, thanks. How does the code work without declaring Lastrow and i as datatypes?
Very Good
@ExcelDestination
4 жыл бұрын
Thanks
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.
time table : @4:42 creating the form @10:41 the code to find data @16:34 to update data
U r amazing my friend 😘😘😘
@ExcelDestination
2 жыл бұрын
Thanks 😊
How do you make this work with tables?
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?
@bchotai4914
Ай бұрын
I have same issue. Did you manage to resolve this?
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.
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.
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.
@ExcelDestination
3 жыл бұрын
ok let me try to create tutorial as you mentioned
any way to make it so its not case sensitive I've tried Lcase ect but no luck
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 !
@ExcelDestination
3 жыл бұрын
you can watch my another tutorial of userform to learn Search, Add, Delete record. Link is given below : kzread.info/dash/bejne/dGhkj9ifnrnHl8Y.html
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.
Thanks
@ExcelDestination
3 жыл бұрын
Welcome
Can you add code if you type the product name and then search and all the other fields ...
it is really good to me but when i save it lost all of sudden why
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.
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
I get an error on the update command. It says “compile error variable not defined”. The debug highlight the “lastrow =“
@EurysSantos
3 жыл бұрын
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
Thanx
Can I know different between the "product_id = Trim(TextBox1.Text)" instead of "product_id = TextBox1.Text " without Trim fnction?
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 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.
Then can we add New data inside the Excel sheet or we need to manually ad the data
@ExcelDestination
3 жыл бұрын
You can check following userform tutorial for ADD, Delete, Display..... Link is : kzread.info/dash/bejne/dGhkj9ifnrnHl8Y.html
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?
@ExcelDestination
3 жыл бұрын
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..
Hi can you add a code if product I’d not found in the search
What If I need to build sheet2 by a form using sheet1 datas....
Hi can you help me to create template for data search in multiple worksheets
@ExcelDestination
3 жыл бұрын
You can take help from following tutorial for searching value on multiple worksheets. kzread.info/dash/bejne/fK2g1ZSThKzXhdI.html
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
@ExcelDestination
4 жыл бұрын
you can declare variable as variant for date.
@dbags317
4 жыл бұрын
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
@kingkind2002
4 жыл бұрын
Any update
genius
@ExcelDestination
2 жыл бұрын
Thanks
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.....
@ExcelDestination
3 жыл бұрын
You need to save your excel file in "Macro Enabled" format. So that you can always view\use your developed Code.
How to highlight the updated cells with different colors.
@ExcelDestination
4 жыл бұрын
use code for color
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
hey bro can you explain why is there error for the last row for mine
@ExcelDestination
4 жыл бұрын
pls share your code at exceldestination@gmail.com
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
@ExcelDestination
3 жыл бұрын
Pls check another tutorial for user form to search and display. Link in given below : kzread.info/dash/bejne/dGhkj9ifnrnHl8Y.html
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
4 жыл бұрын
exceldestination@gmail.com
how to show 2 rows dear?
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.
@ExcelDestination
4 жыл бұрын
for any excel vba support, write to exceldestination@gmail.com
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?
@v3x14g6
3 жыл бұрын
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
it is showing error in lastrow = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
@ExcelDestination
4 жыл бұрын
check sheet name
@abdulkadri4070
4 жыл бұрын
@@ExcelDestination Worked many thanks for Helping!!
@fatefury
4 жыл бұрын
i check my sheet name, correct one, but it still showing the error
@fatefury
4 жыл бұрын
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
Very Nice, Thanks for the Same. Please give me code for Multiple criteria Search through UserForm
Lastrow = Worksheets("Sheet1").Cells(Rwo.Count, 1).End(xlUp).Rwo Here gives me error the Lastrow value Lastrow = 0
@ExcelDestination
3 жыл бұрын
you have written incorrect spelling of row........ code should be : Lastrow=Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUP).row
Hello, for me, i get a mistake, because "i" is not defined... did you do that somewhere else?
@umsophorn5654
Жыл бұрын
I met the same problem as yours.
Usfull video... How can deduct QTY From stock.
@ExcelDestination
4 жыл бұрын
for any excel vba task, you can reach out to me at exceldestination@gmail.com
Display list of items like sales of same products in a day month
Does not like it if Option Explicit is on
mine is not running can you help me
What if product id is not unique?
@ExcelDestination
3 жыл бұрын
then you can use any other column, with unique values. or you can make customized column by adding id and one more column.
@Philip-dy3ww
3 жыл бұрын
Excel Destination thank you for your reply!
i got run time error '438'
can you put the vba code
How to add a new record sir?
@ExcelDestination
4 жыл бұрын
you can watch another tutorial to learn add and delete data through UserForm. Link is : kzread.info/dash/bejne/dGhkj9ifnrnHl8Y.html
@iqbalchowdhury7130
4 жыл бұрын
Thanks much. I tried many tutorials but yours is the easiest. Keep up the good work.
Link please