Need to find record using userform then update and overwrite in same row in spreadsheet

Hello Everyone,

Basically I have learnt vba from scratch by piecing various things together and somehow I have managed to create what I need.

I have a spreadsheet which is populated by a userform. I have the next, previous, first and last record buttons so i can scroll through existing records.

However, I cannot find how to do the following and it is becoming very frustrating:

1. I need to be able to find a record by putting search terms in a text box
2. When I find the record, I need to be able to update it, delete some things if need be and then save the overwritten record on the same row using an update/amend command button. At present I can only save any overwritten records to a new line which is not what I want.

I have scanned the forum but cannot find a solution that does not add an additional row

Unfortunately because of restrictions on my (work) computer I am unable to attach a file. However I am very good at following examples so if someone has something similar that I can use then I would be very grateful. I can give a sanitized example of some of the code I am using though.

Your help would be much appreciated

The following is the code I use to populate the spreadsheet and add another record

Please Login or Register  to view this content.

The following code occurs when the form is intialized

Please Login or Register  to view this content.

The following is the code I use to move along records

Please Login or Register  to view this content.

I hope this all helps

Copy of Copy of Client Leads Log vHOME.xlsHello and thanks for replying

I have created a search button with the code you suggested but where would I put the search term as I currently just get the message back. I am sure this is down to some fault of mine but if you could inform me I would be very grateful again .

I did find some code (spreadsheet attached) which does what I want, however when I transfer it to my spreadsheet I get the following error message "user-defined type not defined" around the code

Please Login or Register  to view this content.

The reason I like this method is because it searches the whole spreadsheet for the term I am looking for not just one column.

Any help welcome and appreciated

If you're getting that error, is it possible that you don't have the listview installed on your computer? The code you posted worked for me.

The same thing with a listbox would be:

Please Login or Register  to view this content.

Hooray!!!!!

Thank you soooo much. This works!!!

Would you mind please changing the following code that I use to display the found record on the userform

Please Login or Register  to view this content.

I am guessing that I change the Listview1 to something but not sure what.

Finally - do you have any ideas on how once I have retrieved the found record and changed it, how to save it back in the same row with an amend/update command button?

Thank you so much for your assistance

I now have knowledge of the FIND function which I did not before

Thanks

Originally Posted by JBeaucaire

I'm not familiar with anything you're trying in the new code.

The FIND syntax can search more than one column. I'm not sure I can help much more than I have. You're already playing with ideas beyond anything I've done with forms.

Thank you so much for your assistance (The message at the bottom of the thread was meant for you)

I now have knowledge of the FIND function which I did not before

Thanks

yes, I do have a unique id in column A.

The thing is that there are free text fields with words and phrases in other columns that I might want to look up. Using the suggested code by kyle123 I can put my search term in, which comes back with results of relevant rows in a listbox. I can then choose the record and it fills the userform with that record so that I can edit it and then save on the same row.

The excel sheet that I attached does do what I require except it pastes on an additional row (and uses listview which I do not have but I have converted to listbox).

I am sure that once the desired record fills the userform there must be a way to overwrite on the same row using an update command button.

Is there not some kind of code that says "put this row of data in the same place you retrieved it from"?

Can an iF statement be used to say something like

If you retrieved the data from row 21 then paste back into row 21

or

If the unique id is ABC then clear everything next to ABC and paste new data next to ABC

Originally Posted by JBeaucaire

In my opinion, the minimum parameters that should be included with every FIND command include the LookAt and the LookIn parameters. If you don't specify them, Excel uses the values from the last time it was run, and that's not good. There are no defaults.

Yes, I agree, I didn't write the code, just adapted it from a listview

@paulary, just give it an ID, the user doesn't need to see it, but it makes everything much easier

Can an iF statement be used to say something like

If you retrieved the data from row 21 then paste back into row 21

or

If the unique id is ABC then clear everything next to ABC and paste new data next to ABC

No, you need to tell it where to put the data - it has no concept of where it has come from unless you tell it where it came from, hence the requirement for an ID column

@kyle are you able to adapt the following as you did previously

Please Login or Register  to view this content.

@JBeaucaire - I will try and follow your instructions as I find this all confusing being a newbie to VBA . I have patched things together like a patchwork blanket. I might try and make a copy of my database that is sanitised and more like the one I am actually using. i don't know where i am putting the code etc. i will get back to you and I do appreciate both of your help very much.

I think i am getting an understanding but I do require to search the whole sheet for words and phrases.

Is there a way of using code similar to

Please Login or Register  to view this content.

for more than just the A column


My userform brings the unique ID into itself when I fill it or move from record to record using (the unique id is in the txtref.Text)

Please Login or Register  to view this content.

Can I not use the listbox
press select to select the record I want
It then pulls up the record that I selected into the userform based on the ID number (txt.Ref) of the record that I selected
And then it saves over itself based on the ID?

I apologise if I am going round in circles

By the way I am using the code that you suggested earlier. trying now and will update you....

Originally Posted by JBeaucaire

It's a good idea to force yourself to understand the code you are intending to rely on in the future. You can do it.

Hello again,

I have adapted the code that you suggested behind a search button and an update button.

Then I realised that I have no where to put the search term. Where do I put the search term that i am looking for?