ComboBox question

Hi. I want to make some things with combo boxes, but I'm completely new to VBA and it's very strange to me.
I have 8 combo boxes on Sheet 1, which must contain the info from Sheet 2.
The "Group" Combo boxes should contain the letter of the group (A, B, C or D - the information should be used from the D2:D5 cells from Sheet 2).
I want when user clicks on the Group Combo box (the one on the "1st Grade" row for example), and select some group (for example A), in the left combo box (which at first says "Choose Group ..."), all the names of studenst from 1st Grade and A group to be displayed.

\1

\1

What you want is not entirely clear.

Where do you want the list to be displayed? Do you want the active sheet to switch to sheet 2 and filter sheet 2 for the group selected? Or, do you want the list to be displayed on the sheet that contains the combo boxes?

If the latter, then when do you erase these names from that sheet?

If the former, do you want to remove the filter when the user returns to the combo box sheet, or as soon as they leave the sheet containing the names, or only when they make a different selection that involves that sheet?

What do you want to do if the user cannot follow directions? So, what if they make a selection from the "group" combo box before they make a selection from the "grade" combo box?

And why do you have 4 sets of these boxes?

OK, looking at it again, I think I get it. You want the list of names in the first combo box.

The exact code will depend on how the combo boxes are named and how you want the names to be displayed. Also, whether you want the names sorted or not. I have chosen to name the first set of combo boxes "cb1_Names" and "cb1_Group". And, to display the names as LastName, First Name. I have not sorted the names.

Please Login or Register  to view this content.

Thank you very much for the reply. I can understand the code but unfortunately it doesn't work with my xls and I can't figure out what's wrong. Could you please attach your .xls file so I can take a look at it ?

Can only at guess the problem without more to go on.

First ... where did you put the code? It needs to be on the code window for the worksheet that has the combo boxes on them. As soon as you type "Me.", a drop-down should appear that lists your 8 ComboBoxes. If this does not happen, then you have the code in the wrong place.

Second ... you need to either change the code or change the names of the combo boxes.

Third ... I used a short-cut when defining the worksheet. This line of code:

Please Login or Register  to view this content.

is using the CODEname of a worksheet. That is the name that is NOT in parenthesis in the Project Explorer window of the VB Editor.

10x again It works now. The strange thing is that my xls is the same, and I still can't find the error in it.
Would it be more complicated if the student names were sorted by average mark (new column) ?

Not really. But, there is one thing to be constantly vigilant about.

When the code is in the code window for an object (which the "event" code for an ActiveX control, like the combo boxes, must be), that object might or might not be able to access other objects.

What?!? For example, the code below:

Please Login or Register  to view this content.

If you put this code in the code window for Sheet1, it will fail at the second executable line (selecting the range). This demonstrates that you cannot select a range in a different worksheet from code in a worksheet.

To get around this limitation, I tend to put as little code as possible in an object's code window. Putting the code instead in a "Module". (Use Insert >> Module from the VB Editor menu).

If you want to sort the data manually, this warning is irrelevent. But, if you want to sort the data using code, then put the code in a general code module.

Hope this helps.

I want to sort using code so I'll put the code in the general code module.
Thanks again

Please Login or Register  to view this content.

How can I add 1 000 separator here ?
(at column I have points, which I want to be displayed with space between every three digits; it is ok in the column with formatting, but in the combo box doesn't work)

Not sure which of the 3 items you want formatted.

If it's the last item, it would look like this:

cb2.AddItem ws.Cells(i, 2) & ", " & ws.Cells(i, 1) & " - " & Format(ws.Cells(i, 12), "# ##0")

Here is one idea. I put is as code behind a command button. It relies on identifying combo boxes by name. Then, parses out the points from the displayed value by finding the dash between the name and the points.

Please Login or Register  to view this content.