Using a ComboBox in a Userform to create a dropdown list to select specific Worksheets

[pejota]

Hi there,

MY INTRO
I'm new here. I've been an excel user for quite sometime but for the first time I've been tasked with designing a user form. I'm not very familiar with using VBA still, although I've learned a bit of programming in my life, programming in VBA is still new stuff for me.
This is probably going to be a good forum for me to learn and share (the little I know) about excel.

My Doubt
I need to create a dropdown list (CombobBox) in the user form that can select which worksheet the data will be entered in.

Now, I have a few worksheets and I want to allow the user to select only the worksheets for the months (I have 12 worksheets named after each month).

After they select the month on the drop down box in the user form, they will enter the rest of the data in the form as per normal. What I need is that this data goes to the spreadsheet for that specific month when they click the command button to insert it.

Stupidity Disclaimer
I don't know how easy or hard this. If I left out any crucial details or didn't explain this properly, please let me know and I'll post more info. If there are any resources for learning this I am not aware of this, please send me a link. I searched this forum and I still couldn't find an answer exactly like mine - not one that worked, at least.

Your help is greatly appreciated!

Cheers,
PEDZ

[tehneXus]

Hi and welcome to the forum,

please check this example: x_UserForm Example.xlsm
It should give you an idea of user-forms used to enter/read data from a worksheet.

It can be easily modified to select the target/source worksheet if you want to go with this kind of code.

[pejota]

Thanks, but I couldn't get this to work.

I an error when I click on the button on the spreasheet:
COuld not load some objects because they are not available on this machine.

Then when it opens up Visual Basic I get:
Compile error: Can't find project or library.

Any thoughts?

[tehneXus]

Hi,

I assume this is caused by the missing reference to the calendar control.

Open the VBA Explorer (ALT+F11), in the menu go to Tools -> References. Search for "Microsoft Calendar Control" and check the box next to it.
Continue with the "OK" button, save and re-open the file.

If you cannot find "Microsoft Calendar Control" in the list download it from here: http://www.gmayor.com/Zips/MSCAL.ZIP
or remove the control and corresponding code from the userform.

If you need help just let me know.

[pejota]

It still didn't work. I used the code below and it seems to do what I need:

Please Login or Register  to view this content.

Does it make sense? However, I'm still configuring the form to input the data in the right rows/columns so I'm not sure.