Searching using macro & displaying results (more than 1 result)

[Smurlos]

I have made a booking confirmation system which stores each booking in a worksheet "Bookings". A macro enables you to view the history by searching for the booking reference number.

There's a seperate sheet "ConfirmationHistory" which has the following columns:

A) ReferenceID (this is the ref id from the Bookings sheet)
B) DateStamp (a date & time stamp that the confirmation was reprinted/emailed)
C) Info (displays whether confirmation was printed or e-mailed)
D) LoggedinUser (displays the user logged in who actioned the confirmation)

Now, when a booking is made, an entry will automatically be made in the "ConfirmationHistory" sheet that initial e-mail has been sent.

If the customer says they didnt get this, you may go back into the booking and print confirmation. In which case you'd then have a 2nd entry in this spreadsheet for the same reference number.

I was wondering if there was a way to select all rows from the table (a bit like an autofilter i guess) which have the selected reference number in the ref id field and then display these rows in one of these multi-select combo boxes for example?

i.e. Say I searched the ref num "111" in autofilter, it would only show the following 2 rows.

111 - 23/02/2010 12:00 - Initial Email Sent - Carl
111 - 25/02/2010 09:30 - Print Out Done - Carl

I then want these 2 rows to appear in a multi-line list box (not a drop down, the other kind) in my user form.

Hope I haven't confused anyone.
Thanks
Carl

[mojo249]

Hi Carl

You can copy the selected rows to a temp sheet using whatever criteria you want. Then link the temp sheet to a multi column listbox.

To copy the selected items use either a Do...Loop or For...Next:

This code probably won't work without some tweeking but it should give you the idea.

Dion

Please Login or Register  to view this content.

[Smurlos]

Hi Carl

You can copy the selected rows to a temp sheet using whatever criteria you want. Then link the temp sheet to a multi column listbox.

To copy the selected items use either a Do...Loop or For...Next:

This code probably won't work without some tweeking but it should give you the idea.

Dion

Please Login or Register  to view this content.

Hi Dion

Thanks. I'll give it a whirl.
Just a quick note. How would I carry out the first part in regards to searching for a particular criterion and then copying those rows to the temp worksheet?

Thanks :-)

[mojo249]

The following part of the code copies columns A to D of each row where the value in column A is equal to varReferenceNumber (in this case "111").

If you have a userform, you can set the value of varReferenceNumber from a textbox or combobox. If you don't, then you can set varReferenceNumber from a cell value:

varReferenceNumber = Sheets("Sheet1").Cells(1, 1).Value

Please Login or Register  to view this content.

[Smurlos]

Thank you sooo much, works perfectly.

I just have one small issue as I have sort of adapted it...


On the initial userform, "Welcome" I have a textbox called "RefNum".
The user types in the reference number and clicks "Search".

I have a worksheet called "SearchData".

When they click "Search", I have a macro which searches my Bookings sheet for that reference number. If it finds a result, it copies all the information into the "SearchData" sheet. If no result was found it says "No match to that reference number"

So it copies the information from column B (customer name) to the "CustomerName" field in "SearchData", column C (telephone number) to the "TelephoneNumber" field in "SearchData" etc. etc.

Then it pops up a new userform called "ViewBooking"

Each textbox in there pulls the information from the "SearchData" worksheet. I've then taken the reference number that was searched for, from the SearchData worksheet and made that the value of "varReferenceNumber" and it works perfectly.

However, when I close the "ViewBooking" window, i make it clear all textboxes to blank and it clears the SearchData worksheet. This is fine.

Then it shows the "Welcome" userform again.
However if I enter a new reference number AFTER having done a search already and click "Search" it doesn't search for the new reference number and pops up the ViewBooking userform with all fields blank.

Is there a way to completely reset the userform to the initial state when it first opens so that this will work?

Many thanks for your help :-)

[Smurlos]

"Welcome" userform

Please Login or Register  to view this content.

This searches "BookingsList" for the value of "EZBook_Ref". If there are no results it says "No Results for that booking reference number 'xxxx'", otherwise, it selects the booking in the "BookingsList" sheet. It then selects the information from each column and transfers the information to the relevent cell names (which refer to cells in the sheet "Data").

For the very first search, this works. It then shows the User form "ViewBooking".

Please Login or Register  to view this content.

ViewBooking works perfectly. If i click on the "Close Booking" button, it runs the following macro.

Please Login or Register  to view this content.

So you end up back on the Welcome userform, back to square one.
If you then enter a new reference number that isn't in the system it will show the error message that no booking was found.
If you then enter a different reference number that is in the booking system, it does not transfer the information from each column into the cells in "Data" like it does on the first search and I am not sure why.

Can anyone help pleeeease? Thanks :-)