Link to home
Start Free TrialLog in
Avatar of Frank .S
Frank .SFlag for Australia

asked on

excel search form to be created_1

xRef-jobs_boqs---SEARCH-FORM-1.0.xlsxxRef-jobs_boqs---SEARCH-FORM-1.0.xlsxhi experts id like a simple excel form to be created as per my attached excel workbook, if you need any other info please ask..
Avatar of Frank .S
Frank .S
Flag of Australia image

ASKER

hi experts, my apologies i just realised that i had a pwd on my workbook, i have now removed it so it can be opened.
Avatar of Martin Liss
You'll need to reattach it.
Never-mind.
Attached is a start. It does not show the treeview. When I search for "bored Piers" I find 2 on the data sheet and none on the jobs sheet so I'm concerned because you show 4 results.
29025678.xlsm
hi martin, there are only 2 you are correct, there are 4 shown just for display purposes.
Also, i have opened your attached sheet but there is no user form or anything?
hi experts, if this form can be done a lot easier in an microsoft access form, im happy to do that. The treelist view in the search form is very important for me.
Did you allow macros to be enabled when you opened the workbook. If so then click Alt+F11 to go to Visual Basic and you should see this.
User generated image
Once there, double click on the form and then press F5.
Here's an update that includes the treeview. I coded the userform so that instead of the Clear button, the treeview is cleared if either the source sheet or the search value is changed. Also, if you click Ctrl+Shift+S from any sheet, the userform will appear.
29025678a.xlsm
hi martin, I'm sorry I still cant access the form, could you please give me a step by step on how I could do this?
From any sheet press and hold down both the Ctrl and Shift keys and while holding them down click the "S" key.
In case you are still having trouble, here is a version that has a button on the two data sheets that you can click to show the form.
29025678b.xlsm
hi martin, I have now been able to try your user form, it appears to work, however it does not show all levels of detail in the tree, can you please change to show all levels of detail from the 2 sheets?
Here is what I see after searching for "wal". What other levels of detail do you want to see for, say, the first item in the picture?
User generated image
hi martin, as an example if I do a search under the "job" wksht for the text "Mitchel"
the treeview should show as follows;

wallan
      76396      
             topaz 28
                   livia      
                         aspir
                              ds
                              mitchell shire
                                      yarra valley                              
                                               jobs 18
this shows every level of detail in its expanded form, the nodes sequence order is to follow the same as the worksheet.
OK I should have something for you tomorrow.
could the search text string in the treeview show as hilited also that's important,
thankyou
That's not possible in a treeview.
While it's not possible to highlight the search text string it is possible to highlight the whole node, and I found some time so try this.
29025678c.xlsm
hi martin, hiliting the text in red is fine, I'm looking at the search form you have created and the treeview results shown and the 1 node in the result of the treeview needs to be the column heading "job no", then, column heading 1, 3,4 and so on, could you please change?
so the treeview result should display as follows, for example a search for "heating" in the "data" sheet should show as;

74244, 73943
          heating
                 data 1

in the "jobs" sheet the search for "onyx" will show as;
74111
         onyx 23
                 livia
                        jobs 1
sorry forgot to mention that the text "heating" in the "data" wksht and the text "onyx" in the "jobs" wksht should display in red text
a search for "heating" in the "data" sheet should show as;

74244, 73943
          heating
                 data 1

in the "jobs" sheet the search for "onyx" will show as;
74111
         onyx 23
                 livia
                        jobs 1
That is different then what you posted in post ID: 42153691, so please verify several things:
1) When searching the data sheet you want to see columns B, A and then the column with data in D:H
2) When searching the jobs sheet you want to see columns B, C, D and then the column with data in I:M
3) You just want to see those specific columns in the results and not any of the others
4) You want the column headings for the aforementioned columns to show up in the search results. This implies that when searching in both sheets that there will be two nodes with headings
5) When the headings are shown you want all 3 columns headings to be in a single node and not cascading in 3 nodes
6) Because of the fact that the data under the third heading column can come from several different columns, you will be OK with the third column of the heading saying "other" only
7) You are satisfied with the way the red coloring is done now
Hi Martin, I can confirm the search result for both sheets will be shown in a treeview cascading structure for every information column in both the "data" & "jobs" sheet.
For example a search for the text string "580" from the "data" sheet will be displayed in a cascade structure as follows;

(TREEVIEW IN COLLAPSED FORM)
Jobs (parent node, from coloumn heading)
       
(EXPANDED LEVEL 1)
Jobs (column heading only)
Description (column heading only)
Other 1 (column heading only)
         
(TREEVIEW IN EXPANDED LEVEL 2)
Jobs (col heading)
      76868, 73740
Description (col heading)
       Base cabinet depth increase from 580 to 630 deep
 Other 1 (col heading)        
              data 1

the sequence of information in the search results of the treeview will be as follows for both sheets;
1. col b - job no.
2. col a
3. col c
4. col d
5. col e
6. col f
7. col g
8. col h
9. col I
10. col j
11. col k
12. col l
In the workbook that I have the "data" sheet ends at column "H", but more importantly I really don't understand what you are saying concerning (TREEVIEW IN COLLAPSED FORM), (EXPANDED LEVEL 1) and (TREEVIEW IN EXPANDED LEVEL 2). You seem to be saying that what is in any given node can change depending on wether or not it is expanded, so please show a full example of what should be in the treeview when it is completely expanded after searching for something in both worksheets. To avoid any confusion let me make sure that you understand that what is in any given node can't change just because it is expanded or not. Collapsing or expanding only changes what you see. It is equivalent to hiding and unhiding columns.
Here is a picture of what I think you want. Since I assume you want the headings to show in treeview, and your headings are red, I changed the color of the found node from red to purple.
User generated imageThe headings don't line up because the nodes must all be indented the same amount and what you see here is the default. I think I can increase the indentation but to account for the width of "ref/ comments" the indentation would need to be much larger than it is. Please tell me what I should do.
hi martin, the userform treeview display of results must look like this;

Job (parent node, from column heading)
+ 75631, 76126, 75486 (from "job nos" column)
Description (child node from column heading)
+ base cabinet depth (from "description" column)
Other 1 (child node from column heading)
+ data (from "other 1" column)
I'm sorry but I'm not clear as to what you want displayed in the treeview because in post ID 42153691 you said

the treeview should show as follows;

wallan
      76396      
             topaz 28
                   livia      
                         aspir
                              ds
                              mitchell shire
                                      yarra valley                              
                                               jobs 18

while in post ID: 42156286 you said the following which looks like you want just want to see B, A and D.
the userform treeview display of results must look like this;

Job (parent node, from column heading)
+ 75631, 76126, 75486 (from "job nos" column)
Description (child node from column heading)
+ base cabinet depth (from "description" column)
Other 1 (child node from column heading)
+ data (from "other 1" column)
...and if in fact you do want just B, A and D, do you want to restrict the search to look only in those 3 columns?
hi martin, i can confirm that the search must look in all columns but only display columns that have a value in them,
and that the userform treeview display of results must look like this;

Job (parent node, from column heading)
+ 75631, 76126, 75486 (from "job nos" column)
Description (child node from column heading)
+ base cabinet depth (from "description" column)
Other 1 (child node from column heading)
+ data (from "other 1" column)

the sequence of information in the search results of the treeview will be as follows for both sheets;
1. col b - job no.
2. col a
3. col c
4. col d
5. col e
6. col f
7. col g
8. col h
9. col I
10. col j
11. col k
12. col l

but as i said it will only show the columns that have a value in them, but keep in mind if i decide to add a value into a column that hasnt currently got a value, then the search must then display this into the treeview also as described to you above.
OK I think I've finally got it. In addition to what's been changed for the node display, some other things were modified:
  • The result count is cleared when the treeview is cleared
  • Pressing return after entering the search term triggers the search (just like clicking the Search button)
  • The userform default selection sheet changes depending on where the button is clicked
  • Focus is placed in searchbox when form appears
  • In the AddNode sub you'll find a COL_COUNT constant which is currently set to 13 (column "M"). If you add any new columns just change that value to reflect the maximum number of columns to be searched. Doing that will automatically search all the columns. You could also just change it to 20 or something and be done with it without much wasted processing time.
29025678d.xlsm
Hi martin.. I can't open your file, pls repost
hi martin, i have now been able to open your workbook.
A couple of things i have noticed with the form, the treeview structure is displaying the information without the column headings which i said above needs to show the column headings, for example;

Job (parent node, from column heading)
+ 75631, 76126, 75486 (from "job nos" column)
Description (child node from column heading)
+ base cabinet depth (from "description" column)
Other 1 (child node from column heading)
+ data (from "other 1" column)

currently you are displaying the information in the treeview without the column headings, for example;
75631,76126,75486
         base cabinet depth
                   data 1

so the column headings are all missing, please add them in so it looks like this;

Jobs "75631,76126,75486"
         Description "base cabinet depth"
                   Other 1 "data 1"

any column with data must be shown in this format, the column heading first and then the data contained within that column.
hi martin, could you please add a "clear" button directly above the result text box "12 item/s found", and a "collapse info" button directly above the "search" button.
ASKER CERTIFIED SOLUTION
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
hi martin im assuming the form has unlimited entries in every column? ok thanks i will ask a new question then
Yes, up to the limits of the Excel version you are using.
followed requirements precisely and asked the right questions to minimise time in creating the solution, suggested alternatives to meet requirements, a real expert!
You're welcome and I'm glad I was able to help.

If you expand the “Full Biography” section of my profile you'll find links to some articles I've written that may interest you.

Marty - Microsoft MVP 2009 to 2016
              Experts Exchange MVE 2015
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2016
ok thanks martin i will look at these soon...