Lookup/Search/Find/Match then edit in userform

Hi,

I have a worksheet with 12 columns. I have set up a relitivly simple userform that will populate the first 8 colums of a the last row, sends emails and prints a ticket containing all the information.

Its a simple helpdesk, the tickets are returned with a few feilds i need to enter to the assoiated row in the last 4 columns.

The first column contains a unique reference number, I would like to make a complete userform to lookup the reference number and allow me to fill the last 4 columns of the row, then send an email to confirm completion.

The only problem is I am at the limit of my VBA skills. I have read endless posts, tried various things but can't get anything to work.

What would be the best way to do this... and how do I do it?

Regards,

Cuba

so you want to find a reference number in a list. How Many digits?

I would use a text box to enter the reference

The text box change event would do nothing if the string length is less than your minimum.

Once the minimum length is reached, the textbox change event will populate a listbox with every row that contains your reference. say you type in 1234.
You need to display 91234 and 12345 etc. If there is only one match then the listbox click event is triggerred.

Once the list box is populated it waits for you to click on your selected reference.

The listbox click event selects your entry and populates your userform

you complete the missing fields and press a button to continue.


That is the process.

I will post a file that does something like this for you to study.

Ok

This sample uses three text boxes to enable very specific searches but it will serve you well.

Study this and I will explain the elements to you.

The key routine is the one below:

I am annotating it to explain its functionality for you.

Please Login or Register  to view this content.

I created this a couple of years ago and my search routines are a bit more powerful now.

But it is a good starting point for you.

Am working through it now... Thanks

This code evolved from the one above it is called by a worksheet specific change event.
ie if any specified cells are changed and the length of one of those cells is 3 or more characters.
Then the subroutine is called using a parameter that specifies the string to search for

Please Login or Register  to view this content.

Thanks for this but I am having problems... the ListBox is causing me problems as my userform does not have one

Basically I want to enter the 12 digit reference in a textbox on the userform, if the reference number is not found, a message box tells me this, if it is, when the 4 other text boxes are filled in, this enters the data on to the reference number's row in the corresponding columns on the row when a commandbutton is clicked.

I don't particularly want the end user to work on the actual sheet just work in userforms. I will be the end user 90% of the time but in the 10% someone else is working on it... as has recently happened things have gone wrong.

Cuba

can you sanitise your workbook and post it here for me to look at?

With my method you only need to enter a few digits and click on the listbox, so much faster

OK, attached a copy...

Its far from finished... Its the 'completeUF' userform I am trying to get working, ignore the rest...

I want to enter the reference number in the CReferenceTB textbox Ie. 131211093015 then when filling the other four textboxes on the form enters the data.

take a look. I'm sure your method will work i'm just a slow learner.

I don't really want to use a list box (yet) as i'm only searching for an exact match.

I am really struggling with this... I have updated the workbook a lot... still cant get this bit working

Ok Try this for size.

Thanks,

It's good but its not helping me much. I don't want or need a listbox in the complete userform.

I need the code that basically says, when the button... send command button is clicked the data entered in Engineer, date completed, time spent and comments is added to the corresponding cells on the reference's row.

the reference will already be filled from a previous userform.

I'm going to play with the updated sheet but as before, its not easy to translate to a command button when everything refers to listboxes.

Thanks very much though, I do appreciate the help.

Example.xlsm

Attached is a simplified example, the table on sheet1 has the fights from a video game.

when the user of the workbook is given the scorecard from one of the fights, he enters the reference of the fight in the ref textbox. this selects the row containing the details of the fight.

He then fills out the other 3 textboxes with the result, score and any comments. when he clicks the OK command button. this enters the data entered in those textboxes in to the table in the relevant cells.

I assume the only code I need will be for the ref textbox and the OK command button. I just don't know what.

Have tried many things most error and none have worked.

Hope this helps clarify what i'm asking.

Regards, Cuba

Ahh,

This is different to your original requirement.

BRB

I'm probably really bad at explaining what I'm trying to do. Sorry.

This is all the code that you need in the Userform.

Please Login or Register  to view this content.

I just tested it and it works ok.

It is easy to understand as well

So you should be ok with this.

omg... Thank you that was it. Your a star, will have a drink on you when I get my pay rise