Userform Comboboxes - need a quick how to..

[Guest]

I read an article lastnight by Frank Rice about MS object model programming. It contained info about userforms which I have shyed away from until now as I didn't know how they worked. I have a need to use one now as I suspect they will be cleaner and more efficient than combo boxes and data validation lists on the sheets (which are doing funny things like moving around the page or point blank refusing to work, I don't know why!).

My Question is how exactly do I get a combo or list box to work in a userform. I want to be able to populate the combo box in the userform with five named cells, job1, job2, job3 etc. I then want to be able to select the "job" that I want from the list in the combo box and have the procedure in the macro input that value into a cell in the destination workbook and hide the combo box.
Roger W

 

[Simon Lloyd]

You need to be in the VBA editor, then right click on the left hand sid
where it says sheet1 sheet2 etc then select insert and userform, ad
your buttons and text, now for the job selection......type a list o
your jobs on a sheet highlight the range and in the formula bar on th
left hand side there is a small range window click in this and type
name for you range, now go back to your userform and in the propertie
window on the left scroll down untill you see row source then type you
list name in here, view the code for the combobox1 and typ
activecell.value=combobox1.value and on the next line type me.hide

In the code page for a worksheet select from the top dropdown bo
worksheet and from the one on the left select selectionchangenow on th
next line after sub type userform1.show.

this is the basis of the userform, have a play around with the setting
and captions to suit your needs.

Have fun!

Simo