Multi List Box in User Form

Hi, the attached WB shows 2 sheets (estimate) and (rate table) What I am trying to achieve is that : User selects button "rate table" this pops up user form 1 on estimate sheet (keeping rate table sheet hidden), the user form contains a multi list box which populates from rate table D2:D51 (4 columns) the user selects what items he/she requires then presses enter button. The selected items then populate estimate sheet finding the next 'free' row from 32 onwards. I would like to keep the rate table sheet very hidden at all times.

Not sure if this can be done?

Thanks in advance
Chris.

You were pretty much there.

If the rateTable is always hidden don't use a name with spaces in it (it just makes things easier):

The row source is : Ratetable!D2:G51

You can hide the ratetable by seting the visible property of the sheet to xlsheethidden or xlsheetveryhidden

Thanks Tony, I don't think i'm nearly there as I don't have the abilities to create the code for copying the selected items from the list box to the estimate sheet! However that has solved part of my query - I could'nt get the row source to identify the sheet have tried everything in there - omitting the space works perfectly - thank you.

Chris

Have a look at the attached workbook

Also the code for copying the data across goes on the button click event can be of the for:

Please Login or Register  to view this content.

This just copies the first item but just repeat the last line for any others

Originally Posted by zimbo109

to copy all the selected data over.

Just to be (pedantically) clear.
Selecting an item in the list box only selects the item in the list box which is a single string.

Using the code from my last post :
- rngOut points a range object at the equivalent line on RateTable.
- rngIn points to the first free line on Estimate

(actually the first cell on the line)

so using rngout.offset... = rngIn.offset... you can copy cells from RateTable to Estimate

I have done the first one for you. Just repeat the line changing the offset values

I've added code to the enter button,it allows multiple selections & enters to the form.The code prompts for a quantity& enters. It also allows for the merged cells.

Here's an explanationof the Enter code,forgot to add it to workbook

Please Login or Register  to view this content.

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

This will stop the mismatch error

Please Login or Register  to view this content.

I can't duplicate the other problem,although the End IF on your highlighted row should be on the next line

The ListBox shouldn't populate from whole page,it uses CurrentRegion. Set the rowsource to your needed range

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

It might be better to add the selected items to another listbox

Try this, I've added a second Listbox to store the selection in

You would need to add a new column to the ListBox & change the code to amend the ListBox. I thought the second ListBox gives the user the chance to review the selection& add the quantities, an option to remove an item from the second Listbox could be added

I can't see why you need another userform.

You could probably add a column to the ListBox then add the quantity tothat,but it would mean a lot morecoding

I'll have a look when I can,busy at the moment