Solved: Multiple selection question

[clhare]

I have a template userform that needs to allow the user to select up to 6 different plans from a list of 17 plans. Based on the plan(s) selected, I need to assign values to 2 variables per plan (one will be the same value as found in the list, the other will be an abbreviation of that particular plan name).

I'm kind of stumped on how best to do this. If I use 6 combo boxes, then I have to repeat everything 6 times. If I use a list box with multi-select, can I limit the number of selections to a maximum of 6? Then how would I assign my variables (for plan [n], strB = textB and strC = textC)?

Any help would be greatly appreciated!

[fumei]

Hi Cheryl. Can we get a bit more detail?

allow the user to select up to 6 different plans from a list of 17 plans. I have no idea what a "plan" is, in terms of using VBA.

A combobox (or whatever control) allows to the users to select one (or more) items listed. The items are NOT "plans", they are strings. So the user selects an item(s).

These are strings, OR you can use the index number of the item(s).

So I do not follow what you are saying about assigning variables to the "plan".

[clhare]

Sorry! By "plans" I mean that something like the following would be the items in the dropdown:

-- ABC Company Retirement Plan
-- DEF Co. Pension Plan
-- GHI Inc. Savings Plan

The user can select up to 6 plan names from the dropdown, so how would I set up the combo box to allow that? Can I limit it to 6 or do I have to leave that an open number and then somehow identify how many plan names were selected from the combo box?

Once they have made the selections, I have to define 2 strings based on each of the plan names selected. So, if only the first 2 plan names were selected in the dropdown, I want to assign specific information to the 2 strings, similar to the following:

strFullName1 = "ABC Company Retirement Plan"
strShortName1 = "ABC RP"
strFullName2 = "DEF Co. Pension Plan"
strShortName2 = "DEF PP"

Once I have the 2 strings assigned for each plan name selected, I can replace default text in the document with the values in the strings.

[fumei]

You have to think in the terms of the process you are using.

The user can select up to 6 plan names from the dropdown

Try to stop thinking like that. The users are NOT selecting plan names. They are selecting strings. To you they are meaningful, but to VBA they are only strings.

In other words, if you want to use the strings to mean/do something, YOU have to design and write the logic.

then somehow identify how many plan names were selected from the combo box?

I do not think you can restrict the number of selection allowed for a control that allows multiple selections. If it allows them, then...it allows them. However, again, this is something you can deal with by writing clear and explicit logic.

It is simply getting the logic. Demo attached. Click Show Test Userform on the top toolbar.

There is a Listbox. Select a few using holding the Ctrl key and clicking to multi-select.

Now click the Demo List button. Notice the item selected are listed - below the Listbox, in a label - PLUS some text that states what was selected.

As I mentioned, doing this is only a matter of working out your logic. Hopefully you can deconstruct the demo so you can make it work for you.

Just to cover an issue...you state that maybe 6 items could be selected. OK. Does that means you have 12 string variables already declared?

Dim strFullName1 As String
Dim strShortName1 As String
Dim strFullName2 As String
Dim strShortName2 As String
Dim strFullName3 As String
Dim strShortName3 As String
Dim strFullName4 As String
Dim strShortName4 As String
Dim strFullName5 As String
Dim strShortName5 As String
Dim strFullName6 As String
Dim strShortName6 As String

This seems VERY inefficient.

[clhare]

Okay, I'm trying to figure out how to streamline this so it will work for me and decided that it made the most sense to use a 2-dimensional array in the list box since there are two values I need to use for each item and then use a loop to assign the values from each dimension to a variable so I don't have to declare a bunch of variables that may or may not be needed.

So, I have created a sample file (attached) that populates a 2-dimensional array and there's 2 command buttons on the userform.

When the "Get short names" button is selected, I want the macro to do 2 things: 1) go through a loop and tell me the information stored in the 1st dimension of the array for each selected item, and 2) tell me how many values were selected in the listbox.

When the "Get long names" button is selected, I want the macro to do the same things, just with the information stored in the 2nd dimension of the array for each item selected.

What I can't figure out for this template is how to assign the values to a variable based on a particular dimension in the array. I haven't been able to find anything on the internet that does that and nothing I've tried will work. Once I can get this little example template to work, I think I'll be able to understand multi-dimensional arrays better and be able to get my actual template to work.

Thanks for your help!!

[clhare]

I got it to work!!

I had to declare my array in the Declarations area instead of in the Initialize procedure.

Then I was finally able to correct my code to pull the values into strings.

I've attached my updated sample doc.

[fumei]

"I had to declare my array in the Declarations area instead of in the Initialize procedure."

Yes...you would. Read up on Scope! It is a very very important concept.