Solved: Combo Box help

[karen2712]

Hi

I'm very new to VBA and struggling with a task I've been given. I have an excel sheet, I've just attached a very small example on here, and what I want to do is create a user form that uses a number of combo boxes to find values from the worksheet. In the worksheet example the bold text is used to identify the column headings and each column contains a list of reports that these headings appear in (these will be used in the combo boxes as search criteria) and what I want to happen is that a user can select values in the combo boxes and the result of the query (displayed in a text box) is the reports that these fields appear in. When a user selects a value in the first combo box I don't want this value to be available in the remaining combo boxes.

I hope I've explained that ok - confused myself a bit then.

Anyway I hope someone can point me in the right direction.

Many thanks

Karen

[Bob Phillips]

.

[karen2712]

Thanks very much for the help on this. This is kind of what I was hoping for but I don't think I explained myself very well. I'm looking for a form that has 3 combo boxes on it, each of these combo boxes contains the column heading - Value 1, Value 2 etc and when a user selects a value the form returns all the reports that contain that heading - e.g if you select value 1 it will return Contact Details Report, New Business Report and Leavers Report but if they then choose Value 2 from 2 from combo box 2 in addition it will only display New Business report and Leavers report because these are the only 2 reports that contain both Value 1 and Value 2. I hope that I've explained things a bit better now - sorry for the misunderstanding.

[karen2712]

Hi

I'm still struggling with the code for this problem. I've got the comboboxes working on the user form fine now but I still can't find a way of displaying the matches of the combo boxes into a text book on the form itself. I'm not sure if this is the best way to do what I'm trying to do but my idea, when I've finished, is to display the user form full screen size so that the underlying Excel data is not visible to a user.

I hope someone can help me with this.

Many thanks in advance.

[lucas]

can you please post what you have or is it the same as xld posted?

what do you mean by this?

I still can't find a way of displaying the matches of the combo boxes into a text book on the form itself.

If your trying to put all of these results into one textbox......??? don't think so and why would you want to?

my idea, when I've finished, is to display the user form full screen size so that the underlying Excel data is not visible to a user

Why not hide the sheet. You can have a default(dashboard) main entry page and the sheet with the data can be hidden.

[karen2712]

Hi

The sheet I'm working on is the same as on the original posting. Thanks for the tip about hiding the sheet - I think I'm over complicating things a bit.

Basically the worksheet that I'm putting together will contain a list of approx 160 sql reports. Because there are so many reports it's getting very difficult to find which one contains the data a user might need so we thought that if we provided some kind of search facility it would make life a bit easier. The sheet that I've put together contains a lot of field headings from the reports and in each column it shows which reports contain that heading. We thought that we could allow users to choose 3 of the main headings (by combo boxes) that they were looking for and then find some way of displaying the report names that include these headings on the form, I thought a text box could be used to display the names of the reports but I don't know if this is possible or the right way to do it - can you advise please? I really am striggling with the best way to do this - as I said I'm new to vba and I seem to have been landed with this task that no one wants to do!!!!!

[lucas]

Ok Karen, the file you have provided is the one that you wish to derive the combo box contents from for your search...I don't see any data that is related to these headers....we need a small sample of fake data with some kind of link or idea of how it relates to the headers you have provided.....

If the headers you have provided are in column heads, etc. in the actual database...

[karen2712]

Hi

Sorry about, the original worksheet got changed a little and I'd completely forgotten about that. I've reproduced an example of the sheet and hope this helps. I want the values in row 2 to be available in the combo boxes and then if one (or more of those values) is picked from a combo box some way of displaying the results on the form. E.g if a user picked Member Ref and Member Name the results would be Member Contact Details and New Business submissions.

[lucas]

Sorry Karen......your explaination doesn't make any sense to me.

I want the values in row 2 to be available in the combo boxes and then if one (or more of those values) is picked from a combo box some way of displaying the results on the form. E.g if a user picked Member Ref and Member Name the results would be Member Contact Details and New Business submissions.

Is Member contact details an acronym for the acutal data? If so where is the actual data. If not then that means you just want the words Member contact details returned to a textbox???

[mdmackillop]

This was based on your Post #6. I'll read the later bits now.

[mdmackillop]

I'm lost with Post #8 as well.

[karen2712]

Hi, sorry to be so confusing. The headings in row 2, Member Ref and Member Name etc, are field headings in the reports listed below them. For example, the reports in B3:B6 all contain the heading Member Ref and the reports in C3:C4 all contain the heading Member Name. What I'm trying to do is use these headings in row 2, Member Ref and Member Name etc, as filters to return the name of the report that contains that heading. If it's possible I'd like to be able to filter on up to 3 headings. For example, if someone chose the headings Member Ref, Member Name and Member Address it would tell them that these fields were available in the Member Contact Details report and the Data Protection Details report.

I hope this makes more sense.

Thanks for all your help.

[karen2712]

Hi

I've attached another worksheet that I have been working on, this is what I've managed to do so far. I've explained in post #11 what I'm trying to achieve next and I hope that you'll be able to point me in the right direction on this.

Many thanks in advance.

[mdmackillop]

Is my sample in Post #10 not achieving this?

[karen2712]

Hi

Thanks very much for all your help. I tried to open the file in post 10 today at work but it wouldn't work. I've just done it from home though and it seems to be working ok. I'll adapt this tomorrow with the correct data to double check.

Once again a million thanks.