Combo Box

dtaylor · Nov 5, 2002

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

DonkeyOte · Nov 5, 2002

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 · Nov 6, 2002

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 · Nov 6, 2002

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 · Nov 6, 2002

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

A

B

C

D

1

JohnSmith

Suzy

1

JohnSmith

2

JohnSmith

Todd

1

JaneSmith

3

JohnSmith

Tommy

1

BobRoberts

4

JohnSmith

Frazier

1

JamesJones

5

JaneSmith

Bobby

2

RoseCarter

6

JaneSmith

Sally

2

7

JaneSmith

Johnny

2

8

JaneSmith

Jerry

2

9

JaneSmith

Sherry

2

10

JaneSmith

Sammy

2

11

BobRoberts

Alan

3

12

BobRoberts

Elaine

3

13

BobRoberts

George

3

14

BobRoberts

Newman

3

15

JamesJones

Jenny

4

16

JamesJones

Kosmo

4

17

JamesJones

Esmerelda

4

18

RoseCarter

Jimmy

5

19

RoseCarter

Billy

5

20

RoseCarter

Timmy

5

21

RoseCarter

Gale

5

22

RoseCarter

Gary

5

23

RoseCarter

Kramer

5

24

RoseCarter

Estelle

5

25

RoseCarter

Frank

5

26

RoseCarter

Mulva

5

27

RoseCarter

Morty

5

28

RoseCarter

Dolores

5

29

RoseCarter

Norm

5

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>OptionExplicit</pre><pre style="line-height: 100%; margin-top: 0; margin-bottom: 0">Private fRunChangeAs Boolean</pre><hr align=left width=500><pre style="line-height: 100%; margin-top: 0; margin-bottom: 0">PrivateSub ComboBox1_GotFocus()' When ComboBox1 is selected, the user should be changing it,' so set fRunChange to true, and we'll allow the Change Event' to fully run.

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

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

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

ActiveSheet.Range("L6").SelectEndSub</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

dtaylor · Nov 6, 2002

i bow to the excel gods...

thanks russell

methody · Apr 2, 2004

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

Combo Box

dtaylor

Active Member

Excel Facts

DonkeyOte

MrExcel MVP

Russell Hauf

MrExcel MVP

dtaylor

Active Member

Russell Hauf

MrExcel MVP

dtaylor

Active Member

methody

Well-known Member

Similar threads

Forum statistics

Share this page

Combo Box

dtaylor

Active Member

Excel Facts

DonkeyOte

MrExcel MVP

Russell Hauf

MrExcel MVP

dtaylor

Active Member

Russell Hauf

MrExcel MVP

dtaylor

Active Member

methody

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock