Excel VBA Userform to Search and Update Data

Excel VBA Userform to Search and Update Data - Excel VBA Userform Example

Рет қаралды 178,170

4 жыл бұрын

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

@franciswood6104 16 күн бұрын

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!

@aprhaaroeira3787 2 жыл бұрын

I really like this example!... Congratulations. i'm 68years men and i start now to understand something .Thanks

@tomkochka2058 2 жыл бұрын

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

@ROVideos 11 ай бұрын

This is the best video I've found on the subject. Short, easy to follow. You are great.

@javedahmed8343 2 жыл бұрын

Beautifully explained. Left no place to say anything negative.

@pureauraspace3313 3 жыл бұрын

You have done so well with your explanation. It was quick and simple to go along

@ExcelDestination 3 жыл бұрын

Glad you liked it

@greatlakeschorus6887 4 жыл бұрын

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!

@javedahmed8343 2 жыл бұрын

Very subtle explanation. Very useful. Screen shot itself is very informative

@ExcelDestination 2 жыл бұрын

thanks

@joelabacherli1310 3 ай бұрын

Perfect video. These are the ones that make these concepts sink in and stick. Thank you very much for the informative walkthrough

@excitingoffers6730 4 жыл бұрын

this is the thing i was looking for... thank you very much sir for this upload.

@ebusdk 3 жыл бұрын

Thanks a million sir! Used this form as a stock-lookup/update for inventory

@royokinawa 4 жыл бұрын

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ď

@janiceshen3834 2 жыл бұрын

Good to have your lecture, it's so clear ........

@ExcelDestination 2 жыл бұрын

Thanks

@rossaronson1163 4 жыл бұрын

Would it make a difference if the item I’m searching for is a combobox instead of a text box?

@sunitapatil4677 3 жыл бұрын

Such a helping videos thank you very much Sir

@sandeepdhruv3826 2 жыл бұрын

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

@brendanheslop5070 4 жыл бұрын

Lovely. Just the Job! Thanks

@ExcelDestination 4 жыл бұрын

Glad you like it!

@kushalmukhoti Жыл бұрын

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?

@DetroiterDave 3 жыл бұрын

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 жыл бұрын

منننىت🛀🏾🛀🏾👨‍👩‍👦👨‍👨‍👦🏇🏇🏇🏇🏇

@johnpaulbadajos633 2 жыл бұрын

Amazing, Thanks very informative. How about incorporating message for records not found within the sheets? thanks

@noah5656 4 жыл бұрын

Thank you so much!

@dieucao1742 2 жыл бұрын

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 жыл бұрын

ه ه ه ه ه ه ه ن ه ه ه ه ه ه ه ه ه ه ه هه ه ه هه هه ه ه هه ه ه ه ه ه ه ه ه ه ه ه ه ه ه هه ه هه ه ه ه هه ه ه ه ه ه ه ه ه ه ه ه ه هه ه ه ه ه ه ه هه ه ه ه ه ه ه ه ه هه ه ه ه ه ه هه ه ه ه ه هه ه ه هه ه ه ه ه هه هه ه ه ه ههه هه ه ه ه هه هه ه ه ه ه ه ه هه ه ه ه ه هه هههه ه ه ه ه هههه هه ه ه ه ه هه ه ههه ه هههههههه هه ه ههههههه ه ههههه ه ههههههه هههههههههههه هههههه هههههههههههههههههه هههههههههههههههههههه ننهنننننننننهننننننننننننننننننننننننننننننهننننننننننهننننننننهننننننننننننننننننننهنننننننننننننننننننننننننننننننننننننننننننننننننننننننننننننننننهننننننننننننننننننننننننننننهننننننننننننننننننننننننننننننننننننننننننننننننننننننننننننننننننننننننننننننننننننننننننهنننننننننننننننننننننننننننننننهننننهننننننننننننننننننننننننننننننننننننننننننننننننننهننننهننننننننننننننننننننننننننننننننننننننننننننننننننننننننننننننننهننننننننننننننننننننننننننننننننننننننننننننهنننننننننننننننننننننننننننننننننننننننننننننننننننننننننننننننتبتلل

@MrBijaypatni 4 жыл бұрын

Great Great information. Thanks very useful

@ExcelDestination 4 жыл бұрын

Glad it was helpful!

@grayjappe562 2 жыл бұрын

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?

@davecks3042 4 жыл бұрын

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 : kzfaq.info/get/bejne/fNR6aNZ2ldPDmJs.html

@padmanathaprabuchandran254 3 жыл бұрын

its really wonderful...thank you so much....

@ExcelDestination 3 жыл бұрын

welcome

@dangminhkhangdanorgan3000 3 жыл бұрын

Thank you! code very helpful for me in office.

@ExcelDestination 3 жыл бұрын

Great to hear!

@yinyinmay871 Жыл бұрын

Thank you very much, it's very useful.

@ExcelDestination Жыл бұрын

Welcome

@JMallesham 3 жыл бұрын

Sir this is very useful information. Tqs Sir.

@ExcelDestination 3 жыл бұрын

Welcome

@eldaramaj2460 3 жыл бұрын

that code helped me a lot

@alvingapay2592 4 жыл бұрын

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.

@competitiononly4431 3 жыл бұрын

Teaching method is very good

@ExcelDestination 3 жыл бұрын

Thanks and welcome

@johng5295 2 жыл бұрын

Thanks in a million. Great content. Awesome imagination. Grade: A++💥

@ExcelDestination 2 жыл бұрын

welcome

@mariamxxxx Жыл бұрын

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?

@Stockmarket_the_trader 3 жыл бұрын

Thanks for uploading... Please upload the one video on understanding of vba code...

@ExcelDestination 3 жыл бұрын

Will upload soon

@shellykomar 2 жыл бұрын

HI, Great Tutorial, I have to search the values according the reference value. which is in 6th column. how to code that?

@erwinvanlooy 3 жыл бұрын

How do you make this work with tables?

@MohAboAbdo 4 жыл бұрын

Thank you so mutch.

@ExcelDestination 4 жыл бұрын

You're welcome!

@competitiononly4431 3 жыл бұрын

I pray for you successful in every wrok

@ExcelDestination 3 жыл бұрын

Thank You!

@ISK.786 2 жыл бұрын

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??

@gustavobf5030 3 жыл бұрын

THANKS!

@madmidgy12 4 жыл бұрын

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....:)

@querrythis 2 жыл бұрын

Nice and simple.

@ExcelDestination 2 жыл бұрын

Thanks

@NewYears1978 Жыл бұрын

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.

@JATINKUMAR-qu4vi 2 жыл бұрын

Thank u very much

@dilpreetsinghsethi8928 Жыл бұрын

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.

@Damodar.Shetty 3 жыл бұрын

Awesome Sir 👌

@ExcelDestination 3 жыл бұрын

Thanks ✌️

@veefronda2747 2 жыл бұрын

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.

@jjarias655 2 жыл бұрын

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.

@neeldeshmukh5042 3 жыл бұрын

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 : kzfaq.info/get/bejne/eJpgYNmX07zFl5c.html

@binibrahim590 2 жыл бұрын

U r amazing my friend 😘😘😘

@ExcelDestination 2 жыл бұрын

Thanks 😊

@v3x14g6 3 жыл бұрын

any way to make it so its not case sensitive I've tried Lcase ect but no luck

@fwmfwm 3 жыл бұрын

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 : kzfaq.info/get/bejne/eJpgYNmX07zFl5c.html

@kerryclifford3080 2 жыл бұрын

Nice and simple, thanks. How does the code work without declaring Lastrow and i as datatypes?

@competitiononly4431 3 жыл бұрын

Thanks Sir

@tousifrahman 3 жыл бұрын

thanks a lot

@kendallperkins9843 3 жыл бұрын

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

@georgekhaba6436 2 жыл бұрын

time table : @4:42 creating the form @10:41 the code to find data @16:34 to update data

@juhisinha9085 4 жыл бұрын

Superb..

@ExcelDestination 4 жыл бұрын

Thanks 🤗

@alexsanchez9737 4 ай бұрын

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.

@Jerroldac1 3 жыл бұрын

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..

@limitone9564 2 жыл бұрын

Can you add code if you type the product name and then search and all the other fields ...

@umeshshikalgar6759 4 жыл бұрын

Very Good

@ExcelDestination 4 жыл бұрын

Thanks

@prbhat6469 11 ай бұрын

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

@kingkind2002 4 жыл бұрын

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

@giefanytubo3951 3 жыл бұрын

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. kzfaq.info/get/bejne/gN-cppWLua_VhaM.html

@sameerpendharkar8600 3 жыл бұрын

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.

@hengyi6053 3 жыл бұрын

hey bro can you explain why is there error for the last row for mine

@ExcelDestination 3 жыл бұрын

pls share your code at exceldestination@gmail.com

@bchotai4914 Ай бұрын

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

@soethein9525 Жыл бұрын

Can I know different between the "product_id = Trim(TextBox1.Text)" instead of "product_id = TextBox1.Text " without Trim fnction?

@fairosabdulmutalip641 4 жыл бұрын

How to highlight the updated cells with different colors.

@ExcelDestination 4 жыл бұрын

use code for color

@bikram903 3 жыл бұрын

Thanks

@ExcelDestination 3 жыл бұрын

Welcome

@alializadeh8195 2 жыл бұрын

Thanx

@farranaziah17 3 жыл бұрын

it is really good to me but when i save it lost all of sudden why

@DeepuMathewgoldeneye 2 ай бұрын

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

@igoravsharma 3 жыл бұрын

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 : kzfaq.info/get/bejne/eJpgYNmX07zFl5c.html

@dbags317 4 жыл бұрын

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!

@kimnguyen-gr9ge Жыл бұрын

Hi can you add a code if product I’d not found in the search

@abduljamaal513 Жыл бұрын

What If I need to build sheet2 by a form using sheet1 datas....

@AayKay2023 3 жыл бұрын

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.

@jesusmyhope554 4 жыл бұрын

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

@alby27able 3 жыл бұрын

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

@bluejay12293 2 жыл бұрын

genius

@ExcelDestination 2 жыл бұрын

Thanks

@sureshpanur561 Жыл бұрын

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.

@safesanju 4 жыл бұрын

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

@chamnanprum4068 3 жыл бұрын

how to show 2 rows dear?

@lanki2610 2 жыл бұрын

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.

@everythingandanything4102 Жыл бұрын

Does not like it if Option Explicit is on

@dinkarshinde3728 9 ай бұрын

Very Nice, Thanks for the Same. Please give me code for Multiple criteria Search through UserForm

@abdulkadri4070 4 жыл бұрын

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

@pltsm3970 3 жыл бұрын

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

@basheerpt4089 4 жыл бұрын

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

@misipatimulipola5700 3 жыл бұрын

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