vba search form errors_01

Which ones are duplicates?

hi, in screenshot 1, the duplicates in the search form results are the 1st 4 items in red, so result 1 & 3 in the result have been duplicated

The fact that the first letter after the underscore is being dropped is bizarre!

yes i know, the other wkshts dont seem to have this problem only this one

I found the reason. Now on to the other things.

ok thanks for the update..

I believe the reason for the duplicates is that "eave" for example is in row 10 twice. Once in column B and once in column C. What do you want to do about that?

i get that, however it should only be shown once in the result, can we treat the search so it searches by row that way the result if in more than 1 column in the same row it will only show the result once?

That may be possible.

In this search please show me manually what the results should look like.

How about this?

Or this?

hi martin, the result should only show "description" once because all the info is in the same row, even though there are "-", have you understood? so the closest option you have listed is the last option 3, basically it should like the following;

1575 Windows Screens and Grills
            description - for BAL jobs screens to be alum not fibreglass
                                - alum window shrouds/ surrounds

Is this possible? this way its clear that the result is coming from the 1 row in the source wksht, otherwise it looks as though its coming from 2 different rows from the wksht.

The problem with your suggestion is that it will be hard to align the "- alum..." line under the "for BAL..." line because "description " is not the same width as 12 spaces (11 for "description" and one more) but I'll see what I can do.

ok...i understand...what about...can you still have the text"description" as part of your vba code only but not show it in result search form so make it invisible? so then the spaces will remain the same for each line? so basically only show "description" once per row of wksht result? the red text is to be still be shown only on the line with the search criteria, the other items with "-" to only be shown as normal black text.

How about this?
It uses tabs to align the content but note that there are two nodes under the 1575 node. Hopefully you can live with that.

Attached is the workbook that also handles the other problems (I hope).
29215889.xlsm

hi martin, yes i can live with that sure...
a question, with the result text in the search form, it seems as though there is a limit of characters it displays, can "..." be added to the end of the result text in the form so i see the result line?

I seem to have made an unintended change. Let me explain.

In the workbook you attached if you search in cc notes for 1250 for example and then you hover the mouse over the description node you will see this where both the node and the tooltip text are truncated using "...".
I think what I meant to do there was to show the truncation in the node but not in the tooltip text.

However in the changes I made for this question I forgot to truncate the node. That may be a good thing because now you can use the horizontal scroll bar at the bottom of the form to see the complete contents of the node. The tooltip text is still truncated.

So what do you want me to do with the node? Truncate it or show the whole thing via scrolling?
Similarly for the tooltip text. Truncate it or show the whole thing?
2021-05-16_06-44-56--1-.png

hi martin, please;
- truncate the tooltip text only
- node to show all text via scrolling at the bottom of the form
**note** in the screenshot you attached above, the 1st letter of the result text is still missing, it should read "temp" not "emp", please check that the results show the 1st letter in all cases.

**note** in the screenshot you attached above, the 1st letter of the result text is still missing...

That's because that result is from the workbook that has the problem. 29215889.xlsm should not have that problem.

- truncate the tooltip text only

Will do but then what is the purpose of having a tooltip at all?

hi martin, can the tool tip show all text or is there a limit of the text characters shown in a tool tip? that will determine my answer to you..

There probably is a limit but I don't know what it is. I added 50 characters to the 1250 description and an un-truncated tooltip text shows the whole thing.

ok thanks martin, please remove the tool tip & only have the scroll so that all the description can be seen in the form.
Have you managed to complete all the changes? If so, could you pls send me the final update..

Here it is.
29215889a.xlsm

hi, martin ive just done a quick search from this search form but it appears that the search text i entered in the last result shown in the form does not hilite it red like the others, why is that?
- and it seems there 3 items found but form only showing "2 items found"?

Corrected.
29215889b.xlsm

ok thats fixed in that sht, but in the "jobs data" there are similar issues, see screenshot below. Is this something you can fix gloabally or would you need to fix these issues again for the "jobs data"wksht?
issues - 5 hilited red, but only "3 found"according to the wksht? and there are issues with the last result display of info..can this be fixed?

Please attach the workbook in which you added "test line 3" and "test line 4".

ok sure.
vba-search-form_04.1.xlsm

hi martin, have you been able to find a solution from my last comment to you..

If you are referring to the jobs data problem I will wotk on it today.

Are there any other sheets in addition to cc notes and jobs data that could have a description consisting of more that one row?

Are there any other sheets in addition to cc notes and jobs data that could have a description consisting of more that one row?

hi martin, yes there are 3 other sheets & they are; "cost centres", price book & recipes.
- can all these 5 sheets be formatted as text because each time i enter a "-" in the cell excel shows a msgbox re a formula & i have to set the cell as text & re-enter the info again.
- and can sheets be automatically set as wrap text, each time i enter a new line in the same cell with a "-".

I assume that 29215889c.xlsm does what you want for the  jobs data sheet. If so then since the requirement to handle data like that in jobs data and cc notes was never in the question that resulted in the development of this workbook, please close this question and ask a new one and I'll make the necessary changes for cost centres, price book and recipes.