Combo Box

dtaylor

Active Member
Joined
Mar 21, 2002
Messages
379
Hello All,

I need to have a second combobox be dependent on the selection of the first.

I have seen posts concerning this and everyone directs the user to a thread regarding named ranges and data validation using INDIRECT. This would usually work but my file will have varying size ranges and using pre-defined ranges and the indirect just isn't doing it for me. Not to mention that account managers are transferring very often so the size of the ranges will change.

What I have is two ranges, which will change in size but never more than a thousand lines.

Range 1 - Sales Managers
Range 2 - Account Managers

I need only the Account Managers linked to the selected Sales Manager to show when selected by the end user.

Is combo boxes the way to go?

Since my file is based on data retrieved from a .mdb database and is using an inline SQL query I was thinking of adding a query that that populates a hidden sheet with the selected manager and his/her account managers then populating the combobox with this data.
This is not the most elegant solution but it should work.

Any suggestions?

Thanks Dan
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
Dan,

Think you nailed it to be fair with the SQL using a variable (Sales Manager) - you can make the SQL run on a button click event - so when someone selects a sales manager it runs the SQL and returns all account managers to a specified range - which is used as the list fill range on your combo box.

I have to say that I think your idea is probably the best way of doing it, it will be quick and "clean".

Luke
 

Russell Hauf

MrExcel MVP
Joined
Feb 10, 2002
Messages
1,611
If you'd like to see a way you can do this, click on the link below. I wrote this in 5-10 minute intervals (in between doing other work) during the last day and a half, so hopefully it is complete. Please let me know if you have any questions.

HTH,

Russell

CLICK HERE
 

dtaylor

Active Member
Joined
Mar 21, 2002
Messages
379
thanks for the responses! as always this site shines above all others...

Russel could you please post your code to this thread...big brother @ my company is blocking access to your link...

i have already coded a solution but would very like to see your input..especially since you spent some time on it...


once again thank you

regards dan
 

Russell Hauf

MrExcel MVP
Joined
Feb 10, 2002
Messages
1,611

ADVERTISEMENT

Here is my try (at pasting in what I wrote) I hope it comes out ok:


--------------------------------------------------------------------------------
This is a long-winded approach, but I'm going to post it anyway because I've wanted to address this for some time now. Please note that this is one of several ways that this can be done, and is by no means the easiest. If you can, use the methods described in earlier posts such as one of these:



http://mrexcel.com/board/viewtopic.php?topic=40&forum=2&start=10

http://mrexcel.com/board/viewtopic.php?topic=28124&forum=2 (which has a link to another post)



- or many others if you search...



However, this does work well for the case of exporting data of unknown size to Excel from Access.


--------------------------------------------------------------------------------





Ok, first I'll describe what you'd need to do in Access (as far as data to be put into Excel).

Step1 - Identify unique Sales Managers:

For this, you either need a table that holds unique Sales Managers (each sales mgr is in the table only once), or write a query that gets unique sales managers (Select fldSalesMgr from tblX Group By fldSalesMgr Order By fldSalesMgr).

Step2 - Assign a number from 1 to X for each Sales Manager:

This query is a little bit complex, but fun. Here's an example of the SQL (say I wrote the query above to get unique sales managers and saved it as qrySalesMgrs):

SELECT qrySalesMgrs.fldSalesMgr, (SELECT Count(fldSalesMgr) From qrySalesMgrs A WHERE A.fldSalesMgr<=qrySalesMgrs.fldSalesMgr) AS Sequence
FROM qrySalesMgrs
ORDER BY qrySalesMgrs.fldSalesMgr;

If you want to build this query, add the qrySalesMgrs, then in the first query field, put fldSalesMgr, and sort it ascending. In the second field put:

Sequence: (SELECT Count(fldSalesMgr) From qrySalesMgrs A WHERE A.fldSalesMgr<=qrySalesMgrs.fldSalesMgr)

(my Sales Manager is stored in fldSalesMgr, yours may differ of course).

What this does is it gives you a list of Sales Managers, in Alphabetical order, and a list of numbers in the Sequence field, from 1 to X (where X is the number of Sales Managers). You can also do this in Excel, of course, but why not let Access do the work for you before you spit the data into Excel?

Save this query also.


Step3 - Get a list of Sales Managers and their corresponding Account Managers (with the manager number from above).

This would involve joining the last query we created with a table that has both Sales Managers and Account Managers. I assume you already have this since you want to put them in combo boxes. The resulting output should look something like the first 3 columns below:
OneComboBasedOnOther.xls
ABCD
1JohnSmithSuzy1JohnSmith
2JohnSmithTodd1JaneSmith
3JohnSmithTommy1BobRoberts
4JohnSmithFrazier1JamesJones
5JaneSmithBobby2RoseCarter
6JaneSmithSally2
7JaneSmithJohnny2
8JaneSmithJerry2
9JaneSmithSherry2
10JaneSmithSammy2
11BobRobertsAlan3
12BobRobertsElaine3
13BobRobertsGeorge3
14BobRobertsNewman3
15JamesJonesJenny4
16JamesJonesKosmo4
17JamesJonesEsmerelda4
18RoseCarterJimmy5
19RoseCarterBilly5
20RoseCarterTimmy5
21RoseCarterGale5
22RoseCarterGary5
23RoseCarterKramer5
24RoseCarterEstelle5
25RoseCarterFrank5
26RoseCarterMulva5
27RoseCarterMorty5
28RoseCarterDolores5
29RoseCarterNorm5
Sheet2


So we have Sales Manager, Account Manager, and Sales Manager Number in the 3 columns, respectively. And I gather that you already know how to put this information in Excel (let me know if not).

Ok, now let's go to Excel.

Step 4 - Assign a dynamic range for your list of managers (Sales Mgr, Acct Mgr, Sequence):

Go to Insert - Name - Define. Type in a name (I used rngManagers), then type the following formula in the "Refers To" box:

=OFFSET(Sheet2!$A$1,,,COUNTA(Sheet2!$A:$A),COUNTA(Sheet2!$1:$1)-1)

(where Sheet2 is the name of the sheet where you will put the list of managers from your query). This range is outlined in purple above.

Step 5 - Put a list of Sales Managers in Excel.

You can either export this data from Access, or you can use Advanced Filter (Unique Values, copy to another location) on your complete list of managers to get this. Again, I would do this in Access and export to Excel. For my example, the managers are:

John Smith
Jane Smith
Bob Roberts
James Jones
Rose Carter

I have these in Sheet2!D1:D5, and I have inserted a dynamic range for these also (I called my range rngSalesMgrs):

=OFFSET(Sheet2!$D$1,,,COUNTA(Sheet2!$D:$D))

(This is the reason for the minus 1 in the dynamic name above (rngManagers) for the "all managers" list - there's data in Column D, but we don't want to count it for the 3-column list above). This range is outlined in red above.

Step 6 - Hide the sheet with these ranges.

This is obviously optional.

Step 7 - Create 2 Combo Boxes on the sheet you want the user to see.

Create these using the Control Toolbox. For my example, the Sales Manager combo box is ComboBox1, and the one with the Account Managers for that Sales Manager is ComboBox2. In the properties of ComboBox1, set the ListFillRange to rngSalesMgrs. Do not set the ListFillRange of ComboBox2.

Step 8 - Insert code like the following behind the Combo Boxes (see below).

This code will go in the Worksheet module of the worksheet that these combo boxes are on. The best way to insert the code is to (in Design View) right-click on the button, then select "View Code." For more information on Workbook and Worksheet modules click HERE.<pre><font color='#000000'><font color='#000080'>Option</font><font color='#000080'>Explicit</font></pre><font color='#000080'><pre style="line-height: 100%; margin-top: 0; margin-bottom: 0">Private</font> fRunChange<font color='#000080'>As</font> Boolean</pre><hr align=left width=500><font color='#000080'><pre style="line-height: 100%; margin-top: 0; margin-bottom: 0">Private</font><font color='#000080'>Sub</font> ComboBox1_GotFocus()<font color='#008000'>' When ComboBox1 is selected, the user should be changing it,</font><font color='#008000'>' so set fRunChange to true, and we'll allow the Change Event</font><font color='#008000'>' to fully run.</font>

fRunChange = True<font color='#000080'>End</font><font color='#000080'>Sub</font></pre><hr align=left width=500><font color='#000080'><pre style="line-height: 100%; margin-top: 0; margin-bottom: 0"> </pre><pre style="line-height: 100%; margin-top: 0; margin-bottom: 0">Private</font><font color='#000080'>Sub</font> ComboBox1_LostFocus()<font color='#008000'>' When ComboBox1 doesn't have the focus, don't allow the</font><font color='#008000'>' Change event to fully run (this is in conjunction with the</font><font color='#008000'>' "If Not fRunChange Then Exit Sub" line in ComboBox1_Change).</font>

fRunChange = False<font color='#000080'>End</font><font color='#000080'>Sub</font></pre><hr align=left width=500><font color='#000080'><pre style="line-height: 100%; margin-top: 0; margin-bottom: 0"> </pre><pre style="line-height: 100%; margin-top: 0; margin-bottom: 0">Private</font><font color='#000080'>Sub</font> ComboBox1_Change()<font color='#008000'>' -- We only want to run the change event if ComboBox1</font><font color='#008000'>' -- has been changed. Because ComboBox1's ListFillRange</font><font color='#008000'>' -- is based on a dynamic range, whenever the worksheet is</font><font color='#008000'>' -- changed, the dynamic range is re-calculated, and this</font><font color='#008000'>' -- seems to trigger the change event for this combo box.</font><font color='#008000'>' -- "Not fRunChange" is equivalent to "fRunChange = False"</font><font color='#000080'>If</font><font color='#000080'>Not</font> fRunChange<font color='#000080'>Then</font><font color='#000080'>Exit</font><font color='#000080'>Sub</font><font color='#008000'>' -- Call SetRange, pass it the sequence number of the currently</font><font color='#008000'>' -- selected Sales Manager (a combo box list is zero-based, so</font><font color='#008000'>' -- the first item is item 0. So add one to the ListIndex).</font>
FillAccountMgrs (ComboBox1.ListIndex + 1)<font color='#008000'>' -- I like to select a cell on the sheet after the combo box is</font><font color='#008000'>' -- changed. The reason for this is that Excel can act weird if</font><font color='#008000'>' -- you try to do some things while a combo box is still selected.</font><font color='#008000'>' -- Cell J6 in my workbook is the cell behind my ComboBox1.</font>
ActiveSheet.Range("J6").Select<font color='#000080'>End</font><font color='#000080'>Sub</font></pre><hr align=left width=500><font color='#000080'><pre style="margin-top: 0; margin-bottom: 0"> </pre><pre style="margin-top: 0; margin-bottom: 0">Private</font><font color='#000080'>Function</font> FillAccountMgrs(lngSalesMgr<font color='#000080'>As</font> Long)<font color='#008000'>'*********************************************************************</font><font color='#008000'>' Purpose: Populates ComboBox2 with the Account Managers that</font><font color='#008000'>' correspond to the Sales Manager that is passed in.</font><font color='#008000'>' Inputs:</font><font color='#008000'>' lngSalesMgr: the sequence number of the Sales Manager that has</font><font color='#008000'>' been selected in ComboBox1</font><font color='#008000'>'*********************************************************************</font><font color='#000080'>Dim</font> rngManagers<font color='#000080'>As</font> Range<font color='#000080'>Dim</font> lngRow<font color='#000080'>As</font><font color='#000080'>Long</font><font color='#000080'>Dim</font> lngFirstRow<font color='#000080'>As</font><font color='#000080'>Long</font><font color='#000080'>Dim</font> lngLastRow<font color='#000080'>As</font><font color='#000080'>Long</font><font color='#000080'>Dim</font> lngNumRows<font color='#000080'>As</font><font color='#000080'>Long</font><font color='#008000'>' -- This is the range that we will search to find all Account</font><font color='#008000'>' -- Managers for the passed in Sales Manager.</font><font color='#000080'>Set</font> rngManagers = ThisWorkbook.Names("rngManagers").RefersToRange

ComboBox2.Clear<font color='#008000'>' -- Navigate through the managers range to see which ones</font><font color='#008000'>' -- belong to the currently selected Sales Manager. If</font><font color='#008000'>' -- we get a match, add it to ComboBox2</font><font color='#000080'>For</font> lngRow = 1 To rngManagers.Rows.Count<font color='#000080'>If</font> rngManagers(lngRow, 3) = lngSalesMgr<font color='#000080'>Then</font>
lngFirstRow = lngRow<font color='#000080'>Do</font><font color='#000080'>While</font> rngManagers(lngRow, 3) = lngSalesMgr
ComboBox2.AddItem rngManagers(lngRow, 2)
lngRow = lngRow + 1<font color='#000080'>Loop</font>
lngLastRow = lngRow - 1<font color='#000080'>Exit</font><font color='#000080'>For</font><font color='#000080'>End</font><font color='#000080'>If</font><font color='#000080'>Next</font> lngRow<font color='#008000'>' -- How many managers do we have?</font>
lngNumRows = lngLastRow - lngFirstRow + 1<font color='#008000'>' -- We don't want to show too many rows - if there are more than</font><font color='#008000'>' -- 10 Account Managers, then there will be a scroll bar that the</font><font color='#008000'>' -- user can use to find any that are not in the first 10.</font><font color='#000080'>If</font> lngNumRows > 10<font color='#000080'>Then</font>
lngNumRows = 10<font color='#000080'>End</font><font color='#000080'>If</font><font color='#008000'>' -- Set Combo2's text to its first item.</font>
ComboBox2.Text = ComboBox2.List(0)<font color='#008000'>' -- This is just for cosmetic reasons - show the exact number</font><font color='#008000'>' -- of Acct Mgrs if less than or equal to 10.</font>
ComboBox2.ListRows = lngNumRows<font color='#000080'>End</font><font color='#000080'>Function</font></pre><hr align=left width=500><font color='#000080'><pre>Private</font><font color='#000080'>Sub</font> ComboBox2_Change()<font color='#008000'>' Again, I just like to select a cell on the sheet after</font><font color='#008000'>' changing the combo box. L6 is behind ComboBox2</font>

ActiveSheet.Range("L6").Select<font color='#000080'>End</font><font color='#000080'>Sub</font></pre>

EDIT: One thing I thought of after re-reading this is that you need to export the data to Excel in the locations that I have above (for my example to work, anyway). So the fist query goes on Sheet2 in columns A-C, and the unique sales managers go to column D. You could also put these on separate sheets if you wanted to...
This message was edited by Russell Hauf on 2002-11-07 14:16
 

methody

Well-known Member
Joined
Jun 17, 2002
Messages
843
I have used this advice in getting one combobox to depend on the result of another and it functions perfectly but when I save the file in which I tried it and then reopen it I get the big message that Excel has encountered a problem and will have to close. The repaired file removes all the code. Is there any thing i can do the amend the code or is there some other problem, I am using Excel 2002
 

Watch MrExcel Video

Forum statistics

Threads
1,123,324
Messages
5,600,956
Members
414,417
Latest member
Nobu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top