Dropboxes and VBA code in Excel 2007

[luca87itc]

Hi folks,
I need your help.

I have the following situation.
In a sheet2, I need to create drop-boxes (based on another Sheet1), one listing names of retailers, one some criteria (called "A"), one criteria "B", one "C", one "D".
One is supposed to select one OR MORE names from each list (each list is "independent" from the others).
Then I am supposed to create a button to generate a Sheet3, displaying what has been selected in the drop-boxes (list of retailers and criteria) and the corresponding text (all included in Sheet1).

My first question is "how to create drop-boxes with a multiple selection"? I use Data->Data Validation to create them, but I can select only one each time.

The second problem is how to create a macro (or VBA code) to display the results.
I am really under pressure for this work, so any help or suggestion is really appreciated.

Thanks

 

[Trevor G]

I would consider building a Userform from the VBA side which will use a Listbox to list the records and then you can set the properties to allow mulitple selections.

Use Alt + F11 to go into the VBA screen then use the Insert Menu and select Userform. You will then get some controls and select the Listbox and draw a list box in the form, you can also add a label and then a command button. This will give you the designing part then select the listbox and look at the Properties and search down until you find MultiSelect you can click the down arrow and change.

You can also set the row source to a named range of cells.

Then it is a case of coding the command button to give you the results into another sheet.

You could then add a command button on the worksheet to shwo the userform.

If you design the form and set the properties then post back with a screen shot and some screen shot of the data on the worksheet that will assist in finding a solution.

 

[luca87itc]

Thanks a lot for the suggestion.
I am not such an expert in Excel so I did not think about userforms.
I'll try to make something and post it again.
Thanks again

 

[luca87itc]

Hello,
I have created the Userform.
As you can see in the screenshot I have 7 list-boxes, 6 of which have also a check-box.
This is the first thing, I would need a VBA code in order to have the list-box shown only if the corresponding check-box is checked.

-- removed inline image ---

 

[luca87itc]

Sorry, I don't know how to show the screen shots, and how they are not shown in the message.

 

[Trevor G]

Send me a private message to gain an email address to send a copy of your workbook.

 

[luca87itc]

I was also thinking, for the second part, about a combination of Vlookup and Hlookup, to find the text needed.
After the selection from drob-down list (even with a simple single selection), being able to show the text corresponding to what has been selected.
Could it work?