how to populate from combobox to worksheet

[dan2010]

Hi,

i have searched online and i found many links for populating from worksheets to combobox but none for populating from combobox to worksheets.

i have attached an example of what i am doing. user will choose some items from the listbox and choose 1 year from combobox. then a macro will insert the chosen items into the respective year.

i have already thought of a way to extract the selected items from listbox but not from combobox.

so i would like to know what is the coding to find out which year the user has chosen from combobox.

any help is greatly appreciated
Thank you.

[royUK]

You can simply use something like this
[cells(!,1).Value=Me.ComboBox1.value[/code]

If you want to enter entries from a multiple selection the code would be a little more complicated

[royUK]

I've just had a closer look at your form. You need to do some checking on using these controls, the form is slow loading because it is reading empty rows into the Listbox. The best way is to either use a Dynamic Named Range for the data or set the RowSouce as the form opens

[dan2010]

You can simply use something like this
[cells(!,1).Value=Me.ComboBox1.value[/code]

this is good enough for me, thank you

The best way is to either use a Dynamic Named Range for the data or set the RowSouce as the form opens

i dont understand how to use dynamic named range. found a site
http://support.microsoft.com/kb/830287 but dont really understand how to name the range.so do i name a range for each column? or is it possible to name a range for all 3 columns?

and after which i just enter the name into listbox>properties>rowsource?

[royUK]

You can name a the range to expand by rows or columns.

I would generally set the rowsource on initialisation of the form

Please Login or Register  to view this content.

[dan2010]

Please Login or Register  to view this content.

i have tried to use your code but the listbox populated data from sheet2 instead of sheet3. the reason might be due to the fact that the macro is in sheet2. i am planning to make sheet3 a hidden sheet.

Please Login or Register  to view this content.

this coding populates data from sheet2 too.so is there any way to change the coding so that listbox will populate data from sheet3 instead?

by the way i have tried to make the macro process faster by changing the limits of the variables "a" and "c".

Please Login or Register  to view this content.

my plan is to make my code loop until the last row to make it run faster, but it made my excel freeze instead. can you kindly point out where did i go wrong?

thank you for your time and advice

[royUK]

Looping code will make code slower. Why are you doing this

Please Login or Register  to view this content.

The code for the form should be in th form's module, not the sheet.

Please Login or Register  to view this content.

[dan2010]

i have tried your coding but the listbox does not show anything :S

i have attached my sample. please let me know what is the mistake.

ps: i just started to learn coding 1 month ago, so i am currently using the basic coding and logic to find solutions while still learning how to make my coding simpler and less messy

pss: sorry for the late reply because i am sharing computer with internet access with a few people.

[royUK]

Try this the listbox fills from sheet3

[dan2010]

hmm....what did you change?

by the way, it shows the column headers as 1 of the option :s

[royUK]

To exclude the headers

Please Login or Register  to view this content.

I hust put the code into the initialise event

[dan2010]

i want header. but i want the header to be the 1st row. currently, the header row in listbox is empty, and the header in sheet3 is copied into the 2nd row in listbox. from what i know, if the header is in 5th row, then the row source should start from row 6.