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

Подписаться 46 тыс.

Просмотров 183 тыс.

50% 1

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

Опубликовано:

2 окт 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 171

@franciswood6104 3 месяца назад

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!

@pureauraspace3313 3 года назад

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

@ExcelDestination 3 года назад

Glad you liked it

@sunitapatil4677 4 года назад

Such a helping videos thank you very much Sir

@excitingoffers6730 4 года назад

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

@JMallesham 3 года назад

Sir this is very useful information. Tqs Sir.

@ExcelDestination 3 года назад

Welcome

@dangminhkhangdanorgan3000 3 года назад

Thank you! code very helpful for me in office.

@ExcelDestination 3 года назад

Great to hear!

@querrythis 2 года назад

Nice and simple.

@ExcelDestination 2 года назад

Thanks

@bchotai4914 4 месяца назад

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

@eldaramaj2460 3 года назад

that code helped me a lot

@mariamxxxx 2 года назад

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 4 месяца назад

I have same issue. Did you manage to resolve this?

@alexsanchez9737 6 месяцев назад

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.

@ROVideos Год назад

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

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

@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 : ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-B40-vfnVagc.html

@MohAboAbdo 4 года назад

Thank you so mutch.

@ExcelDestination 4 года назад

You're welcome!

@umeshshikalgar6759 4 года назад

Very Good

@ExcelDestination 4 года назад

Thanks

@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 4 года назад

^ this information would exponentially help my workflow as well!

@saadfarrukh3687 2 года назад

Gvď

@prbhat6469 Год назад

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

@rezarohoman6201 6 дней назад

nice one week for me to get working thank u

@georgekhaba6436 2 года назад

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

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

@احمدمطاوع-ب2ذ 2 года назад

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

@veefronda2747 3 года назад

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.

@everythingandanything4102 Год назад

Does not like it if Option Explicit is on

@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

@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

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

@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!

@kushalmukhoti 2 года назад

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?

@SanjayMahato76435 2 года назад

Display list of items like sales of same products in a day month

@aprhaaroeira3787 2 года назад

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

@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 : ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-B40-vfnVagc.html

@erwinvanlooy 3 года назад

How do you make this work with tables?

@НинаФарафонтова-ж1к 2 года назад

Введите нормальную работу каналу ЮТюб

@rahulg-l6h 7 месяцев назад

Dim lastrow As Long ' Declare the variable lastrow Dim i As Long ' Declare the variable i please add this

@DeepuMathewgoldeneye 4 месяца назад

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

@bikram903 3 года назад

Thanks

@ExcelDestination 3 года назад

Welcome

@dinkarshinde3728 11 месяцев назад

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

@alializadeh8195 2 года назад

Thanx

@farranaziah17 3 года назад

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

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

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

@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

@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

@НинаФарафонтова-ж1к 2 года назад

Убрать режим инкогнито

@НинаФарафонтова-ж1к 2 года назад

Отключить канал инкогнито

@НинаФарафонтова-ж1к 2 года назад

Уберите канал инкогнито

@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

@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

@santupramanik7078 9 месяцев назад

Link please

@ebusdk 3 года назад

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

@sandeepdhruv3826 3 года назад

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

@javedahmed8343 2 года назад

Beautifully explained. Left no place to say anything negative.

@Stockmarket_the_trader 3 года назад

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

@ExcelDestination 3 года назад

Will upload soon

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

@أبوعمادالخفاجي-ه8ه 2 года назад

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

@chamnanprum4068 3 года назад

how to show 2 rows dear?

@soethein9525 Год назад

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

@giefanytubo3951 4 года назад

Hi can you help me to create template for data search in multiple worksheets

@ExcelDestination 4 года назад

You can take help from following tutorial for searching value on multiple worksheets. ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-Jyls2ZTIqUo.html

@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

@JATINKUMAR-qu4vi 2 года назад

Thank u very much

@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 : ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-FnJ5sE0m_hg.html

@johng5295 2 года назад

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

@ExcelDestination 2 года назад

welcome

@javedahmed8343 2 года назад

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

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

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

@dilpreetsinghsethi8928 2 года назад

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.

@bluejay12293 2 года назад

genius

@ExcelDestination 2 года назад

Thanks

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

@yinyinmay871 Год назад

Thank you very much, it's very useful.

@ExcelDestination Год назад

Welcome

@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!

@joelabacherli1310 6 месяцев назад

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

@hengyi6053 4 года назад

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

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

@dannycreations9581 8 месяцев назад

can you put the vba code

@neeldeshmukh5042 4 года назад

Then can we add New data inside the Excel sheet or we need to manually ad the data

@ExcelDestination 4 года назад

You can check following userform tutorial for ADD, Delete, Display..... Link is : ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-B40-vfnVagc.html

@neilsonsalazar6882 Год назад

mine is not running can you help me

@competitiononly4431 3 года назад

Thanks Sir

@fairosabdulmutalip641 4 года назад

How to highlight the updated cells with different colors.

@ExcelDestination 4 года назад

use code for color

@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

@limitone9564 2 года назад

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

@shellykomar 2 года назад

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

@abduljamaal513 Год назад

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

@padmanathaprabuchandran254 3 года назад

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

@ExcelDestination 3 года назад

welcome

@gustavobf5030 3 года назад

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.