Link to home
Start Free TrialLog in
Avatar of calbais
calbaisFlag for Canada

asked on

How to use comboboxes to filter information

In the attached Workbook, in Worksheet “Letter”, I need to make a choice from ComboBoxProvince, ComboBoxAIGACG and ComboBoxLetterType.
Based on those choices, the program should display the corresponding worksheet.

For example:
Choose “NL-Newfoundland”, “Apprenticeship Incentive Grant (AIG)” and “Missing Information Letter” and the worksheet “AIG_NL_Mis” should appear.
When the worksheet appears the user chooses a reason from the list by selecting the option button to the left of the reasons.

When the option button is selected, the paragraph in column B (AIG_Newfoundland_Missing_Reason 1) is inserted in the Worksheet “PrintLetter” in Cell B24. The completed letter will then be printed and mailed to our client.
The VBA code in the workbook (obtained from 'buttersk' on Experts Exchange) is a start of what I want to accomplish but I don’t know how to finish to make it work!

If anyone can finish my code or start over from scratch, I would greatly appreciate it.
EE-Version.xlsm
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

Try this which is your workbook with a few changes. One problem is that ComboBoxLetterType gets blanked out by existing code but I think I worked around it.
EE-Version.xlsm
Avatar of calbais

ASKER

Thanks for you reply Martin.

I made a slight change to your option button code so it would enter the value in the column B beside each option button in the Printletter B24 cell. That works fine.

What I still need is that when I choose from, ComboBoxProvince, ComboBoxAIGACG and ComboBoxLetterType, I need the corresponging worksheet to pop up so the user can then click the appropriate option button. After the option button is clicked, the "PrintLetter" worksheet should appear so the user can review it and then print the letter.

I've attached my revised version for use.

Thanks
EE-Version-Nov-15.xlsm
Please verify that the one case you mentioned in your original question which was
Choose “NL-Newfoundland”, “Apprenticeship Incentive Grant (AIG)” and “Missing Information Letter” and the worksheet “AIG_NL_Mis” should appear.
works, because it does for me.
Avatar of calbais

ASKER

Hi Martin.

Your right. It does work!!  Sorry!! I was trying it with the wrong one.

Could you show me how to add the other sheets? I seen your 'case' statement and believe that's where the magic is happening. How can I add the rest of the pages to that?

If you would just add one for the same info as you have in your comment above and change the AIG_NL_MIS to ACG_NL_MIS - then I think I can do the rest myself.

Thanks for your help.

PS. Is it possible to make the "PrintLetter" page appear after the optionbox is clicked?
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
Avatar of calbais

ASKER

Thanks. I'll give that a try.

In answer to your question- I put 3 options just for testing. There will be an unknown number of options and for 13 provinces!! And the options are different for AIG, ACG, Missing, and Rejection letters. The options won't change often once we get them all in the pages but I didn't know a more efficient way of doing it and still keep it all clear. We need just certain options available depending on the selections made in the comboboxes. And the option text will not be just "ACG_Newfoundland_Missing_Reason 1", etc as I have in this workbook. They will actually be short paragraphs.

I just did it this way until I can get the basic things working the way we want.

Another thing you could probably do easily - In the "Letter" sheet you see the line "(remove or hide this line if "ACG" is selected)" in red.
If the user chooses "Apprentice Completion Grant (ACG)" this combobox doesn't apply. Is there a way to hide the combobox and the text on that line if "...ACG" is chosen but show it if the "...AIG" option is picked?

Thanks again for your help and comments. I'll try inserting more case statements as soon as I get some time.

Regards

Calvin
I and others are willing to give you more help but could you close out this one and create a new question please?
Avatar of calbais

ASKER

I've entered all the case statements for NL-Newfoundland. When I choose the Province, the grant type (AIG or ACG, and the Letter type (Missing or Rejection), it works perfect the first time.
If I then choose NL-Newfoundland and change the grant type, I am taken directly to the sheet for the letter type previously chosen. It doesn't wait until I choose the letter type (Missing or Rejection).

How can that be fixed?

Thanks

I've attached my updated workbook.
EE-Version-Nov-15.xlsm
Avatar of calbais

ASKER

I managed to fix the problem stated above!

Thanks for your help Martin.

I've attached the final version so others can see the final result!
EE-Version-Nov-15.xlsm
Avatar of calbais

ASKER

Quick response and it worked!
I haven't actually looked your solution but here's mine which I accomplished by adding a button the 'Letter' sheet.

In any case you're welcome and I'm glad I was able to help.

Marty - MVP 2009 to 2012
EE-Version-Nov-15-1.xlsm