Userform searching data on another workbook

Hi everyone,


I have created a userform to search data kept in a different workbook. Several problems in hand:

1) How to get the user form on file FKT.xls to retrieve data from the workbook Data1.xls?

2) Formating the presentation of data: the column M, where are the results i need to present on the user form are displayed on text line, i need them to be presented as vertical list.

3) to not be able to close the user form on the X, but using only the Close button, and display message "Please use Close button".

Thanks everyone,

VS

The data to search is in column N, and the data to present in column M.

The objective is to search for an account (N) and retrieve the equivalent values (M).

Hi guys,

@barryleajo & jaslake, many thanks for your prompt solutions. Could you assist in the following:

1- how to put FKT.xls to open and close the data1.xls file automatically (location of both files C:\FKT\data1.xls). It is not very user friendly to have to open the data1 file first and then the FKT file.

2- my search field, txtTarget, should search for partial content of the cell and not only exact matches. (cell content = 1030028888T, i wish to search by 28888T for instance)

3 - how to create a right-click button option on top of the search result (1 line only) "Copy", that only copies the last 10 digits of the search result. (it shows EUR 6987004000 and i want to copy only the 10 numbers using the right-click option "Copy".

thanks in advance for your help

Hi barry,

I have followed a different way. I managed to open the file where data was kept and solved issue #1 of my previous post.

1) I have now created a 2nd text box to where i copy with double click on lstResults the selected line of the search i made. I need now to make visible on this new text box only the last 10 digits of the search. i.e. result of the search is USD 6984564000 and i just want to view the numbers of the text box. how can i manage this? After the double-click that will export the selected line to the txtbox, i'd like the text to be selected automatically so i can copy it just with ctrl+C instead of having to select the text with mouse.

2) On the search text box i want to search for partial content of column N, all accounts start with 10300, i do not have the need to put that number, just want to search for the last 5 characters. i.e. 1030028888T, i want to search for 28888T (an expression like contains maybe?)

3) Recap... I enter 28888T (not mandatory to restrict to 6 characters), i press ENTER or button search (both ways must work), the results go to lstResults, i double-click the line i wish (i.e. USD 6984564000), the last 10 characters go to the text box and are highlighted for selection with CTRL+C shortcut or right-click button option "Copy".

i guess this will be all i need to be just perfect.

Regards to all

Hi barry,

I have attached the last version of my file. Thanks for your guidance and codes provided to reach the result i have adapted. Almost there, can you assist with the following queries:

1) users must enter a 6 digit (5 numbers + 1 letter) to perform the search with sucess. Else a popup message "Please enter valid format account".

2) how to put tags appearing when the mouse is on top of the field? lets say i wish to have a tag ("righ-click to copy the account selected") whenever the mouse scroll is on top of the list box Results.

3) as soon the correct format to search is inputed (i.e. 28888T, we can either press ENTER and the search begans auto, or use the "SEARCH" button as the code already working with. any idea for this?

once again thanks a lot barry

Hi,


While running pre-production tests some issues came along:

1) I need to search column N when column B is equal to "Open", where to input this rule?

2) when listbox as many results, for instance for account 28888T, when the mouse pointer is on top of the bottom accounts, like USD or TRY, the account choosen will be the one below the one i wish. I guess this is related with the listbox results being too many, but how to solve?

for now the "search anyway" wont be necessary as i need exact results.

Regards,

VS

Hi barry,

I have not changed the font size in lstResults lisbox, still Tahoma bold size 10. in the comand line the size is 1.2 like originally you gave me, now you changed it to 1.5 and is getting worse. I have found a tricky way, i sized my listbox to 123,2 height, this way i only see 10 lines and use the vertical scrollbar to go down. This way the result i right-click is always the one copied to clipboard.

About the requirement on point 1, on the data1.xls there is no content on column B, but the definitive data file has column B with "Open" or "Closed". I wish to search results on column N with column B = "Open", same row.

I now update my last version of both my files.

VSFKT.xlsFondsliste.xls

Hi barry,

Is working fine and no new issues found so far. Many thanks for all your support and assistance.

Regards my friend.

VS