Show Search results in userform and edit

**cruiser102** · 05-08-2010, 04:45 PM

Good Evening all,
I am currently working on an excel document that has a search function. As things have progressed it has become evident that i need the search function to search for a name, show all the information on a userform which then allows the user to edit the information in the userform and resave it over the original

Is this something thats possible? I have tried to acheive this myself but struggling so any help would be appreciated.

The user may also need to search for something within the description, so i need to be able to allow the user to select which person it is before they edit it.

I have attached a basic version of the one i am working on. The one i have is a little more advanced,however this will allow people to practice :D

Thanks in advance everyone

**jaslake** · 05-08-2010, 07:59 PM

Hi cruiser102

Your workbook appears to be password protected.

John

**cruiser102** · 05-09-2010, 12:35 AM

Oops, Sorry All!! The access password is 4444

**royUK** · 05-09-2010, 02:58 AM

So is the VBA Project

There's no data to test with

What'snot working in the Search Form?

Write results to a ListBox then select items to edit,see the DatabaseForm here

http://excel-it.com/vba_examples.htm

**cruiser102** · 05-11-2010, 10:49 AM

Sorry again!
The access to the workbook is 4444 and the VBA Code password is 3918.

I have left all data out of the sheet so any willing assisters can put in their own.

I have a search function already which returns to a seperate worksheet within the database. However, i would really like to have the search information returned to a userform so that the user can ammend the data and resave it if required.
It is often that the information will be stored on a person but we will need to add further information into the description area, Currently we have to scroll through approx 600 names to find the right person to edit information on the data sheet.

Again, sorry for not advising the passwords.

Roy, I have looked at the link you provided, however it says that the amend function on the worksheet cant work and comes up with 'Debug'

regards
MArk

**royUK** · 05-11-2010, 01:17 PM

Not sure what you mean,the userform example works fine.

**jaslake** · 05-13-2010, 05:59 PM

Hi cruiser102
I'm tied up for a few days working on my son's new house. If this isn't answered when I'm free, I'll look into it for you.
John

**cruiser102** · 05-14-2010, 10:14 AM

Still not having much luck with this.
With all due respect Roy, i cant get this to work on my PC from your site.

I need to have my search results returned to a userform so i can edit them and resave.

Proving to be a difficult one! Losing my hair over it! lol

**royUK** · 05-14-2010, 01:12 PM

Why is it not working,what errors are you getting? There's been literally thousands downloaded & used

**jaslake** · 05-14-2010, 05:48 PM

Hi cruiser102

I've used royUK's referenced J & R Database Form on several projects and it's worked quite well. Of course, I've modified it to suite my requirements.

The attached workbook contains a highly simplified version of his code but it does this

i would really like to have the search information returned to a UserForm so that the user can amend the data and resave it if required

This simplified code does NOT look for multiple surnames such that if you have three Smith's in your data file, it will find the first and modify it accordingly. This, of course, can be dealt with. royUK's referenced J & R Database Form contains code and Userforms to do just that.

To address this need

It is often that the information will be stored on a person but we will need to add further information into the description area, Currently we have to scroll through approx 600 names to find the right person to edit information on the data sheet.

I'll need more information and examples of what you're looking for. Your best bet is to upload actual examples of what you have so we can look at the data.

You may wish to look at his suggested code and user forms to make them work for you. If you like, I can pursue this further with you to help you adapt his code to your needs.

Let me know.

John

**cruiser102** · 05-14-2010, 07:19 PM

Gents,
Thanks for your help.
That is great, i will work with that. Thanks again.

If i get stuck i will shout

Thanks all

**cruiser102** · 05-15-2010, 12:28 AM

Hi Jaslake,
Sorry, I am struggling again. I cant seem to get my head around this.

Sorry for sending you this again, i am struggling with this search option return.

Basically what i am trying to do is search the data using the specific criteria in my search function. Once i have searched the data i want it to enter the list of possible search matches to a listbox within a userform.

Once they have been returned i want the option to be able to click on the relevent record in the listbox and be given the option to amend/change the description within the already saved data. Once the users have added/amended the data they can press save and it will auto save it back into the original place on the data worksheet.

My current search form is available by choosing the search tab at the bottom and pressing the button on that worksheet (Red)
I have attached another form with data in it.

**jaslake** · 05-15-2010, 12:41 AM

Hi cruiser102

OK, I think I have a better idea of what you're looking for. Can't guarantee I can help. Let me study this and see if I can figure it out. I'll get back to you ASAP.

John

**royUK** · 05-15-2010, 02:57 AM

The workbook & VBA Project are protected

Is this what you want?

**cruiser102** · 05-15-2010, 07:30 PM

Hi Roy,
Thanks thats what i am after, i just need to know how to amend a specific record from the listbox now.. Without having to go find it in the data sheet.

Ideally, i would click on the required name in the listbox that is populated from the search and this would then bring up another form or something or take me to the entry on the data sheet where i can amend the information.

Thanks guys for your help so far, really appreciated

**jaslake** · 05-15-2010, 08:45 PM

Hi cruiser102

I think the attached does as you requested.

John

**royUK** · 05-16-2010, 04:22 AM

I've added some code to amend the selected details

**cruiser102** · 05-18-2010, 09:48 AM

Gentleman! You are brilliant!!

Thanks very much for your efforts on this, both are superb! That gives me a brilliant example to use on my database!

Thanks again for your help!

Regards
Mark

**cruiser102** · 07-18-2010, 12:30 AM

Hello All,
Some time ago i put on a post about problems with my search function within my excel sheets. I have only just had time to get back on here and put on a copy of the book i am working on.

The Problem:
When searching i wish to search part words on all the options, IE, If a name is stored as a surname of Smith, i would like it to appear if i searched for SM

The same with the notes, if i wish to search all the notes for a specific word, such as Bike, i would like each relevent entry to be returned to the search form.

Would one of you kind people be able to point me in the right direction please.

Regards
Mark

**royUK** · 07-18-2010, 03:09 AM

Was the other post not solved,if not then "bump" it

**Leith Ross** · 07-18-2010, 04:38 AM

Hello Mark,

You should repost the workbook without the VBA Project protected. Wasn't a barrier for me, but most anyone else would give up and refuse to help you any further.

**cruiser102** · 07-18-2010, 01:59 PM

Hi,
Firstly, - I searched for the older post to 'Bump' it back to the top, but unfortunately couldnt locate it, hence starting a new one. (Hope this is ok?)

Secondly, It doesnt appear to be protected the VBA on the workbook on the one i thought i had uploaded - I have uploaded another one below which should definately be unprotected.

Look forward to hearing from all you intelligent people

Regards
Mark

**shg** · 07-18-2010, 02:24 PM

Threads merged.

To find past posts, go to you public profiile, statistics tab.

**cruiser102** · 07-24-2010, 06:24 PM

Do we have any helpers out there?

**StartingOut** · 12-21-2012, 07:27 PM

Hi this file NameNo Data (2 is great a huge help to me, just 1 question if I could ask. Does anyone know how to get this code to bypass the 10 column ListBox value. I am using the as my base code but I have 21 columns of data and the code just wont return more then 10 columns of data to the ListBox

**jeffreybrown** · 12-21-2012, 07:57 PM

@StartingOut,

Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.

Show Search results in userform and edit

LinkBack

Thread Tools

Rate This Thread

Display

Show Search results in userform and edit

Re: Show Search results in userform and edit

Re: Show Search results in userform and edit

Re: Show Search results in userform and edit

Re: Show Search results in userform and edit

Re: Show Search results in userform and edit

Re: Show Search results in userform and edit

Re: Show Search results in userform and edit

Re: Show Search results in userform and edit

Re: Show Search results in UserForm and edit

Re: Show Search results in userform and edit

Re: Show Search results in userform and edit

Re: Show Search results in userform and edit

Re: Show Search results in userform and edit

Re: Show Search results in userform and edit

Re: Show Search results in userform and edit

Re: Show Search results in userform and edit

Re: Show Search results in userform and edit

Problem with VBA Search

Re: Problem with VBA Search

Re: Problem with VBA Search

Re: Problem with VBA Search

Re: Show Search results in userform and edit

Re: Show Search results in userform and edit

Re: Show Search results in userform and edit

Re: Show Search results in userform and edit

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions